Msaccess Report and Page Totals

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 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 Text Box 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.


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 on 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 Internet on basic usage of MS-Access (or that was the 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 on the look out for something different, advanced, interesting and need more exciting experience with MS-Access usage then this Website is primarily targeting Readers like you.


The majority of the crowd may be on the other side now but I am sure they will be on the look out for something better all the time. That is why VBA Code is the main driving force behind all the examples given on this Site.


So, let us try out part of this experiment without VBA Code. We need a simple Report to do that.

1. Import the following Tables from Northwind.mdb sample database (C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb):




• Order Details


• Products




2. Open a new Query in Design View, do not select a Table or Query from the displayed list and click Close.


3. Select SQL View from View Menu to display SQL Editing Window of the Query.


4. 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 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.


5. Click on the Product_ListQ Query and select Report from Insert Menu.


6. Select Auto Report: Tabular from the displayed list and click OK to create the Report.


7. Save the Report with the name Products_List.


8. Open the Report in Design View and change the design to look like the sample image given below.






9. 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 PageTotal, 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 Carry 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 Program to change the Caption value to TOTAL at 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 are trying to display values into the other Text Boxes 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.



10. Save the Report.



11. 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 summing up in this control and the last line has the total so far value on the Page. If we put a reference to 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.



12. Open the Report in design view and write the following expression in the Control Source Property of the CF Text Box:


=[QtyRunningSum]


13. Now, preview the Report again and check whether the last total value is appearing in the Page Footer area in CF Text Box or not. Advance to the next page and check the value appearing there too.


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 to take care of to get it right.


The first choice is to put a reference to the CF Text Box control in the BF Text Box like =[CF] to bring the Total Value from the previous page to the current Page Header.


The second one is to put a reference to 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.


Read the following Articles where we have discussed the Format and Print Events of the Reports and to learn few other Tricks we have tried earlier:


Hiding Report Lines Conditionally
Hiding Report Lines Conditionally-2
Hiding Report Lines Conditionally-3
Detail and Summary from same Report


14. 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 Text Box when we use Program for Page Total.


15. 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 on the Detail Section on 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 undergoes the Format and Print Events; the controls CF & BF internally keeps on populating with changed 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 from the first line running sum QtyRunningSum or previous Page CF Text Box Value we will get the BF Text Box value correctly.



16. 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.



17. Click on the QtyRunningSum Text Box, display the property sheet and change the Visible property value to No.



18. Save the Report and open it in Print Preview.

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


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

1. Create a copy of the Product_List Report and name it as Product_List2.


2. Open the Report in Design View.


3. 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 Program at 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.
   
   
   
4. Delete the QtyRunningSum Text Box from Detail Section.


5. Select Code from View Menu to display the Class Module of the Report.


6. Copy and paste the following Code into the Class Module, Save and Close the Report.




Option Compare Database

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



We have declared two Variables x_pagetotal and x_CF at the global area of the Module. At 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 PageTotal, CF Text Box values and resetting x_pagetotal Variable to zero. Here, we are checking whether the current Page Number is equal to the Last Page Number, if so then changes the lblCF Label Caption to TOTAL: .



7. 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 in 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 below, 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.

---

MS-Access and Reference Library
InputBox and Simple Menus
Change Form Modes On User Profile
Positioning POPUP Forms
Synchronized Floating Popup Form