Access Cookbook - Ken Getz [11]
Many of the examples in this chapter are based on a fictional music collection database that you could use to keep track of your favorite musicians and your album collection.
1.1. Specify Query Criteria at Runtime
Problem
When you design a query, you don't always know which subset of records you would like to see when you run the query. Instead of creating several queries with the same basic design but slightly different criteria, you'd like to be able to create one query that can be used to return the same fields, but a different set of records, each time it's run.
Solution
Use a parameter query with one or more replaceable parameters that it will request at runtime (when you run the query). This solution demonstrates how you can create and run parameter queries using the default parameter prompt.
Here are the steps to create a parameter query using default prompts:
Create any type of query in query design view.
Choose a field for which you wish to define a parameter. Create a parameter for that field by entering the prompt you would like to see when the query is executed surrounded by square brackets ([]) in the Criteria row for that field. For the example query qryAlbumsPrm1, you would create a parameter for the MusicType field by typing:
[Type of Music?]
in the Criteria row under MusicType.
Select Parameters from the Query menu to open the Query Parameters dialog, where you declare the parameter. For this example, enter:
Type of Music?
in the Parameter column of the Query Parameters dialog, and choose:
Text
from the data type combo box to tell Access that this is a text parameter. This step is optional in this query, but some queries require it (see Recipe 1.1.3), so make it a habit. Steps 2 and 3 are shown in Figure 1-1.
Figure 1-1. The qryAlbumsPrm1 parameter in design view
Save the query and run it. Access will prompt you to enter the type of music with a parameter dialog (see Figure 1-2).
Figure 1-2. The Enter Parameter Value dialog for qryAlbumsPrm1
To see how this works using the sample database, open 01-01.MDB and run the qryAlbumsPrm1 query. You will be prompted for the type of music. Enter a music type, such as rock, alternative rock, or jazz. The query will then execute, returning only the records of the specified music type. For example, if you enter "Alternative Rock" at the prompt, you'll see the datasheet shown in Figure 1-3.
Figure 1-3. The datasheet for qryAlbumsPrm1
Discussion
For queries with simple text parameters, you can get away without declaring the parameter using the Query → Parameters command. If you create parameters for crosstab or action queries, however, you must declare the parameter. We recommend that you get in the habit of always declaring all parameters to eliminate any chance of ambiguity. The entries you make in the Parameters dialog end up in the Parameters clause that is added to the beginning of the query's SQL, which you can see by selecting View → SQL View.
The result of a parameter query needn't be a query's datasheet. You can base reports, forms, and even other queries on a parameter query. When you run the object that is based on the parameter query—for example, a report—Access knows enough to resolve the parameters prior to running the report.
You can use parameters in any type of query, including select, totals, crosstab, action, and union queries.
1.2. 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