Why no input parameters for combo box rowsource?

[Jon]

I've been learning the ADP way for the past few months...in many ways it's
pretty cool and works well. Here's one thing I've found that doesn't make
sense though...there appears to be no way to base a combobox's row source on
SQL (statement or sproc) and have it dynamically parameterized. I can set
the property to "EXEC my_sproc 1" and pass static parameters, but the only
way I can find to change this at run time is to use VBA to rewrite the
rowsource property. Now forms based on SQL data sources have the input
parameters property that are used for this situation but the individual
combo and list box controls don't. any one else noticed this? What gives? If
it's true then this seems like a rather large oversite on MS's part.


Jon

 

[Jacek Segit]

Witaj Jon,
W Twoim li¶cie datowanym 27 lipca 2004 (04:30:10) mo¿na przeczytaæ:

J> I've been learning the ADP way for the past few months...in many ways it's
J> pretty cool and works well. Here's one thing I've found that doesn't make
J> sense though...there appears to be no way to base a combobox's row source on
J> SQL (statement or sproc) and have it dynamically parameterized. I can set
J> the property to "EXEC my_sproc 1" and pass static parameters, but the only
J> way I can find to change this at run time is to use VBA to rewrite the
J> rowsource property. Now forms based on SQL data sources have the input
J> parameters property that are used for this situation but the individual
J> combo and list box controls don't. any one else noticed this? What gives? If
J> it's true then this seems like a rather large oversite on MS's part.

Yap, its true, that Microsoft sometimes (?) do not think
But maybe in next version of Office (I've started with Acc2k and
ADP before AccXP was developed, and before release the next version
I always hope that that kind of useful function will be implemented)

Regards
Jacek Segit

 

[Malcolm Cook]

Jon,

It can be done.

You have 2 options:

# 1 - use sproc, not statement, and name the parameter to the stored proc to
be EXACTLY THE SAME as the name of a form variable (or Access field) ON THE
SAME FORM AS THE COMBOBOX (except, that the sproc will have an "@" as 1st
character). MS Access programmers have tricked out Access to notice this
and dynamically update the underlying ADOCommand.

# 2 - since ACC2K you can set the recordset of a combobox to one that you
create yourself. You do this once on form_load. Then, you can write vb
proce to edit the ADODB.Parameters of the combo boxes recordset in any way
you like (i.e. setting parameter values from variables or a data structure
that does not have to abide by the naming convention in #1) before issuing
any requery. The timing logic of when to issue the requery is of course
up to you. If you factor your code well, this approach works quite nicely.
If you're interested in pursuing this approach, I may be able to help a
little further...