Access Project with SQL Backend - Filter by value on a form

[storm75m]

I'm brand new to using Access Projects and SQL server data and need a little assistance.

I'm trying to create a list box control on a main form that links to another table to show some detail (Parent/Child related data). Example Parent = District, Child = Fees. I want the list box to only show fees for the current district displayed on the main form. In a normal Access database, as the recordsource for the listbox, I could limit the criteria simply by putting the where clause as [DistCode] = [Forms]![frmDistricts]![DistCode].

However with Access Projects, there are no longer any local queries, just SQL server views and stored procedures, and referencing a control on a form does not work. Do I need to create a stored procedure with parameters or something? How do I do this type of thing? I'm sure it's simple, just not sure where to start. Thanks!

 

[hwkranger]

Storm,

I don't use ADP's for the reason that it limit's the ability to do on the fly ad-hoc without having full rights to the server. I'm not a DBA, and I don't have full rights, but I'll try to answer this from what I remember of using ADP's.

Actually, I quickly made an ADP and I was able to do the following:

Private Sub Combo0_Enter()
Me.Combo0.RowSource = "SELECT dbo.tblOperator.*, [Mgr/Opr] AS Expr1 FROM dbo.tblOperator Where [Mgr/Opr] = " & Nz(Me.Text2, 0)
MsgBox (Me.Combo0.RowSource)
End Sub

Meaning, you can change the rowsource dynamically. That's what I did....

Randall Vollen
National City Bank Corp.

 

[storm75m]

thanks...

Is this the only way it can be done? I'm going to have several list boxes and subforms all over this screen with a multi-tab control, and was trying to avoid binding all of the record sources programatically. Forntunately I am the DBA as well, so I can add or change objects if I need to. Can you use a stored procedure as the recordsource for an object?

 

[hwkranger]

Storm,

I envy that fact that your company allows cross development priviledges.

Yes, you can use a SP results as a recordsource. I would suggest using client side cursor though. I can't remember why, but I had problems with using server side cursors with SP's.

You also will probably have to use MSDATASHAPE instead of OLESQL drivers for your connection.

That's about all that I can remember right now.

Randall Vollen
National City Bank Corp.