This Access tutorial explains how to reset page number and reset total page number for each group in Access Report.
You may also want to read:
Access Report page break by Group and add blank page
Access Report Section Property
Access Report reset page number and reset total page number
When you have a Report that has different grouping, you may want to print out as separate reports with page number reset.
In the previous post, I have demonstrated how to print out separate reports for different grouping by adding a page break when grouping end page is odd number.
In this post, I will show how to reset page number based on the result of the last post.
Now lets quickly recap what has been done in the last post. In the last post, I page break a report by group (Department) using Force New Page property, and then use VBA to conditionally add a blank page if the grouping end page is odd number.
In the last example, Finance Department are in Page 1 and 2. Since the last page is even number, PageBreak49 is invisible (no page break).
For HR Department, since the last page is odd number, PageBreak49 becomes visible (add page break).
Reset page number to 1 for each group
Suppose we only need Page x instead of Page x of 8 (we will discuss this in the next part).
Switch to the Design View
In the Page Footer, change the Text box to =[Page] from [Page] of [Pages]
Click on Dept Header, in the Property, click on Event > On Format > Code Builder, then insert then below code to reset page for each group header
Page = 1
Then switch to Print Preview and you will find that page number is reset to 1 for each Department.
Reset total Pages for each group
In this section, I will use the method suggested in this article to reset total page number + insert blank page + remove blank page header
Now forget all about the above method an go back to the Design View of the last post.
1) In Page Footer, set the Visible property to No for the page Text Box. We need the Page and Pages value in order for the total pages Macro to work, but it does not have to be visible.
2) In Page Footer, create a Text Box called ctlGrpPages with no value, which is used to display the new [Page] of [Pages]
4) Select Page Footer, in the Property, select Event > On Format > Code Builder, copy and paste the below codes at Module level. Since Page Footer Section Procedure is rerun for every page, we need to reuse those variables.
Dim GrpArrayPages() ‘Array that stores new total page number of each page
Dim GrpNameCurrent As Variant ‘Group value of the page
Dim GrpNamePrevious As Variant ‘Group value of last page
Dim GrpPage As Integer ‘page number to be displayed in Text Box
Dim GrpPages As Integer ‘total page number to be displayed in Text Box
5) Paste the below codes in Page Footer Format Event
‘The first visit of Access to determine the total pages
If Me.Pages = 0 Then
ReDim Preserve GrpArrayPage(Me.Page + 1)
ReDim Preserve GrpArrayPages(Me.Page + 1)
GrpNameCurrent = Me!TextDept ‘change to your group text box name
‘If group is not changed
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
‘If group is changed
Else
Me.ReportHeader.Visible = True
GrpPage = 1
GrpArrayPage(Me.Page) = GrpPage
GrpArrayPages(Me.Page) = GrpPage
End If
‘The second visit of Access to assign Page and Pages
Else
Me!ctlGrpPages = “Page ” & GrpArrayPage(Me.Page) & ” of ” & GrpArrayPages(Me.Page)
End If
GrpNamePrevious = GrpNameCurrent
End Sub
Switch to Print Preview, now the Group Finance and HR has their own total page number.
What if the “Repeat Section” property of the Header is True? Doesn’t the page# get continually reset?
I tested, if the group header is set to repeat, the page doesnt reset
Where do you paste the code from step 5?
Click on the Page Footer then on the right panel, look for Format Event, click on the button