Access Report page break by Group and add blank page

This Access tutorial explains how to add page break by Group in Access Report and add blank page if last page is odd number.

You may also want to read:

Access Report reset total page number

Access Report page break by Group

When we create Access Report which has different grouping, we may want to start a new page for each group.

Assume that we have the following Report grouped by Department and Section. How do we start a new page for each Department?

access_report_page_break

First, go to the Design View of the Report. As you can see at the bottom, it is grouped by Department and then grouped by Section.

access_report_page_break_02

Click on Dept Header, in the Property page, set the Force New Page property to Before Section

access_report_page_break_03

Alternative, click on the Dept Footer, in the Property page, set the Force New Page property to After Section. Do not use After Section in Access 2010 because it is bugged to give blank pages, use Before Section instead.

Now go to Print Preview and you will see Access starts a new page for each Department.

access_report_page_break_04

access_report_page_break_05

Access Report add blank page if last page is odd number

If we are going to distribute each report to each department, we cannot have page 1 for department A and page 2 for department B, instead you can print single side.

To print double side, if the end page of department A is odd page, we need to insert a blank page, so that we have department A in page 1, blank page 2, department B in page 3.

Now go to Design View, under Dept Footer, insert a Page Break Control, say PageBreak49.

access_report_page_break_06

Next, click on Depart Footer, in the Property, select Event > On Format > Code Builder

access_report_page_break_07

Insert the following code

If Me.Page Mod 2 = 0 Then
    Me!PageBreak49.Visible = False
Else
    Me!PageBreak49.Visible = True
End If

Go to Print Preview. Now 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

In page four, the inserted blank page contains no data but it leaves Page Header there. To remove the Page Header in blank page, use the following code for Group Footer.

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
    If Me.Page Mod 2 = 0 Then
        Me!PageBreak49.Visible = False
        Me.Section(3).Visible = True
    Else
        Me!PageBreak49.Visible = True
        Me.Section(3).Visible = False
    End If
End Sub

Report.Section(3) is an Object that represents Page Header section, you can find the numeric value for other Objects in the below table

Setting Constant Description
0 acDetail Report detail section
1 acHeader Report header section
2 acFooter Report footer section
3 acPageHeader Report page header section
4 acPageFooter Report page footer section
5 acGroupLevel1Header Group-level 1 header section
6 acGroupLevel1Footer Group-level 1 footer section
7 acGroupLevel2Header Group-level 2 header section
8 acGroupLevel2Footer Group-level 2 footer section

Now that you know how to insert blank page for each group if the last page is odd number, unfortunately the page number is not reset for each group.

Read the below article if you want to rest page number and total page number for each group.

Access Report reset total page number

Outbound References

https://support.office.com/en-us/article/Insert-a-page-break-control-in-an-Access-report-86D2EA7D-7AA6-44F2-8507-23134A9A2E15

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also 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 in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

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