• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6196
  • Last Modified:

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!
0
jaguar5554
Asked:
jaguar5554
  • 4
  • 3
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I presented the code below in my last webinar "Your Access questions answered":

http://www.youtube.com/watch?v=fww9P8MXCR4&list=PL19621FF07B4976BB&index=1

  I think you'll find it a bit simpler then the code used in the MSKB article (although that code does work).

 This line:

GrpNameCurrent = Me!ID


   is where you need to change the control name.   "ME" is a shorthand reference to the current object (form or report).  It will not work in a standard module.  This code needs to be in the report.

Jim.

Option Compare Database
Option Explicit

'************ Code Start *************
' This code was originally written by James H Brooks.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' James H Brooks
'

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


Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)

  Dim i As Integer

  If Me.Pages = 0 Then
    ReDim Preserve GrpArrayPage(Me.Page + 1)
    ReDim Preserve GrpArrayPages(Me.Page + 1)
    GrpNameCurrent = Me!ID
   
    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
    Else
      GrpPage = 1
      GrpArrayPage(Me.Page) = GrpPage
      GrpArrayPages(Me.Page) = GrpPage
    End If
  Else
    Me!ctlGrpPages = "Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page)
  End If
 
  GrpNamePrevious = GrpNameCurrent

End Sub
1
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
1
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
1
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
jaguar5554Business AnalystAuthor Commented:
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....
0
 
jaguar5554Business AnalystAuthor Commented:
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?
0
 
jaguar5554Business AnalystAuthor Commented:
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!
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now