You've created a report based on a parameter query that prompts the user for one or more parameters when the query is run. The report works just fine, but you'd like to be able to document somewhere on the report what parameter values were entered by the user. That way you'll know, for example, which years' records are included in the report. Is there any way to do this with Access?
You can print the values of query parameters on a report by referring to the parameters as if they were fields in the underlying query. This solution shows you how to create controls on a report that document the user-entered runtime parameters.
Load the 03-02.MDB database and open the qryAlbumsPrm query in design mode to verify that this query has three parameters (Figure 3-3). Now open the rptAlbumsPrm in preview view. Because this report is based on qryAlbumsPrm, you will be prompted for the three parameters.
Enter your values at the parameter prompt. If you enter the parameter values from Table 3-1, you should see a report that looks similar to the one shown in Figure 3-4.
Parameter |
Sample value |
---|---|
Type of music? |
Rock |
Starting year? |
1960 |
Ending year? |
1979 |
Notice that the selected parameters are included in the page header of the report. Run the report again, entering different parameters, and verify that the new parameters are correctly printed on the report.
Follow these steps to print the values of query parameters on your own report:
Create a query with one or more parameters. If you aren't sure how to do this, read the Solution in Recipe 1.1. Don't forget to declare your parameters using the Query Parameters command (see Figure 3-3). In the sample database, we created a parameter query named qryAlbumsPrm with three parameters.
Create a report based on the parameter query from Step 1. In the page header of the report (or any other section you'd like), create text boxes that reference the parameters as if they were fields in the underlying query. Surround each parameter reference with square brackets. We used two text boxes in the rptAlbumsPrm sample report, as summarized in Table 3-2.
Text box name |
Control source |
---|---|
txtMusic |
="Music Type: " & [Type of music?] |
txtYears |
="Years: " & [Starting year?] & " to " & [Ending year?] |
|
During report design, you are free to reference any "unknown" you'd like as long as you put brackets around it. (If you don't put brackets around it and it's not a field in the underlying record source, Access thinks you entered a string constant and forgot to surround it with quotes, so it puts the quotes in for you.) When you run the report, Access tries to locate the unknown references. If it locates a query parameter or form control that satisfies the reference, it copies the value into the control and continues running the report. If it can't locate the unknown reference, however, it puts up a parameter dialog, requesting help in locating that unknown piece of data.
|
You can also create parameters directly on reports that are independent of query parameters. For example, you might use this type of "report parameter" if you create a report that requires a person's name and signature at the bottom of a page when you know that the name will vary every time you run the report (and cannot be obtained from the report's record source). Simply add a text box that references the new parameter?for example, [Enter signature name:]. Access will prompt you for this report parameter when you run the report, just as if you had defined the parameter in the report's underlying query.