I would like to have a querry when executed to provide a dropdown box to
select the parameter to ensure the user gets the correct spelling
Not a good idea to filter data by a Name field. More than one person
or company can have the same name. Use an ID field instead.
You will need a form form this.
This example uses SupervisorID and SupervisorName fields to filter by.
Substitute your field names.
First, create a query that will display the fields you wish to show
Next, make a new unbound form.
Add a combo box that will show the SupervisorID field as well as the
Supervisor Name field and Department field (you can use the Combo Box
wizard to do so).
Set the Combo box's Column Count property to 3.
Hide the SupervisorID field by setting the Combo box's ColumnWidth
property to 0";1";1"
Make sure the Combo Box Bound Column is the
SupervisorID field (Column 1).
Name this Combo Box "cboFindName".
Add a command button to the form.
Code the button's Click event:
DoCmd.OpenQuery "QueryName"
DoCmd.Close acForm, Me.Name
Name this form "ParamForm"
Go back to the query. As criteria, on the Query's SupervisorID field
criteria line write:
forms!ParamForm!cboFindName or forms!ParamForm!cboFindName Is Null
When you wish to run the query, first open the form.
Find the correct SupervisorName in the combo box.
Click the command button.
The Query will display just those records selected and the ParamForm
will be closed. If no name is selected, all the records will be
displayed.
