Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 1.2 Using a Form-Based Parameter Query

1.2.1 Problem

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.

• You
  can't write any VBA event procedures to run behind
  the Parameters dialog.

1.2.2 Solution

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
prompt:

1. 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
   yetwe 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
   1975."

2. Create
   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.

3. 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
   looks like:

   Private Sub cmdCancel_Click( )
   DoCmd.Close
   End Sub
   Private Sub cmdOK_Click( )
   DoCmd.OpenQuery "qryAlbumsPrm2", acViewNormal, acEdit
   End Sub

4. 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:

   Forms![frmAlbumsPrm2]![cboMusicType]

   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]

5. 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.

6. Save the query and close it.

7. 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

1.2.3 Discussion

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!