Use combo box to set parameter for query

[Guest]

I know how to create a parameter query, but is it possible to get the dialog
box that it invokes to display a drop-down list of potential values instead
of requiring the user to type in a value off the top of their head? ...maybe
get the values from existing entries in the underlying table or something?
Is this crossing the line into VBA?? Thanks.

 

[John Vinson]

I know how to create a parameter query, but is it possible to get the dialog
box that it invokes to display a drop-down list of potential values instead
of requiring the user to type in a value off the top of their head? ...maybe
get the values from existing entries in the underlying table or something?
Is this crossing the line into VBA?? Thanks.

You can do this pretty easily.

Create a little unbound form, let's call it frmCrit.

Put a Combo Box on it using as rowsource the list of values that you
want the user to have available for selection. This will typically be
a Query sorted appropriately; the bound column will be the field to be
searched for, which need not be the *visible* column of the combo. For
instance, you might have a Customers table with a CustomerID,
lastname, firstname, etc.; you could have CustomerID as the bound
column but Lastname, Firstname as the first visible column. Let's call
this combo box cboCrit.

Use a query criterion of

=[Forms]![frmCrit]![cboCrit]

It's convenient to put a command button on frmCrit (using the wizard)
to open a Report or Form based on the query; it is rarely or never
appropriate to open the query datasheet directly.

John W. Vinson[MVP]

 

[Guest]

Thanks! I guess I was so bent on the idea of changing the existing dialog
that I didn't consider this angle.

I know how to create a parameter query, but is it possible to get the dialog
box that it invokes to display a drop-down list of potential values instead
of requiring the user to type in a value off the top of their head? ...maybe
get the values from existing entries in the underlying table or something?
Is this crossing the line into VBA?? Thanks.

You can do this pretty easily.

Create a little unbound form, let's call it frmCrit.

Put a Combo Box on it using as rowsource the list of values that you
want the user to have available for selection. This will typically be
a Query sorted appropriately; the bound column will be the field to be
searched for, which need not be the *visible* column of the combo. For
instance, you might have a Customers table with a CustomerID,
lastname, firstname, etc.; you could have CustomerID as the bound
column but Lastname, Firstname as the first visible column. Let's call
this combo box cboCrit.

Use a query criterion of

=[Forms]![frmCrit]![cboCrit]

It's convenient to put a command button on frmCrit (using the wizard)
to open a Report or Form based on the query; it is rarely or never
appropriate to open the query datasheet directly.

John W. Vinson[MVP]