group page numbers

[natrluvr]

I have a report that has a group. Going through the threads I have figured out how to page number my groups. What I am looking for now is if I can do a total page number of the report but have it only count the pages that start a new group. The below example would be for a five page report:

page 1 (of report)
page 1 of 2 (for group)
page 2 of 2 (for group)
page 2 (of report)
page 1 of 2 (for group)
page 2 of 2 (for group)
page 3 (of report)
page 1 of 1 (for group)

 

[THWatson]

natrluvr
Take a look at this link from the Microsoft Knowledge Base and see if this helps.

http://support.microsoft.com/?kbid=209215

Tom

 

[natrluvr]

Thanks Tom for the information. Problem is I don't want the new group to reset to one. I want it to continue with the page count. I would like evertime the new group starts a page is continues to count. The reason for this is that often times the group may add or remove pages throughout the year and we don't want it to interfere with the page numbering of the report.

If you can think of anything else I would appreciate. I will work with what you sent though and see if I can get it to work the way I need.

Sarah

 

[THWatson]

Sarah
I see what you are wanting to do. There has to be a way.

I'll continue to see if I can figure it out.

Tom

 

[THWatson]

Sarah
Here's some code modified from the Northwind database.

Option Compare Database
Option Explicit

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

Private Sub PageHeaderSection_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.CategoryID
    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) & " for " & Me.CategoryName
  End If
  GrpNamePrevious = GrpNameCurrent

End Sub

In your page footer put two controls.
1. The first would be an unbound control
ctlGrpPages

2. The second would have the expression...
= "Page " & [Page] & " of " & [Pages] & " in report"

You will have to change the GrpNameCurrent = Me.CategoryID line in the code, from CategoryID to whatever your Group is.

You will also have to change the GrpArrayPages(Me.Page) & " for " & Me.CategoryName line in the code, from CategoryName to your Group name.

You can also modify the text in the second control in the page footer to suit your needs.

Hope this helps.

Tom

 

[natrluvr]

It's getting really close.

It still isn't numbering right for the total report. It's numbering every page. I am hoping that if a group spans more than one page than it will only number the first page in succession with the report and not the rest of the group, than continue the succession when the next group begins. So for example if I had a report that had three data items to the group it would page number like this:

(page 1)

Page 1 of 2 for dogs. Page 1 for report.

(page 2)

Page 2 of 2 for dogs.

(page 3)

Page 1 of 2 for cats. Page 2 for report.

(page 4)

Page 2 of 2 for cats.

(page 5)

Page 1 of 2 for pigs. Page 3 for report.

(page 6)

Page 2 of 2 for pigs.

**That way if someone adds data that extends the group for cats to three pages, the total pages of the report will not change. Someone can easily add the third page of cats to a printed out report without affecting a table of contents or a page number system.

Thanks for all your help in this.

Sarah

 

[THWatson]

Sarah
What you are wanting to do is eliminate some pages from the counting. But...if a table of contents shows 35 pages and you add 2 pages (1 to cats and 1 to pigs) there are still 37 pages. Wouldn't that, over time, lead to a distorted picture?

In any event, I don't know of a way do actually what you are referring to. It might be possible...I just don't know how to "count pages" but "not count pages."

So I think you should seek a second opinion. How be you make a new post, showing the code that I gave you, but also asking if it is possible to modify that code to do what you want?

Good luck. I will be interested in seeing responses to your new post.

Tom

 

[natrluvr]

Thank you so much for all your help. I took your comments into a meeting and we all came to the consensus that your right. It will still be confusing the route we thought we wanted to go, so we are exploring some other options.

Thanks again for attempting. The code you did provide was an asset that we intend to use so our group page numbering works.

Sarah

 

[THWatson]

Sarah
Good luck with your project.

Tom