Reset Page Numbering
How to Restart Page Numbering in an Access Report
NEW! See my updated video that covers the same topic in more detail: Print Multiple Invoices. In the extended cut I also show how to reset page number across grouping levels so you can still have Page 1 of 3 in your groups!
Q: Is it possible to have page numbers restart at 1 in an
Access report, depending on some field? For example, if I have a report
with an employee section, with details about shifts worked. Each
employee section may take up several pages. Can I have the page
numbering start at one again every time the employee changes? > Greg B.
A: Yes, it's possible, but
it's not easy to do. You think Microsoft would have made a feature like
this a little more accessible. You need group levels and a little VBA
source code to do it.
First, you'll need to group all of the same employee's records together
using a Sorting &
Grouping Level. Turn on the Group Header if you don't already see
it. You don't need a Group Footer. You don't actually need to put any
data in the Group level, but if you want to put the employee's name and
info in there, that's fine too.
Now, add your page number
textbox to the bottom of the page in the Page Footer section. I cover
this in Access 104. You
just need an unbound textbox with =Page in the Control Source.
Now, right-click on the FirstName Header section and select Build Event
(code builder).
In the VBA section, enter: Me.Page = 1
Also, make sure you set the "ForceNewPage" property for your Group
Header to Before Group.
Now here's what happens... the pages will number themselves normally,
but when a FirstName group header hits, the VBA code runs and sets the
page numbering to 1. Then the next group section forces a new page,
numbers accordingly, until the next section hits and resets the page
number.
Again, it works... it's not easy to do... and I think Microsoft should
have put a "reset page numbering" option in the properties for grouping
levels, but that would make things too easy, and you wouldn't need to
keep me around anymore.
:) LoL
Hope this helps.
By Richard Rost
Click here to sign up for more FREE tips
|