Page number reset for each record in group (1 Viewer)

Mr. SLP

Registered User.
Local time
Today, 14:22
Joined
Dec 23, 2017
Messages
56
I have a report that is grouped by student (page break setup so every student has its own page) and then all the equipment that is assigned to them. There’s a few students that this list spans more than 1 page. I would like to have the pages numbered so I can easily see in a print out that I have all the pages for a specific student. So, if everything fits on 1 page “page 1 of 1” on button but if it span pages “page 1 of #”. Is this possible? How would I do this?


Sent from my iPhone using Tapatalk
 

isladogs

MVP / VIP
Local time
Today, 19:22
Joined
Jan 14, 2017
Messages
18,186
Many years ago I used this code in various reports to do what you want
Hopefully it will be self explanatory as I haven't touched the code for over 10 years. If you need to ask questions, I'll study it carefully!

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

Private Sub PageHeaderSection_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.[Activity Code]  '<== replace 'Activity Code' with an appropriate control in the Page Header section of your own report. 
    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

Looking at it now, I can't remember why the code is in the PageHeaderSection_Format event.
However, I do know I used it successfully on many reports & always in that event
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:22
Joined
May 7, 2009
Messages
19,169
If you can't remember, read the book again where you get the code.
 

isladogs

MVP / VIP
Local time
Today, 19:22
Joined
Jan 14, 2017
Messages
18,186
Thanks for that useful advice arnel!!!! :D

When I used this code back in 2005, I will indeed have got it from somewhere rather than written it myself.
It was probably example code from the MS website.

Anyway, 12 years on, I don't need to look up how it works ....

2 points to add:
a) Its in the PageHeaderSection_Format event as that's where the referenced control is
b) GrpNameCurrent = Me.[Activity Code] '<== replace Activity Code with an appropriate control on your own report. I'm using a PK field
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:22
Joined
May 7, 2009
Messages
19,169
you can Extend what Riddington
has shared you. So that the
code can be re-used on other reports
that you need to set same functionality.
Create a Class and instantiate its object.

Copy and pasted to New Class Module.
Named it clsPageGroupNewPage.
Code:
Option Compare Database
Option Explicit

Dim grpArrayPage() As Integer, grpArrayPages() As Integer
Dim grpNameCurrent As Variant, grpNamePrevious As Variant
Dim grpPage As Integer, grpPages As Integer

Public Function ReportPageOnGroup(Page As Integer, Pages As Integer, varGroupValue As Variant)
    Dim i As Integer
    If Pages = 0 Then
        ReDim Preserve grpArrayPage(Page + 1)
        ReDim Preserve grpArrayPages(Page + 1)

        grpNameCurrent = varGroupValue

        If grpNameCurrent = grpNamePrevious Then
            grpArrayPage(Page) = grpArrayPage(Page - 1) + 1
            grpPages = grpArrayPage(Page)
            For i = Page - (grpPages - 1) To Page
                grpArrayPages(i) = grpPages
            Next

        Else
            grpPage = 1
            grpArrayPage(Page) = grpPage
            grpArrayPages(Page) = grpPage

        End If
    Else
        ReportPageOnGroup = "Page " & grpArrayPage(Page) & " Of " & grpArrayPages(Page)

    End If
    grpNamePrevious = grpNameCurrent

End Function

to call it in ANY Report:

Code:
Dim clsRpt As clsPageGroupNewPage

Private Sub Report_Open(Cancel As Integer)
	Set clsRpt = New clsPageGroupNewPage
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
	' Me.txtBoxPage is the textbox you want to set the New Page Numbering
	' theGroupingFieldNameHere refers to the grouping field you have.
	Me.txtBoxPage = clsRpt.ReportPageOnGroup(Me.[Page], Me.[Pages], Me.[theGroupingFieldNameHere])
End Sub

Private Sub Report_Close()
    Set clsRpt = Nothing
End Sub
 

isladogs

MVP / VIP
Local time
Today, 19:22
Joined
Jan 14, 2017
Messages
18,186
Hey Puzon.
I didn't know we were sharing last names with the rest of the forum ... :)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:22
Joined
May 7, 2009
Messages
19,169
Merry Christmas to you and your family.
 

isladogs

MVP / VIP
Local time
Today, 19:22
Joined
Jan 14, 2017
Messages
18,186
And to you 'somewhere out there' wherever that is.
 

DJ Ice

New member
Local time
Today, 15:22
Joined
Mar 25, 2020
Messages
1
I know this is old, but it's the first answer that pops up in a google search and I just wanted to add this to save others hours of troubleshooting which I can never get back.

You NEED to have regular page numbering on the report. You can hide it, make it not visible or whatever you want to do, but you need to have it there.

The code above assumes that the report is making multiple passes through the data as that is required in order for the report to know the last page number. However, Access is now "smart" enough to look at the items on your report, and if a second pass is not required, it won't do it which leaves your textbox blank. By adding the default page numbers, that forces Access to do a second pass which in turn gives the code above the required information to display properly.
 

jmoates72

New member
Local time
Today, 12:22
Joined
Jun 16, 2021
Messages
1
DJ, great find!!

I spent two hours troubleshooting, and was about to give up, when I found your post. So obvious, yet so not. Surprised no one ever metioned this.

I'm slow, but I can learn. ;)
 

Users who are viewing this thread

Top Bottom