Access Cookbook

Using a Form-Based Parameter Query

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.

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

   Table 1-1. Parameters for qryAlbumsPrm2

   Query field	Data type	Control type	Parameter reference
   MusicType	Text	Combo box	Forms!frmAlbumsPrm2!cboMusicType
   Year	Integer	Text box	Forms!frmAlbumsPrm2!txtYear1
   Year	Integer	Text box	Forms!frmAlbumsPrm2!txtYear2

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

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.

Tip

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!