Stored Proc with param used for combo box

[Bob]

Hello All,
I'd like to have ComboBox 1 drive what is displayed in ComboBox 2.

I can use forms and even subForms & Reports, and use stored procs for
everything
using the .InputParameters property

The combo box has no such property.

I need something like:
Me.cbo_SelectResourceType.RowSource = "Eq_ResourceTypes @rtid = " &
Me.cbo_SelectResourceGroup.Column(0)

but this fails saying that it can not find "Eq_ResourceTypes @rtid = 12" (or
some variation).

I simply want to use a stored proc as the rowsource for a combo box and have
the stored proc use an input param.

Any help would be much appreciated.
Thanks in advance,
bob.

 

[Sylvain Lafontaine]

Add the word EXEC before the name of the SP:

...RowSource = "EXEC Eq_ResourceTypes @rtid = " ...

Take care with the required delimiters for strings and dates/times.

 

[Bob]

: ) VERY COOL!

works perfectly!

Thanks Sylvain!

 

[Sylvain Lafontaine]

I should have said that if all the parameters used in the combobox' SP are
also parameters for the main form SP, this should also work; however,
performance might be less because for complexe forms/subforms, the
comboboxes will be requiried more times than necessary.

With the EXEC method, you won't have this problem.

Also, there have been report of using controls with the same name as the
parameters but personally, I haven't be able to make this work reliably the
last time that I've checked (around circa 2000).

 

[Bob]

Thanks for the additional information Sylvain!
The Exec Method works great.
The first combo's OnChange event fires the EXEC to reset combo 2.
It resets the 2nd combo only when needed.

Typically, on forms, I set the InputParams in the OnOpen event.
Then in the OnCurrent event of the Main form, I reset the inputParameters
of any subForms. This seems to work well and performance has been good.

Is this the correct approach?

Thanks again,
Bob.

 

[Sylvain Lafontaine]

I suppose that if it works, then it's OK. Personnally, I prefer not to use
the InputParameters for subform and use the either the Exec method or the
Link Child and Master Fields method.

 

[Bob]

Thanks Sylvain.
That's interesting. I will experiment using both, to see if there are any
significant differences in performance.
Once again, thanks for your help with this.
Bob.