Solved

Page numbers based on grouping in a subreport

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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