Solved

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

Posted on 2013-05-15
7
4,055 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 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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
0
 
LVL 57
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.
0
 
LVL 57
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
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 57
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now