Excel-Access.tips: 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 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

Assume that 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

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.