Solved

Page numbers based on grouping in a subreport

Posted on 2014-02-04
4
325 Views
Last Modified: 2014-02-19
Can someone please tell me how to insert page numbers in a subreport based on the group header of the report?  I want the page numbers to start on a new group header within the subreport.

Thanks
0
Comment
Question by:Kdankwah
  • 3
4 Comments
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39833571
Use this link ....


http://office.microsoft.com/en-us/access-help/reset-page-numbers-for-each-group-in-a-report-HP005187401.aspx

Reset page numbers for each group in a report

Show AllShow All
Before you do the following procedure, create a macro that uses the SetValue action to change the Page property to 1.

Do one of the following:
If the page number is located in the page header, use the following values for the SetValue action arguments:

Item: [Page] and Expression: 0

If the page number is located in the page footer, use the following values for the SetValue action arguments:

Item: [Page] and Expression: 1

Save and close the macro.
Open the report in Design view.
Add a text box to show page numbers in the page header or footer.
ShowHow?

Do one of the following:
If the page number appears in the page header, set the ForceNewPage property of the first-level group header to Before Section and the OnFormat property of the first-level group footer to the name of the macro you created in step 1.

If the page number appears in the page footer, set the OnFormat property of the first-level group header to the name of the macro you created in step 1 and the ForceNewPage property of the first-level group footer to After Section.

 NOTE   The Employee Sales by Country report in the Northwind sample database uses an event procedure in the OnFormat property to reset the page number for each country/region. To view this report, open the Northwind database in the Samples subfolder of your Microsoft Office folder, and then open the Employee Sales by Country report in Design view.



ET
0
 

Author Comment

by:Kdankwah
ID: 39833672
Thanks, I saw this during my research, its for Access 2003.  I am using 2010.  I dont know if it will work for 2010.  I tried it and it was giving me some error messages.


Thanks
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39834019
What error or you getting?  The procedure above is pretty generic and should work with 2010.

ET
0
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 500 total points
ID: 39834569
Also, here is another way I accomplished what you are asking for a client a few years ago.   They have a a Customer Collections Letter (Main Report) that includes the Customer Statement (Sub-Report).  The customer statement can be multiple pages once the collections letter prints and they wanted to number the statement.  Both reports group on Customer Number and the statement sub-report is inserted in the Customer Number Group Footer Section.  

1.) In the sub-report customer group header I placed a Label and Text control ...

Page: TxtPageNum

2.)  In the sub report customer group header OnPrint Property I used the following code:

Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)
    Static PrevCustNum As String
    Static PageNumStmt As Long
   
   If Nz(PrevCustNum, "") = "" Or PrevCustNum <> Me.CUSTNMBR Then
       PageNumStmt = 1
   End If
   If PrevCustNum = Me.CUSTNMBR Then
       PageNumStmt = PageNumStmt + 1
   End If
   
   Me.TxtPageNum = PageNumStmt
   PrevCustNum = Me.CUSTNMBR
End Sub


This setup also includes the [Page] & " of " & [Pages] in the Page Footer of the main report that will number the overall pages on the report including the collections letter.

Also, I use a Recordset processing loop that prints a separate report for each customer.


ET
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now