ACC2000: How to Control the Number of Records Printed per Page

Article translations Article translations
Article ID: 209006 - View products that this article applies to.
This article was previously published under Q209006
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

Expand all | Collapse all

SUMMARY

This article describes a method that you can use to control the number of records printed per page in a report.

MORE INFORMATION

You can control the number of records printed per page in a report by setting the Visible property for page breaks. The following example demonstrates how to print three records per page in a report:
  1. Start Microsoft Access and open the sample database Northwind.mdb.
  2. Follow these steps to create a report called Report1 based on the Suppliers table:
    1. In the Database window, click Reports, and then click New.
    2. Select the Suppliers table, and then click OK.
    3. Add the following text boxes to the Detail section of the report, and place them on one line:
       Report: Report1
       --------------------------
       Caption: TestReport
       ControlSource: Suppliers
    
       Text Box:
           Name: Address
          ControlSource: Address
       Text Box:
          Name: City
          ControlSource: City
       Text Box:
          Name: Region
          ControlSource: Region
    					
  3. Add a text box with the following properties to the Detail section. Place the text box directly above the Address control. This control acts as a counter for the number of records in the report:
       Text Box:
       -----------------------
          Name: Counter
          ControlSource: =1
          Visible: No
          RunningSum: Over All
    					
  4. Open the toolbox if it is not already open, click Page Break, and then add a page break control to the lower-left corner of the detail section. Place it directly below the Address control, and set the Name property to PageBreak.
  5. In the Detail section, set the OnFormat property to the following Event Procedure:
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    
       If Me![Counter] Mod 3 = 0 Then Me![PageBreak].Visible = True _
       Else Me![PageBreak].Visible = False
    
    End Sub
    					
  6. Minimize the amount of white space by moving the Page Footer bar to directly below the PageBreak control.
  7. Preview the report.
    The first page of the report should contain the following three records:
    Address           City            Region
    ----------------------------------------
    49 Gilbert St.    London
    P.O. Box 78934    New Orleans     LA
    707 Oxford Rd.    Ann Arbor       MI
    					
If the last record is missing or is only partially displayed, the PageBreak control is not down far enough in the report's Detail section.

NOTE: You can modify this example to print any number of records per page by changing the code in the OnFormat property of the Detail section from "mod 3" to "mod x" where x is the number of records you want to print per page. For example, if you want to print 11 records per page, change the expression to read:

If Me![Counter] Mod 11 = 0 Then Me![PageBreak].Visible = True _
   Else Me![PageBreak].Visible = False
				

REFERENCES

For more information about the SetValue action, click Microsoft Access Help on the Help menu, type setvalue action in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the Visible property, click Microsoft Access Help on the Help menu, type visible property in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about the Page Break control, click Microsoft Access Help on the Help menu, type page break control properties in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Properties

Article ID: 209006 - Last Review: June 29, 2004 - Revision: 2.0
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbhowto kbusage KB209006

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com