Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-05-15
7
Medium Priority
?
5,373 Views
Last Modified: 2013-05-15
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
Comment
Question by:jaguar5554
  • 4
  • 3
7 Comments
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 39168375
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
 
LVL 58
ID: 39168390
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
 
LVL 58
ID: 39168399
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jaguar5554
ID: 39168419
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
 

Author Comment

by:jaguar5554
ID: 39169038
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
 

Author Comment

by:jaguar5554
ID: 39169239
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
 
LVL 58
ID: 39169331
<<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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question