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

Introduction

Ms-Access Functions Sum, Count, Avg, etc., cannot be used in Page Header or Footer Areas of Reports for creating Page-wise summary values. These will work in Report Group Header/Footer or in Report Header/Footer areas only.

That doesn't mean that you cannot do any calculations on Page Header/Footer Areas. You can write expressions to print useful information without using the above categories of functions.

For Example: When you design Reports with the built-in Report Wizard you have seen MS-Access uses expressions in the Page Footer Area to display Date/Time and Page Numbers.

The Report Wizard uses the Function =Now() in a TextBox to print Date and Time and the Expression ="Page " & [Page] & " of " & [Pages] to display Page Number information in the Page Footer area.

Even though we cannot use this method to calculate and print Page-wise Total Values, we can use it to print the Running Sum values on every page in Page Header and Page Footer Areas.

We don't have to struggle with any VBA Code for this.

The Targeted Readers of this website.

By going through the contents of this website one gets the general impression that if you want to do something in MS-Access then you must know and use VBA Code. This is not true. You can do plenty of task automation with Macros. The first Ms-Access Application that I have developed in 1997 for use in our Department is without a single line of VBA code and it is still in use. The only change that I have made to that Application is to convert it from MS-Access Version 2 to MS-Access97, later to MS-Access2000, and modified the Main Switch Board Form to give it a better look.

You can get tons of materials from the Internet on basic usage of MS-Access (or that was a general impression that I had when I started this Website) and quickly attain the basic knowledge on Table Design, Relationships Design, Queries, Forms, Reports, and Macros. Once you are through with the basics and when you are on the lookout for something different, advanced, interesting, and need a more exciting experience with MS-Access usage then this Website is primarily targeting Readers like you.

Once you are bored with the basic stuff and on the lookout for something better browse the contents on this website. Here, the VBA Code is the main driving force behind all the examples given on this Site.

Trial Run Without VBA Code

So, let us try out part of 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 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; from OrderID numbers between 10248 and 10300; so that the sample Report will have only about five or six pages in Portrait Mode.
Click on the Product_ListQ Query and select a Report from 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 look like the sample image given below.
Add the Red-colored TextBoxes 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 on 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 this 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 try to display values into the other TextBoxes we have created for Running Sum, BF, and CF values without VBA Program.
At a later stage, we will hide the Quantity field copied for calculating Running Sum but we will keep it visible now and use it for calculating values, on the other two controls BF and CF on 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 Over All.
Each Quantity value is summed up in this control and the last line has the total so far 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 CF TextBox or not. Advance to the next page and check the value appearing 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 by now without reading further down.
You have two choices to put the Value in this Control but both have a little problem taking care of to get 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 one is to put a reference in the QtyRunningSum Text Box, like =[QtyRunningSum]. This is also logically correct assuming that the BF control will take the total cumulative value from the previous page because the QtyRunningSum control in the Detail Section on the current page comes after the Page Header Section Format and Print Events.
Report Line hiding Tricks tried earlier
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:
Choose one of the two options I have presented above and change the Control Source Property Value with either the expression =[CF] or with =[QtyRunningSum]. If you ask me, I prefer the first one because we are going to delete the QtyRunningSum TextBox when we use the Program for 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 of the Detail Section of the current page. In other words, the Value in the BF control is equal to the cumulative value of the previous page plus the Quantity Value in the first line of the current page. This is because even after the Page Header/Footer control values undergo the Format and Print Events; the controls CF & BF internally keep on populating with changing Running Sum values. If this was not the case we could have easily calculated the Page Total value by finding the difference between CF & BF control values on the same page.
This is the reason why we have to depend on VBA Code to solve the Page Total problem.
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.
Change the expression in the BF Text Box in the Page Header Section as follows:
- =[CF] - [Quantity]
  Or
- =[QtyRunningSum] - [Quantity]
We don't need the QtyRunningSum 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 make a copy of this Report to our Page Total example. We will use VBA Code to calculate and update 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 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 have 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 Detail Section.
Select Code from 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 are adding up the Quantity Values in both Variables. At the Page Footer Print Event Procedure we are updating the Page Total, CF TextBox values, and resetting the x_pagetotal Variable to zero. Here, we are checking whether the current Page Number is equal to the Last Page Number, if so then 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 check the control values there too.

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

NB: Here, I would like to remind you about the other serious side effect of this method which I have already explained earlier. Since the Program is depending on the Page by Page calculations to arrive at the correct Control Totals on each page, if you jump from one page to a distant page and skip one or more pages in between, by typing the Page Number in the Page Number Control, then these control values will not be correct.

If the Report is printed directly to the Printer, without Previewing and moving between pages as explained above, it will print with correct values on each page.

LEARN MS-ACCESS TIPS AND TRICKS