Solved

Page numbers based on grouping in a subreport

Posted on 2014-02-04
4
342 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

726 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