Msaccess Report and Page Totals ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

When designing reports in Microsoft Access, you might have noticed that functions like Sum, Count, or Avg don’t work in the Page Header or Page Footer sections. These aggregate functions only operate in the Report Header/Footer or Group Header/Footer areas — not on individual pages.

That might sound limiting, but there’s a workaround. You can still use simple expressions in the Page Header or Footer to display useful, dynamic information — no VBA code required.

For example, when you build a report using the Report Wizard, Access automatically adds expressions in the Page Footer to show the current date, time, and page numbers.

The expression =Now() prints the date and time.
The formula "Page " & [Page] & " of " & [Pages] displays the page numbering.

While these expressions can’t calculate page-wise totals, you can use them to show running sum values on each page — both in the header and footer. It’s a simple, elegant way to make your reports more informative without writing a single line of VBA.

The Targeted Readers of this website.

By going through this website, one might get the impression that to work with MS Access, you must know and use VBA code. This is not true. Many tasks can be automated using Macros. The first MS Access application I developed in 1997 for our Audit Department didn’t contain a single line of VBA code—and it’s still in use today. The only changes I’ve made to that application over the years were converting it from MS Access Version 2 to MS Access 97, then to MS Access 2000, and giving the main switchboard form a facelift.

You can find plenty of material on the Internet covering the basic usage of MS Access (or at least that was my impression when I started this website). With these resources, you can quickly learn the essentials—Table Design, Relationships, Queries, Forms, Reports, and Macros. Once you’ve mastered the basics and are ready for something more advanced, interesting, and exciting, this website is designed especially for you.

When you’re ready to move beyond the basics, explore the contents here. VBA code is the main driving force behind all the examples presented on this site.

Trial Run Without VBA Code

Therefore, let us try this experiment without the VBA Code. We need a simple Report to do that.

Import the following Tables from the Northwind sample database (C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb):
- Order Details
- Products
Open a new Query in Design View, do not select a Table or Query from the displayed list, and click Close.
Select SQL View from the View Menu to display the SQL Editing Window of the Query.
Copy and paste the following SQL String and save the Query with the name Products_ListQ:
```
SELECT [Order Details].OrderID, Products.ProductName, [Order Details].Quantity
FROM [Order Details] INNER JOIN Products ON [Order Details].ProductID = Products.ProductID
WHERE ((([Order Details].OrderID) Between 10248 And 10300));
```
We have selected only a few records from the Order Details Table, with OrderID numbers between 10248 and 10300, so that the sample Report will have only a few pages in Portrait Mode.
Click on the Product_ListQ Query and select a Report from the Insert Menu.
Select Auto Report: Tabular from the displayed list and click OK to create the Report.
Save the Report with the name Products_List.
Open the Report in Design View and change the design to match the sample image given below.

