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:
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.
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 SubThis 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.
ASKER
I added:
But, I'm getting the same problem.
Private Sub GroupHeader0_Retreat()
Stop
End SubBut, I'm getting the same problem.
<<in the sales person group footer.>>
You want the OnRetreat there.
Jim.
You want the OnRetreat there.
Jim.
ASKER
Still the same with this:
Private Sub GroupFooter1_Retreat()
Stop
End Sub
Hum, well not sure....any chance you can do up a small sample DB?
Jim.
Jim.
ASKER
attached
Page-Numbering-DB.accdb
Page-Numbering-DB.accdb
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
...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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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?
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):
But again, I will yield, so that you can try Jim's suggestions first.
JeffCoachman
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"But again, I will yield, so that you can try Jim's suggestions first.
JeffCoachman
<<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.
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.
ASKER
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.
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.
<<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.
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.
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.