Query with drop down box

[Guest]

Hi,

i wanted to create a query that would run and like a parameter query you
have enter a criteria and then run the query, is there a way of creating a
query that provides you with a drop down box or list so that you can choose
the values/criteria you want?

Thanks

 

[Michel Walsh]

Sure, start by opening a form! and in the combo box after update event,
write code to start the query, getting the parameter from the combo box in
the form. So, instead of starting with a query, you start with a form, but
the end result is the same... you get a parameter of a query read from a
form's combo box. Egg-chicken who's first kind of problem, really



Vanderghast, Access MVP

 

[fredg]

Hi,

i wanted to create a query that would run and like a parameter query you
have enter a criteria and then run the query, is there a way of creating a
query that provides you with a drop down box or list so that you can choose
the values/criteria you want?

Thanks

No, but if you use a form you can reference the combo box on the form
in the query.

Let's assume you wish to use the combo box to find a particular
Company.

Make a new unbound form.
Add a combo box that will show the CompanyID field (as well as the
Company Name) fields.
Make sure the Combo Box Bound Column is the
CompanyID field.
Set the combo column width property to:
0";1"

Add a command button.
Code the button's Click event:
DoCmd.OpenQuery "QueryName"
DoCmd.Close acForm, Me.Name
Name this form "ParamForm"

Code the Query CompanyID field criteria line:

forms!ParamForm!ComboBoxName

When you wish to run the query, open the form.
Find the Company Name in the combo box.
Click the command button.

The query will display just those records for the CompanyID selected.
The Form will close.

Note... this is to run the query as a stand-alone query.
If the query is used as record source for a report, you need to use a
similar, but different, method.