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).

access_report_page_break_08

 

access_report_page_break_09

For HR Department, since the last page is odd number, PageBreak49 becomes visible (add page break).

access_report_page_break_10

access_report_page_break_11

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

access_report_page_break_14

In the Page Footer, change the Text box to  =[Page] from [Page] of [Pages]

access_report_page_break_12

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.

access_report_reset_page_number

access_report_reset_page_number_02

access_report_reset_page_number_03

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.

access_report_page_break_14

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]

access_report_reset_page_number_08

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.

access_report_reset_page_number_04

access_report_reset_page_number_05

access_report_reset_page_number_06

access_report_reset_page_number_07

Click here to download the examples

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *