Dear Dorette:
I'll suggest two ways.
You could put the value into a control and reference that control from
the query. The control does not have to be visible for you to do
this.
You can also first look at "when" the value of this parameter is set,
and what might change it. Whatever events are involved in determining
the value of the parameter, you can have code in those events that
rewrites and assigns the RowSource of the Combo Box.
I strongly prefer the latter method. However, after preparing the new
SQL for the combo box, I always compare this to the current value of
the combo box RowSource, and then I do not set the RowSource if it
hasn't changed. This avoids having to reload the rows when there is
no change.
A problem occurs if someone may have changed the data behind the combo
box since it was loaded. In this case, the combo box will need to be
requeried even though the events that change the RowSource have not
occurred.
To handle this, it is best to requery the combo box on the activate
event. This handles the case of the current user having changed to
another form and altered the data behind the combo box, then returned
to the form.
I commonly do not try to handle the case of another user changing the
data behind the combo box, except to give the user a requery button to
update the data manually if he suspects there is a change.
In Access 2000, how can I pass a value to a parameter in a
stored procedure (Query) specified in the row source of a
combo box? Currently I am promted with a pop-up
requesting the value.
Thanks.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
