Stored procedures and parameters

[Wes Peters]

I have a customer form that has a dropdown containing the CustName and
CustID fields in two columns. The data source is a sp.

When a selection is made from the dropdown, I want to use the CustID field
and pass it as a parameter to another sp which will retrieve all the
customer info and populate the form. Nothing I've tried has worked.

Any thoughts would be appreciated.
Thanks,
Wes

 

[Sylvain Lafontaine]

Easy: add the following to the Input Parameters property of the form:

@ComboCustID int = Forms!MyForm!ComboCustID

and the following parameter to your SP:

MySP (@ComboCustID int) ....

After that, make a requery of the form in the OnAfterUpdate event of the
combobox.

A second possibility would be to dynamically build the record source of the
form with something like this:

Me.RecordSource = "exec MySP " & Me.ComboCustID

There is no need to make an explicit call to the requery method after
changing the record source. Also, each time you change the parameters of
one of your stored procedure, you must make use of the Refresh command (F5)
on the Queries window (or tab). Same thing for the tables window.

 

[Wes Peters]

Thanks - Worked great.

Easy: add the following to the Input Parameters property of the form:

@ComboCustID int = Forms!MyForm!ComboCustID

and the following parameter to your SP:

MySP (@ComboCustID int) ....

After that, make a requery of the form in the OnAfterUpdate event of the
combobox.

A second possibility would be to dynamically build the record source of the
form with something like this:

Me.RecordSource = "exec MySP " & Me.ComboCustID

There is no need to make an explicit call to the requery method after
changing the record source. Also, each time you change the parameters of
one of your stored procedure, you must make use of the Refresh command (F5)
on the Queries window (or tab). Same thing for the tables window.