Right-click to open Large Image in New Tab/Window.
Add the Red-colored text boxes and Labels to the Design of the Report as explained below:
- Make a copy of the Quantity field in the Detail Section and position it to the right as shown.
- Display the Property Sheet (View ->Properties) of the copied control and change the Name Property value to QtyRunningSum.
- Change the Running Sum Property Value to Over All.
- Create a Text Box on the Page Header of the Report and change the Name property value of the Text Box to BF (stands for Brought Forward), the Border Color Property Value to 128, and the Font Weight Property Value to Bold.
- Change the Caption value of the Child Label to B/F:.
- Create a Text Box on the Page Footer of the Report, change the Name Property Value to Page-Total, Border Color Property Value to 128, and the Font Weight Property Value to Bold.
- Change the Caption of the attached Child Label to Page Total, as shown in the sample design above.
- Create another Text Box to the right, change the Name Property Value to CF (stands for carrying Forward), Border Color Property Value to 128, and the Font Weight Property Value to Bold.
- Change the Name Property Value of the child label to lblCF and the Caption property value to C/F:.
We have changed the Label's Name property value to lblCF so that we can address it in the Program to change the Caption value to TOTAL on the Last Page of the Report. At that point, it is not appropriate to show the label as C/F: (Carry Forward). We will do this with the Program while creating Page Totals on the Report.
Before that, we will display values in the other TextBoxes created for Running Sum, BF, and CF values without a VBA Program.
At a later stage, we will hide the Quantity field copied for calculating Running Sum, but keep it visible now, and use it for calculations on the other two controls, BF and CF, in the Page Header and Page Footer Sections of the Report, respectively.
Save the Report.
Open the Report in Print Preview and check how the Values are appearing in the copied Quantity Field with the Running Sum Property Value set to Overall All.
Each Quantity value is summed up in this control, and the last line has the running sum value on the Page. If we put a reference in the QtyRunningSum Text Box in the CF Text Box in the Page Footer area, we can transfer this Value into that Control, showing the cumulative total value at the end of each page.
Open the Report in design view and write the following expression in the Control Source Property of the CF Text Box:
=[QtyRunningSum]
Now, preview the Report again and check whether the last total value is appearing in the Page Footer area in the CF TextBox or not. Advance to the next page and check the value showing there, too.
The next step is to create the Page Header Control BF Value. If you are smart, then you might have already made the change in the BF control.
You have two choices to put the Value in this Control, but both have a problem getting it right.
The first choice is to put a reference to the CF TextBox control in the BF TextBox, like =[CF], to bring the Total Value from the previous page to the current Page Header.
The second option is to place a reference in the QtyRunningSum text box, such as =[QtyRunningSum]. This approach is also logically correct, assuming that the BF control retrieves the cumulative total from the previous page. This is because the QtyRunningSum control in the Detail section of the current page is processed after the Page Header section’s Format and Print events.
Read the following Articles where we have discussed the Format and Print Events of the Reports, and learn a few other Tricks we have tried earlier:
- Hiding Report Lines Conditionally
- Hiding Report Lines Conditionally-2
- Hiding Report Lines Conditionally-3
- Detail and Summary of the same Report
Report Line hiding Tricks tried earlier
Choose one of the two options mentioned above and set the Control Source property to either =[CF] or =[QtyRunningSum]. Personally, I prefer the first option, as we’ll be deleting the QtyRunningSum text box when we implement the program for the page total.
Open the Report in Print Preview and check the Page Header Section BF Text Box Value.
In both cases, you will get the same result: the starting running sum value of the first line in the Detail section of the current page. In other words, the value in the BF control equals the cumulative total from the previous page plus the quantity value of the first line on the current page. This happens because, even after the Page Header/Footer control values go through the Format and Print events, the CF and BF controls continue to update internally with the changing running sum values. If this were not the case, we could have easily calculated the page total by simply taking the difference between the CF and BF control values on the same page.
So, if we subtract the first line Quantity value of the first line running sum QtyRunningSum or previous Page CF Text Box Value, we will get the BF TextBox value correctly.
This is the reason why we depend on VBA Code to solve the Page Total problem.
Change the expression in the BF Text Box in the Page Header Section as follows:
- =[CF] - [Quantity]
  Or
- =[QtyRunningSum] - [Quantity]
We don't want the QtyRunningSum to display now, and we will hide it from the Detail Section.
Click on the QtyRunningSum Text Box, display the property sheet, and change the Visible property value to No.
Save the Report and open it in Print Preview.

Now, the Page Header Text Box BF and Page Footer Text Box CF Values are shown correctly. Move to the next page and check the values appearing there.

We will take a copy of this Report to our Page Total example. We will use VBA Code to calculate and update the Page Total and CF values on the new Report.

New Report with VBA Code.

Create a copy of the Product_List Report and name it Product_List2.
Open the Report in Design View.
Modify the expression in the Control Source Property of the BF Text Box in the Page Header as =[CF].
We will calculate and update only the CF Text Box Value through the Program in the Page Footer Section.
Since we are not depending on the Running Sum value, we don't have to worry about the side effect we experienced in the earlier method. The correct value will be picked from the previous page control CF Text Box.
Delete the QtyRunningSum Text Box from the Detail Section.
Select Code from the View Menu to display the Class Module of the Report.
Copy and paste the following Code into the Class Module, save and close the Report.

The Report Class Module VBA Code
```
Option Compare Database
'gobal declarations
Dim x_pagetotal As Long, x_CF As Long

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount = 1 Then
   x_pagetotal = x_pagetotal + [Quantity]
   If Retreat = False Then
         x_CF = x_CF + [Quantity]
   End If
End If
End Sub

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount = 1 Then
    [PageTotal] = x_pagetotal
    x_pagetotal = 0
    [CF] = x_CF
End If
If [Page] = [Pages] Then
    Report.Controls("lblCF").Caption = "TOTAL:"
End If
End Sub
```
Review of Code.
We have declared two variables, x_pagetotal and x_CF, in the global area of the module. In the Detail Section Print event procedure, we add the quantity values to both variables. In the Page Footer Print event procedure, we update the page total and the CF text box values, then reset the x_pagetotal variable to zero. At this point, we need to check whether the current page number is the same as the last page number. If it is, we change the lblCF label caption to “TOTAL:”.
Open the Report in Print Preview and check the Page Total, BF, and CF Text Box Values. Move to the next page, and the control values are there.

The Report image with cut Sections of the Page Header/Footer areas showing the Control Totals on page number three is given below.

Right-click to open Large Image in New Tab/Window.

Cation: I would like to remind you of another important side effect of this method, which I have explained earlier. Since the program relies on page-by-page calculations to generate the correct control totals on each page, jumping to a distant page by typing the page number in the Page Number control—and skipping one or more pages in between—will result in incorrect control values.

If the report is printed directly to the printer, without previewing or navigating between pages as described above, it will print with the correct values on each page.