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

Recipe 3.2 Print the Value of a Parameter on a Report

3.2.1 Problem

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?

3.2.2 Solution

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.

Figure 3-3. The qryAlbumsPrm parameter query includes 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.

Figure 3-4. The rptAlbumsPrm report includes the parameter values in the header

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:

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

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

These parameter fields will not be listed in either the field list window or the drop-down list of fields in a control's ControlSource property.

3.2.3 Discussion

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.

If you run a report and get a parameter dialog when you didn't expect one, it's likely that you misspelled either a field name or a reference to a query's parameter.

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