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