<

MS Access – Adding “Page ‘x’ of ‘y’” Over a Group in a Report

Posted on
9,757 Points
1,557 Views
2 Endorsements
Last Modified:
Published
Experience Level: Beginner
5:14
Jim Dettman (EE MVE)
Independent consultant specializing in the writing of custom packages for businesses.
Learn how to number pages in an Access report over each group.

Video Steps

1. Activate two pass printing by referencing the pages property

2. Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to determine what pass your on.

3. Use an unbound control to display the page count

2
1 Comment
LVL 69

Author Comment

by:Jim Dettman (EE MVE)
Just noticed that I never actually gave the code in the tutorial:

Option Compare Database
Option Explicit

'************ 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
'

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


Private Sub PageFooterSection_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!ID
    
    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 = "Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page)
  End If
  
  GrpNamePrevious = GrpNameCurrent

End Sub

Open in new window

0
This article series will show you how to utilise the Next Generation Cryptography (CNG) API from Microsoft for modern hashing and encrypting/decrypting in VBA. In this part: Handling passwords.
If you want to save changes made programmatically to a reports Printer property do not use this syntax: docmd.close acReport, Reportname, acSaveYes

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month