Stored Proc with param used for combo box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob - 25 May 2007 16:55 GMT

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.

Reply to this Message

Sylvain Lafontaine - 25 May 2007 17:04 GMT

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.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

> Hello All,
> I'd like to have ComboBox 1 drive what is displayed in ComboBox 2.
[quoted text clipped - 18 lines]
> Thanks in advance,
> bob.

Reply to this Message

Bob - 25 May 2007 17:23 GMT

: ) VERY COOL!

works perfectly!

Thanks Sylvain!

> Add the word EXEC before the name of the SP:
>
[quoted text clipped - 24 lines]
>> Thanks in advance,
>> bob.

Reply to this Message

Sylvain Lafontaine - 25 May 2007 17:44 GMT

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).

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

>: ) VERY COOL!
>
[quoted text clipped - 30 lines]
>>> Thanks in advance,
>>> bob.

Reply to this Message

Bob - 25 May 2007 20:52 GMT

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.

>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,
[quoted text clipped - 41 lines]
>>>> Thanks in advance,
>>>> bob.

Reply to this Message

Sylvain Lafontaine - 25 May 2007 22:04 GMT

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.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

> Thanks for the additional information Sylvain!
> The Exec Method works great.
[quoted text clipped - 55 lines]
>>>>> Thanks in advance,
>>>>> bob.

Reply to this Message

Bob - 29 May 2007 14:23 GMT

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.

>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
[quoted text clipped - 59 lines]
>>>>>> Thanks in advance,
>>>>>> bob.

Reply to this Message