ACC1x: How to Print Total Number of Pages on Each Page of Report

• Microsoft Access versions 1.0, 1.1

SUMMARY

You can print the page number on each page of a report by using the Page property in a text box control in the page footer. The example below also shows you how to print the total number of pages in the report on each page (for example, 1/<n>, where <n> is the total number of pages).

The sample program below uses the SendKeys statement to go to the end of the report, stores the total number of pages in a global variable, then returns to the beginning of the report to print or preview the report.

NOTE: This method may not work with all reports. Below are four known problems with this method:

• If the report window is zoomed (maximized), the END key in the SendKeys statement will not work correctly.

  By default, reports open with the report window zoomed. Pressing Z unzooms the screen and pressing the END key moves you to the end of the report so that the code below can calculate the total number of pages. However, if the report window is zoomed, the END key moves the cursor to the right corner of the screen instead of going to the last page of the report. The SendKeys statement in the GotoEnd() function contains the key combination ALT+Z (or %Z) to toggle the zoomed mode off. If your report displays a 0 for total pages, try omitting the %Z from the SendKeys statement.

• If a query takes a long time to process, Microsoft Access can yield processor control to Windows, which empties the keyboard buffer. When this happens, the GotoEnd() function (which uses SendKeys) fails, the SendKeys data is lost, and the total number of pages listed may be wrong. (This problem most often occurs when a report contains subreports.)
• If your report contains subreports, this method does not calculate the total number of report pages correctly. Currently, there is no workaround for this behavior. A change to this behavior is being reviewed for inclusion in a future version of Microsoft Access.
• If you use an OpenReport macro action where the report is sent directly to the printer, this will fail because the functions are not allowed to go to the end of the report and back again to create the total page number variable.

MORE INFORMATION

To print the total number of pages in a report at the bottom of each page, do the following:

1. Open a new module or a previously created module and enter the following sample code:

   NOTE: In the following sample code, an underscore (_) is used as a line-continuation character. Remove the underscore when re-creating this code in Access Basic.

         '*************************************************************
         'Declarations section of the module
         '*************************************************************
         Option Explicit
         Global TotalPages as Integer
   

         '*************************************************************
         'Create the following functions in the module.
         '*************************************************************
         Function GetTotalPages()
            GetTotalPages = TotalPages
         End Function
   
         Function GotoEnd()
            TotalPages = 0
            SendKeys "%z{END}", False
            ' SendKeys does not work if report is maximized
         End Function
   
         Function SaveTotalPages(TotPgNum As Integer)
            If TotalPages = 0 Then
               TotalPages = TotPgNum
               SendKeys "{HOME}", False
            End If
         End Function
   
   

2. Open your report in Design view.

3. From the View menu, choose Properties to display the property sheet. Add the following function to the report's OnOpen property:

         OnOpen: =GotoEnd()
   

4. Place a text box control called MyPage in the page footer:

         Object: Text Box
         ----------------
         ControlName: MyPage
            ControlSource: =Page
            Visible: No
   
   

5. Change the report footer's OnFormat property to:

         OnFormat: =SaveTotalPages([MyPage])
   

6. Place a text box control in the page footer as follows:

         Object: Text Box
         ----------------
         ControlName: PageNum
            ControlSource: =Page & "/" & GetTotalPages()