Solved

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

Posted on 2013-05-15
7
4,797 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 58

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
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
Technology Partners: 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

631 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