Access Cookbook

Create a Page Total

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?

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:

1. Create your report, and sort and group the data as desired. In the report’s page footer section, include a text box named txtPageTotal.

2. Create the following event procedure in the Format event of the page header and report header sections:

   Private Sub PageHeader0_Format(Cancel As Integer, FormatCount As Integer)
       [txtPageTotal] = 0
   End Sub
   
   Private Sub ReportHeader0_Format(Cancel As Integer, FormatCount As Integer)
       [txtPageTotal] = 0
   End Sub

3. Create an additional event procedure in the OnPrint event for the detail section:

   Private Sub Detail1_Print(Cancel As Integer, PrintCount As Integer)
       [txtPageTotal] = [txtPageTotal] + [Freight]
   End Sub

4. Save your report. When you run it, you will see the total of the field you set in the OnPrint event procedure.

Now load rptPageTotals from 03-07.MDB ...