Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Please help with my access report

EmeeDee

Programmer
May 21, 2025
4
Please how can i arrange the page numbers in an access report to rest for a new group.
I have a report with 78 pages and need to report 1 of 8 and the next set of number should be starting from 1 not continuing as 9 of 78
 
Last edited:
So, you're looking for something like this:
Group 1: Pages 1–8 | Labeled: 1–8
Group 2: Pages 9–16 | Labeled: 1–8
Group 3: Pages 17–24 | Labeled: 1–8

If so, let's call each collection of pages a SET and create these variables in your report's VBA (view code)
Private m_PageInSet As Integer
Private m_PagesInSet As Integer

Next, add a footer to the page of the report...

="Page " & m_PageInSet & " of " & m_PagesInSet

In the report page header, add this...


Code:
    Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer)
    Dim currentPage As Integer
    Dim setSize As Integer
    Dim fullPages As Integer
    Dim leftover As Integer
    Dim totalPages As Integer
    Dim setNumber As Integer

    setSize = 8  
    currentPage = Me.Page
    totalPages = Me.Pages

    ' Determine which set this page belongs to
    setNumber = Int((currentPage - 1) / setSize) + 1

    ' Figure out how many pages are in this set
    If setNumber = Int((totalPages - 1) / setSize) + 1 Then
        ' Last set - might be shorter
        m_PagesInSet = totalPages - ((setNumber - 1) * setSize)
    Else
        m_PagesInSet = setSize
    End If

    ' Determine page number within the set
    m_PageInSet = ((currentPage - 1) Mod setSize) + 1
End Sub
 
I have just become a fan of ChatGpt and posed your question there. Here is their answer. I have not tried this out but thought I would share it as it looks like less coding.

Here's how to reset page numbers by group:​


1. Group Your Report


Make sure your report is already grouped by the desired field:


  • Open your report in Design View.
  • Use the Group, Sort, and Total pane (bottom of the window) to add grouping if it’s not already present.

2. Set the Page Number Reset


  • Click on the Group Header section (e.g., GroupHeader0) in Design View.
  • With the header selected, look at the Property Sheet (press F4 if it's not visible).
  • Find the property called Force New Page and set it to Before Section (or After Section, depending on layout needs). This ensures each group starts on a new page.
  • Still in the Property Sheet, switch to the Event tab (or stay in Format tab depending on Access version).
  • Locate the Page Header section of your report.
  • Now go to the Group Header section again, and in its On Format event, add the following VBA code:


Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Me.Page = 1
End Sub

Replace GroupHeader0 with the actual name of your group header section (check the section bar for the correct name or look at the properties).

Notes:​


  • You need to make sure your Page Number control is located in the Page Header or Page Footer section.
  • This works best when each group starts on a new page, which avoids confusion with overlapping pagination.
 
Each answer so far assumes a completely different result/expectation from what I understand from your post. Please provide more direction with enough examples to assure we understand.
 
Good Day and Thanks a lot.

Listing all staff comes to 26 pages of report

The Report is for listing Committee members as separate groups within that report.

There are lets say 5 committees formed. We do not want to have to use the committee names to generate 5 separate reports

We created a group by committee for example

Thus we want to list committee members and the pages would restart as there is a new committee

so you have
Committee A

Page 1 of 5
2 of 5 etc
up to page 5 of 5

the net committee starts on a new page and so starts as
Committee B
Page 1 of 7,
2 of 7 etc up to
7 of 7

then the next starts
Committee C
page 1 of 4

and so on

So a committee
 
I have just become a fan of ChatGpt and posed your question there. Here is their answer. I have not tried this out but thought I would share it as it looks like less coding.

Here's how to reset page numbers by group:​


1. Group Your Report


Make sure your report is already grouped by the desired field:


  • Open your report in Design View.
  • Use the Group, Sort, and Total pane (bottom of the window) to add grouping if it’s not already present.

2. Set the Page Number Reset


  • Click on the Group Header section (e.g., GroupHeader0) in Design View.
  • With the header selected, look at the Property Sheet (press F4 if it's not visible).
  • Find the property called Force New Page and set it to Before Section (or After Section, depending on layout needs). This ensures each group starts on a new page.
  • Still in the Property Sheet, switch to the Event tab (or stay in Format tab depending on Access version).
  • Locate the Page Header section of your report.
  • Now go to the Group Header section again, and in its On Format event, add the following VBA code:


Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Me.Page = 1
End Sub



Notes:​


  • You need to make sure your Page Number control is located in the Page Header or Page Footer section.
  • This works best when each group starts on a new page, which avoids confusion with overlapping pagination.
Thanks a lot. I tried this but all the pages were simply reading Page 1
 
So, you're looking for something like this:
Group 1: Pages 1–8 | Labeled: 1–8
Group 2: Pages 9–16 | Labeled: 1–8
Group 3: Pages 17–24 | Labeled: 1–8

If so, let's call each collection of pages a SET and create these variables in your report's VBA (view code)
Private m_PageInSet As Integer
Private m_PagesInSet As Integer

Next, add a footer to the page of the report...

="Page " & m_PageInSet & " of " & m_PagesInSet

In the report page header, add this...


Code:
    Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer)
    Dim currentPage As Integer
    Dim setSize As Integer
    Dim fullPages As Integer
    Dim leftover As Integer
    Dim totalPages As Integer
    Dim setNumber As Integer

    setSize = 8 
    currentPage = Me.Page
    totalPages = Me.Pages

    ' Determine which set this page belongs to
    setNumber = Int((currentPage - 1) / setSize) + 1

    ' Figure out how many pages are in this set
    If setNumber = Int((totalPages - 1) / setSize) + 1 Then
        ' Last set - might be shorter
        m_PagesInSet = totalPages - ((setNumber - 1) * setSize)
    Else
        m_PagesInSet = setSize
    End If

    ' Determine page number within the set
    m_PageInSet = ((currentPage - 1) Mod setSize) + 1
End Sub
I am not sure i understand where these codes go into?
you said the header?
 
Since you expect to have a Page of Pages for each committee, you may need to use a solution like the Northwind 2 Developers template Catalog Report. There is a brief discussion on this page that gives a high level overview.
 

Part and Inventory Search

Sponsor

Back
Top