Recipe 3.7 Create a Page Total

3.7.1 Problem

Access allows you to create a group total in the group footer on a report or a report total on the report footer, but you can't find a way to create a page total in the page footer. You understand that this problem doesn't come up too often, but for your report you could really use this element. Is there a way to sum up values over a single page?

3.7.2 Solution

It's true that Access allows aggregate calculations only in group or report footers. You can, however, easily create page totals using two simple macros. This solution demonstrates this technique and shows how to add this capability to any of your own reports.

To create page totals for your own reports, follow these steps:

Now load rptPageTotals from 03-07.MDB in preview view (see Figure 3-14). This report is used to track orders and their freight costs. The items are grouped by month, and each group has a total in the group footer. At the bottom of each page, you'll see the total for all items on the current page.

Figure 3-15 shows the sample report in design view.

Figure 3-14. Page 2 of the rptPageTotals report with page totals

Figure 3-15. rptPageTotals in design view

3.7.3 Discussion

Access makes it simple to sum values in group or report footers: use the Sum function in the ControlSource property for a text box. For example, to sum the freight costs in either a group footer or a report footer, you could use an expression like this:

=Sum([Freight])

and Access would perform the sum over the range included in the footer section (for either the group or the entire report). To create a page total, however, you must dig a bit deeper into the way Access prints reports.

The report-printing engine in Access works as a forward-marching machine: the engine formats and then prints each section in turn, such that each section is handled in the order in which it appears on the page. The report-printing engine deals first with the report header, then any page header, then any group header, then each row of the detail section, and so on. At each point, Access allows you to "hook" into various events, doing work alongside its work.

The two events described in this solution are the Format event and the Print event. Normally, you'll attach a VBA procedure to the Format event of a section if you want to affect the section's layout on the page. You'll use the Print event to make calculations based on the data as you know it's going to print. When Access calls your macro or VBA code from the Print event, you are guaranteed that the current row is going to be printed. You can't assume this from the Format event, because Access calls the code attached to the Format event before it decides whether or not the current row will fit on the current page. From either event, you have access to the current row of data that's about to be printed, and you can use that as part of your event procedure.

In this case, calculating a page total requires two steps: you must reset the page total for each page (and before you start printing the report), and you must accumulate the value in each row as you print the row.

The accumulation part is simple: every time you print a row, the procedure attached to the detail section's Print event adds the value in the current row's Freight field (or whatever field you're tracking on your own report) to the current value in txtPageTotal. When Access needs to print the page footer, that value is filled in and ready to print. The event procedure should be written on the Print event, not the Format event, to ensure that you never add a value to the page footer unless you're sure the row will be printed on the current page. Calling the code from the Print event guarantees this.

You can reset the page total so it starts from zero from the Format event of the page header section. Because this is the first section that will print on every page, resetting the total in the page header should work. You could use the Print event here, but because you're guaranteed that the page header section will fit on its page, you might as well do the work as early as possible. The problem here arises from the fact that, in some reports, you may tell Access to print the page header only on pages where there isn't a report header (see the report's PageHeader property). If you do this, Access won't format the page header on the first page, and it therefore won't call the necessary code. To make up for this, the example report (rptPageTotals in 03-07.MDB) also calls the code from the report header's Format event. Because this event occurs only when Access prints the first page, there's no redundancy here. You may not need to reset the page total from the report header, but it can't hurt.

Be wary of performing any calculations during a section's Format event. Because you aren't guaranteed that the section will actually print on the current page, you could be calculating based on a value that won't be a part of the page. Making this mistake in the sample report, for example, would be a major error. Because this report is set up so that Access will print a group only if the entire group can fit on a page, it might format a number of rows, then decide that the whole group can't fit. Each time it attempts to format a row, it will call the code attached to the Format event, which will add the value to the total. To avoid this problem, perform calculations from a section's Print event only. Use the Format event to change the layout of a section—for example, to make a specific control visible or invisible, depending on the data you find in the current row (see the Solution in Recipe 3.4 for an example of this usage).