Reports: Printing First and Last Page Numbers for Report Groups

Often times it is necessary to print page numbers for groups of records, and not just the overall pages for a report. Access has no built in way to do this, and some workarounds can be very cumbersome. Here's a simple code sample that does the job well, using arrays to handle the group page numbers; an often overlooked method of handling multidimensional data. Open any report in design view and open its Code-Behind-Reports module. Enter the code as listed.

Note: the line Me!Salesperson should be changed to the control name of the group you wish to track pages for. Me!ctlGrpPages is the name of a control you should place in the page footer. You can also change this line to whatever form you wish your page numbers to take.

The code works by filling two arrays with page numbers. GrpArrayPage() holds the group page number, and GrpArrayPages() hold the total number of pages for the group.

When Access formats a report, it often times must make two passes through the report for information such as Page 1 of 10; since Access cannot know how many pages the report will contain until the entire report is formatted. You can use the fact that access makes two passes to create your group page numbers. Because the code listed uses the Pages Property (Total number of pages), you automatically force access to format the report twice.

ReDim Preserve GrpArrayPage(Me.Page + 1)
ReDim Preserve GrpArrayPages(Me.Page + 1)

Since arrays take up memory, and we have no way of knowing how many pages will be in the report, we can dynamically resize the arrays with each pass. Using the Preserve keyword allows you to resize the array without losing any data.

If Me.Pages = 0 Then
  ...
Else
    Me!ctlGrpPages = "Group Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page)
End If

How the code works:

Until access has finished formatting the report for the first pass, the value of the Pages property will be 0; so you can check this value to determine if this is the first or second pass. The first pass is explained below, and the second pass just sets the control in the page footer to the value in the arrays.

On the first pass through the formatting of the report, the code uses the group name and page property to build the group page numbers. Using the current page number as the index number for the array, makes the code a bit simpler.

GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page - 1) + 1

This code sets the value of GrpArrayPage(me.page) to the value of the previos page, GrpArrayPage(Me.Page - 1), then adds one to that to get the next page number.

  GrpPages = GrpArrayPage(Me.Page)
  For i = Me.Page - ((GrpPages) - 1) To Me.Page
     GrpArrayPages(i) = GrpPages            
  Next I

GrpPages, a variable used to temporarily store the page number is then set to the value of the current page. Then the loop sets the value of GrpArrayPages to the value of GrpPages for all of the Array items.

To look at this a bit simpler; assume we are at page three of the group, the values would be:

GrpArrayPage( )	GrpArrayPages( )
	1		3	
	2		3	
	3		3

When you move to page four you get:

GrpArrayPage( )	GrpArrayPages( )	
	1		4	
	2		4
	3		4

...and so on.

If the code detects that the group name has changed " GrpNameCurrent <> GrpNamePrevious" then it resets the group page values to 1.

Arrays are often overlooked when developing code and people often create temporary or permanent tables to handle temporary data. Not only does this make your code more diffiuclt to manage and debug, it makes it less portable. Arrays can often substitute as a very efficient replacement for these temporary tables.

Complete Code Listing:

'************ Code Start *************
' This code was originally written by James H Brooks.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' James H Brooks
'
Option Compare Database
Option Explicit

Dim GrpArrayPage(), GrpArrayPages()
Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant
Dim GrpPage As Integer, GrpPages As Integer

Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
  If Me.Pages = 0 Then    
    ReDim Preserve GrpArrayPage(Me.Page + 1)
    ReDim Preserve GrpArrayPages(Me.Page + 1)    
    GrpNameCurrent = Me!Salesperson
    If GrpNameCurrent = GrpNamePrevious Then
        GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page - 1) + 1
        GrpPages = GrpArrayPage(Me.Page)
            For i = Me.Page - ((GrpPages) - 1) To Me.Page
                GrpArrayPages(i) = GrpPages            
            Next i    
    Else
        GrpPage = 1        
        GrpArrayPage(Me.Page) = GrpPage
        GrpArrayPages(Me.Page) = GrpPage    
    End If
  Else
    Me!ctlGrpPages = "Group Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page)
  End If
  GrpNamePrevious = GrpNameCurrent
End Sub
'************ Code End *************

*** Note: the line Me!Salesperson should be changed to the control name of the group you wish to track pages for. Me!ctlGrpPages is the name of a control you should place in the page footer. You can also change this line to whatever form you wish your page numbers to take.