Execute Stored Procedure With Parameter From Form's Combo Box

[DomDom3]

Hi there,

I'm pulling my yhair out over this - have been scanning posts all morning but have got nowhere.

I have a form, with a click button which runs a stored procedure to screen and a combo box which is used to provide a parameter to the stored procedure.

I've this code:

Dim stDocName As String

stDocName = "spPartner_Spend_Forecast"
DoCmd.OpenStoredProcedure stDocName, acViewNormal, acEdit

which runs the stored procedure but I can't pass the parameter to it.

In the SP the parameter is @LC, I want to make LC equal to the selection from combo1.

Ive tried adding @LC = forms!myreport!combo1 to the input parameter of the form, but this tries to filter the form by the combo box.

In an mdb I would put forms!myreport!combo1 in the query as a criteria, but this obviously doesn't work with an adp.

Can anyone help please?

 

[Crowley16]

don't use docmd.openstoredproc...

instead declare a command object

set it's connection to the current project's connection

set the command text to the stored procedures name

incude the parameters in the command text OR set the parameters using the .parameters collection of the command object

--------------------
Procrastinate Now!

 

[lameid]

Crowley16,
Doesn't the command object return a recordset and not a datasheet?

DomDom3,

I solved this problem by opening a form in datasheet view with an input parameter specified. Not elegant, or simple but effective.