Solved

Page numbers based on grouping in a subreport

Posted on 2014-02-04
4
332 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook Free & Paid Tools
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

813 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

10 Experts available now in Live!

Get 1:1 Help Now