Recipe 1.2 Using a Form-Based Parameter Query
The default type of
parameter query is useful but has several drawbacks:
You get one Enter Parameter Value dialog for each parameter. Since
these are sequential, you can't return to a previous
dialog to change an incorrect value.
You can't select the value from a combo box or use a
format or input mask, which makes it likely that the user will enter
invalid data or data not found in the database.
can't write any VBA event procedures to run behind
the Parameters dialog.
Use a form-based parameter query by creating a more user-friendly
form that collects the parameters.
Here are the steps to create a parameter query using a form-based
Decide how many parameters you will define for the query, in what
order you would like them to be presented to the user, and what type
of form control you would like to use for each parameter. For the
qryAlbumsPrm2 query shown later, in Figure 1-4, we
defined three parameters, as shown in Table 1-1.
(Don't worry about the last column in the table
yet—we will discuss it soon.) Note that we included two
parameters for the Year field so we could select rows based on a
range of years, such as "between 1970 and
Table 1-1. Parameters for qryAlbumsPrm2
an unbound form with controls that will be used to collect the
query's parameters. For qryAlbumsPrm2, we created a
form named frmAlbumsPrm2 with three controls that will be used to
collect the parameters from Table 1-1. All three
controls are unbound; that is, they have no entry for the
ControlSource property. We named the text boxes txtYear1 and
txtYear2. We also created a combo box called cboMusicType to allow
the user to select the type of music from a list of music types. You
can use the combo box control wizard to assist you in creating this
control, or you can create it by hand. If you decide to create it by
hand, select Table/Query for the RowSourceType property and
tblMusicType for the RowSource (not the ControlSource). Leave all the
other properties at their default settings.
Add one command button to the form
that will be used to execute the query and another that will be used
to close the form. For frmAlbumsPrm2, we created two buttons with the
captions OK and Cancel. To accomplish this, you can use the command
button wizard, which will write the VBA code for you.
Here's what the code in the two event procedures
Private Sub cmdCancel_Click( )
Private Sub cmdOK_Click( )
DoCmd.OpenQuery "qryAlbumsPrm2", acViewNormal, acEdit
Create the query. You will now create the parameters that reference
the controls on the form created in Steps 2 through 4. You create
form-based parameters a little differently than default parameters.
Instead of creating a prompt surrounded by square brackets, you will
enter references to the form control for each parameter. For
qryAlbumsPrm2, create the parameters shown in Table 1-1. In the MusicType field, enter:
Enter brackets only around each form and control reference, not
around the entire parameter. For the Year field, enter:
Between Forms![frmAlbumsPrm2]![txtYear1] And Forms![frmAlbumsPrm2]![txtYear2]
Select Query Parameters to declare the data types of the
parameters. Use the same parameter names you used in the previous
step. Choose the data types shown in Table 1-1.
Save the query and close it.
Open the parameter form in form view. Select or enter each of the
parameters. Click on the OK button to execute the parameter query,
returning only the rows selected using the parameter form.
To see how a form-based query works using the sample database, open
the frmAlbumsPrm2 form in 01-02.MDB (see Figure 1-4). This form collects three parameters for the
parameter query qryAlbumsPrm2. Choose the type of music from the
combo box and the range of years to include in the two text boxes.
Click on the OK button to execute the parameter query using the
parameters collected on the form.
Figure 1-4. The form-based parameter query, qryAlbumsPrm2
When you add a parameter to the
criteria of a query, Access knows that it needs to resolve that
parameter at runtime. You must either reference a control on a form
or enter a prompt surrounded by square brackets to let Access know
you wish to use a parameter. If you don't use the
brackets, Access will interpret the entry as a text string.
When Access runs a query, it
checks to see if there are any parameters it needs to resolve. It
first attempts to obtain the value from the underlying tables. If it
doesn't find it there, it looks for any other
reference it can use, such as a form reference. Finally, if there is
no form reference (or if you created a form-based parameter and the
form is not open), Access prompts the user for the parameter. This
means that you must open the form prior to running any parameter
queries that contain references to forms.
Parameter dialogs can sometimes be a symptom of an error in the
design of one or more objects in your database. If you ever run a
query, form, or report and are prompted for a parameter when you
shouldn't be, you probably misspelled the name of a
field or renamed a field in a table without changing the reference in
the query. Access sometimes creates queries on its own to support
subforms or sorting and grouping in reports. You may need to check
the LinkChildFields or LinkMasterFields properties of a subform or
the Sorting and Grouping dialog of a report to find the unrecognized
term that is triggering the errant Enter Parameter Value dialog.
Also, if you change a parameter in the query grid, remember to change
it in the Parameters dialog too!