Distinct Page Numbering for Grouping Level where there is a Subreport in the Group Footer

I have a series of reports that all are based on the same data, but provide different perspectives on the data (sales by sales person).

I am using some code found here at EE to give each sales person (the reports have the first grouping level set on the sales person) a new page numbering sequence (sales person 1 has 1 of 3 pages, sales person 2 has 1 of 2 pages, etc.) instead of one numbering sequence for the entire report.

This is the code being used on the format section event (includes some PageSectionHeader code that is not related to the page numbering:
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 RepCurrent As Variant
Dim RepPrevious As Variant
Dim GrpPage As Integer
Dim GrpPages As Integer


Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
Me.PageHeaderSection.Visible = False
End Sub

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Me.PageHeaderSection.Visible = True
End Sub

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)
    RepCurrent = Me!Rep
    
    If RepCurrent = RepPrevious 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
  
  RepPrevious = RepCurrent

End Sub

Open in new window


This works great for all but one of the reports. That one is different from the other in that it has a subreport in the sales person group footer.

Apparently this is hosing the page numbering code. For example if a sales person has one page for his report, then his page 1 numbering shows as 1 of 2  and the first page of the next sales person's report shows as 2 of 2.
haidentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< That one is different from the other in that it has a subreport in the sales person group footer.>>

 Try adding the code to the On Retreat event as well.

What's happening is that the subreport is forcing a page break, then the report engine is reformatting and backing up a page because of settings you have in place (keep together, etc).

 Place a STOP in the OnRetreat event to check this (when you execute the report, you'll hit the stop if it backs up).

Jim.
0
haidentAuthor Commented:
I added:

Private Sub GroupHeader0_Retreat()
Stop
End Sub

Open in new window


But, I'm getting the same problem.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<in the sales person group footer.>>

 You want the OnRetreat there.

Jim.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

haidentAuthor Commented:
Still the same with this:

Private Sub GroupFooter1_Retreat()
Stop
End Sub

Open in new window

0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Hum, well not sure....any chance you can do up a small sample DB?

Jim.
0
haidentAuthor Commented:
0
Jeffrey CoachmanMIS LiasonCommented:
First, ...please set the Default view of the report to "Print Preview"
...Report View will not accurately display the page numbering.

I am sure Jim can zero in on the root issue, ..

From my experience, ...in some cases, it is easier to do things like this if you put the custom numbering in the group footer.
(Instead of the page footer)
So it may not show up at the exact bottom of the page, ...but instead, at the bottom of the Group.

Finally, note that if you need this level of detail,...then all of this would be done automatically if you "filtered" for each salesperson individually. (loop each employee in the employee table and open/print their unique report.)
This should produce the same output, and no code is required...
Simple sample attached...

JeffCoachman
Database74.mdb
0
haidentAuthor Commented:
It is using Print Preview as the view.

The requirement is that a specific sales person, or subset of all, or all can be selected and the report(s) generated; so looping through them doesn't really fit.

This is all built and works fine and the page numbering was great until the subreport was thrown into the mix.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
OK here's the changes:

1. Set the page header not visible by default.
2. Change the Rep Group Header's ForceNewPage to none.
3. Change the Rep Group Footer's ForceNewPage to After section.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eric ShermanAccountant/DeveloperCommented:
This is all built and works fine and the page numbering was great until the subreport was thrown into the mix.

This is why JeffCoachman posted his comment for which I total agree with.  That's the only way I've coded reports to accomplish what you are trying to do.  The approach you are using worked UNTIL you added the subreport.  If you come up with a work around what will happen when something else is thrown into the mix???

All you have to do is build a recordset or temp table for the sales persons selected then loop through that producing a separate report for each one and it will work every time.  Even if you need a ending report you can produce a summary report where salesperson_id In ("Select salesperson_id from tblTemp").

ET
0
Jeffrey CoachmanMIS LiasonCommented:
Try Jim's suggestions first and see if that fixes your issue as-is.

What etsherman and I are trying to say is that you have a lot of code there that is not working the way you need it to, so why not simplify things, ...and get the same results?

The requirement is that a specific sales person, or subset of all, or all can be selected and the report(s) generated; so looping through them doesn't really fit.
It can be made to...
You can use a multiselect listbox to select any "subset" of salespeople you like.
In this case you would loop all the listbox selections, something like this worked fine for me (for the same report in my sample):
Dim vItem As Variant
Dim empID As Long

For Each vItem In Me.List1.ItemsSelected
    DoCmd.OpenReport "rptEmpSales", acViewNormal, , "EmpID=" & Me.List1.ItemData(vItem), acHidden
    DoCmd.Close acReport, "rptEmpSales"
Next vItem
MsgBox "Done"

Open in new window


But again, I will yield, so that you can try Jim's suggestions first.

JeffCoachman
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<All you have to do is build a recordset or temp table for the sales persons selected then loop through that producing a separate report for each one and it will work every time.  Even if you need a ending report you can produce a summary report where salesperson_id In ("Select salesperson_id from tblTemp").>>

  While that's true, it also means significantly more overhead.   Depending on the number of reports you need, it may be smarter to get it done in one report, especially if you plan to e-mail the report afterwards.   It's one thing to e-mail one report with all people, and then another to send an e-mail with hundreds of attachments.  Just depends on what your goals are and what your working with.

  It wasn't the sub report either that was the problem, but the fact that he was hiding/un-hiding the page headings on the fly and that the Group Header had a force new page setting of Before Section.   In regards to the group, I've never had good results using that setup.   Instead, I always do nothing on the header and a force new page after section on the footer.

 Anyway with those minor changes, the report works perfectly fine.   The page numbering code is actually fairly robust and works well for the most part.

Jim.
0
haidentAuthor Commented:
Works - happy camper here, but wondering how does the page header show when it's visible property is set as No?

I appreciate the suggestions from the other experts, and they have given me a concept that I may well use in future projects - it's just good to not have to majorly rework this when it's almost ready to deliver.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<happy camper here, but wondering how does the page header show when it's visible property is set as No?>>

The visible property is being flipped on and off in your code.   If it was left visible by default, you'd get it on the first page along with your first group header.

Since you already included the column headings in as part of the group header, you don't want the page header showing up.

After it hits the header, then get flipped on, and then not off until the group footer (the next page which is of course the next group header, so no page headings wanted).

Jim.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.