Access Report reset total page number and insert blank page

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 GrpArrayPage()  ‘Array that stores new page number of each page
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

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    ‘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
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
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.





Click here to download the examples

4 thoughts on “Access Report reset total page number and insert blank page

  1. What if the “Repeat Section” property of the Header is True? Doesn’t the page# get continually reset?

Leave a Reply

Your email address will not be published.