• Choose a category
• All
• Classic-Fiction

Setting the RunningSum property to Over Group or Over All tells Access to print the value of the first record as it would normally (in this case, to print 1) but, for the second record, to take the value of the first record and add it to the value of the second record, printing the cumulative total instead of the value it would normally print (in this case, 2). For the third record, Access adds the value of the second record (which is really a sum of the first and second records' values) to the value of the third record (in this case, 3). This accumulation of values continues until the end of the report (if you set RunningSum to Over All) or until the beginning of the next group (if you set RunningSum to Over Group).

You can use RunningSum to accumulate nonconstant values, too. For example, if you want a running total of the weight of evidence items in the rptEvidenceByCase report for each record, you can add a second Weight text box control to the right of the existing Weight control, making the second control identical to the first but this time setting RunningSum to Over Group. You'll also find the RunningSum property useful for financial reports for which you'd like to include a cumulative year-to-date column.

3.2. Print the Value of a Parameter on a Report

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?

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.

Table 3-1. Parameters and sample values for qryAlbumsPrm

Parameter

Sample value

Type of music?

Rock

Starting year?

1960

Ending year?

1979

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:

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.

Table 3-2. These two text boxes reference three parameters from the underlying query

Text box name

Control source

txtMusic

="Music Type: " & [Type of music?]

txtYears

="Years: " & [Starting year?] & " to " & [Ending year?]

WARNING

These parameter fields will not be listed in either the field list