group page # of group pages in report

[supmktg]

Help please!

I'm trying to set group page # of group pages for each new group in the page footer of a report.
The report is grouped by Billing_Cost_Center. I have an unbound text box in the page footer named ctlGrpPages, and I have another text box in the page footer with a control source of ="Page " & [Page] & " of " & [Pages] and it's visible property = no.

I've found a few code samples in posts, but I can't get any of them to work. Here is the code I am trying:

Option Compare Database
Option Explicit

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

Private Sub PageFooter_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!Billing_Cost_Center
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 = "Billing_Cost_Center" & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page)
End If
GrpNamePrevious = GrpNameCurrent
End Sub


I've also downloaded a sample file with report that works fine but when I try to recreate the report using the same data and code I also can't get it to work. Can anyone see what I'm doing wrong?

 

[supmktg]

For those of us struggling with this, (and I've found a bunch of unresolved posts on this subject), I got the code to work--- well sort of?

In the page footer properties window, I edited the Name from "PageFooter_Section" to "PageFooter" and now "Page x of y" appears. Here's my new problem:

The first group of page #'s is correct, and the following groups if they are "Page 1 of 1" are also correct. But if the report changes from a group of 1 pages to a group of multiple pages, then it adds the next group of 1 pages to the group--- What should display as Page 1 of 2 and page 2 of 2 and then page 1 of 1, is displaying as Page 1 of 3 and Page 2 of 3 and Page 3 of 3.

Can anyone suggest a remedy for this?

Thx

 

[Rich]

Post a copy here in 97 ver, I'll take a look for you

 

[supmktg]

Got It !!!

Rich,

Thanks for the offer to review it! Actually, I think it was your group pages sample file from a different post that pointed me in the right direction in the first place and for that I am truly grateful.

As it turns out, I just figured it out and I'd like to share so others might get the benefit. I added the code:

If GrpNameCurrent <> GrpNamePrevious Then
GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page)
GrpPages = GrpArrayPage(Me.Page)
For I = Me.Page - ((GrpPages) - 1) To Me.Page
GrpArrayPages(I) = GrpPages
Next I

'which resets the page count if the group name changes

The new code looks like this:

Private Sub PageFooter_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.BILLING_COST_CENTER

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
If GrpNameCurrent <> GrpNamePrevious Then
GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page)
GrpPages = GrpArrayPage(Me.Page)
For I = Me.Page - ((GrpPages) - 1) To Me.Page
GrpArrayPages(I) = GrpPages
Next I

End If
Else

GrpPage = 1
GrpArrayPage(Me.Page) = GrpPage
GrpArrayPages(Me.Page) = GrpPage
End If
Else
Me.ctlgrpPages = "Job# " & Me.BILLING_COST_CENTER & " " & "Page " & " " & GrpArrayPage(Me.Page) & " of " & " " & GrpArrayPages(Me.Page)
End If
GrpNamePrevious = GrpNameCurrent

End Sub

Thx to everyone who posted Q's & A's on the subject

 

[mega1]

Help

Hi, was wondering if you could help shed more light for me please. I am having the same issue. I am trying to do the same exact thing you are doing here of doing page counts per group (in my case it's vendor id) and when the vendor id changes, I'd like to reset my page counting.

However, I am not a crystal person. This is what I've done so far:
1. Created a group header and footer for vendor id

Where did you put your code? I'd like my "Page X of Y" to go on the page header.

Please help!

Thanks!

 

[Rich]

There's an example here

 

[mega1]

Thanks, but I cannot open that.