Link to home
Start Free TrialLog in
Avatar of jaguar5554
jaguar5554Flag for United States of America

asked on

Reset page number and total page count for each group in MS Access report

Hello~ I am trying to apply the solution Microsoft offers to reset page numbers and total page count on a report (e.g. Page 2 of 4 of 63) (http://support.microsoft.com/kb/841779). Unfortunately, it returns "Invalid use of Me keyword":
Dim DB As Database
Dim GrpPages As RecordSet
Function GetGrpPages ()
'Find the group name.
GrpPages.Seek "=", Me![Country]
If Not GrpPages.NoMatch Then
GetGrpPages = GrpPages![Page Number]
End If
End Function

Any thoughts on how I can get around this problem? (or even an alternative solution to accomplish the special page numbering required for my report).

Much thanks in advance!
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Should be noted that these:

Dim GrpArrayPage(), GrpArrayPages()
Dim GrpNameCurrent As Variant
Dim GrpNamePrevious As Variant
Dim GrpPage As Integer
Dim GrpPages As Integer


 go at the top of the reports code module.   The rest of that code except for the first and last lines gets copy and pasted in to the Page footers OnFormat event.

  You also need a control on the report (can be hidden) that refers to the pages property so that two pass mode is added.  For example, set the controls control source to:

="Page " & [Page] & " of " & [Pages]

Jim.
One last thing:

ctlGrpPages is the name of the control in the footer that will display the page information.

I'm attaching a copy of the sample DB shown in the webinar so you can see this in action.

Jim.
TopAccessQuestions.zip
Avatar of jaguar5554

ASKER

I'm listening to the webinar now but your posts and sample DB are MOST appreciated. I will follow-up with my progress. Stay tuned....
Hello again~ (just got out of a meeting LOL). I am running Access 2003 -- is it possible to resend your copy of the DB in that format?
Hello Jim, I just applied the code you provided which produced exactly what I needed. I have two controls in the report footer:
1. Page of Pages (e.g. Report Page 1 of 63, Report Page 2 of 63, Report Page 3 of 63, etc.)
2. ctlGrpPages (e.g. SiteName 1 of 1, SiteName 1 of 2, SiteName 2 of 2, etc.)

It's perfect.
Thank you so much!
<<llo again~ (just got out of a meeting LOL). I am running Access 2003 -- is it possible to resend your copy of the DB in that format? >>

 Sorry to say it would take some time, which I don't have right now to do.  Is there something specific you wanted out of there?  

Jim.