ACC: How to Control the Number of Records Printed Per Page

Last reviewed: May 14, 1997
Article ID: Q119075
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

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

MORE INFORMATION

You can control the number of records printed per page on a report by setting the page break's Visible property. The following example demonstrates how to print three records per page on a report:

  1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x or 2.0).

  2. Create a new macro with the following action, and then save the macro as Detail Page Break:

          Macro Action
          -------------------------------------------------------
          SetValue
    
             Item: [PageBreak].Visible
             Expression: IIf([TheCounter] Mod 3 = 0, "True", "False")
    
    

  3. Follow these steps to create a report called Test1 based on the Suppliers table:

    a. In the Database window, click the Report tab, and then click New.

    b. Select the Suppliers table and click OK.

    NOTE: In versions 1.x and 2.0, select the Suppliers table, and then click Blank Report.

    c. Add the following text boxes to the detail section of the report:

             Report: Test1
             --------------------------
             Caption: TestReport
             ControlSource: Suppliers
    
             Text Box:
                Name: Address
                ControlSource: Address
             Text Box:
                Name: City
                ControlSource: City
             Text Box:
                Name: Region
                ControlSource: Region
    
       NOTE: In Microsoft Access version 1.x, the Name property is called the
       ControlName property.
    
    

  4. Add a text box with the following properties to the detail section. Place the text box directly on top of the Address control. This control will act as a counter for the number of records in the report:

          Text Box
    
             Name: TheCounter
             ControlSource: =1
             Visible: No
             RunningSum: Over All
    
    

  5. Set the detail section's OnFormat property to the Detail Page Break macro.

  6. Add a page break control to the bottom left corner of the detail section beneath the Address control.

  7. Set the page break control's Name property to PageBreak.

  8. 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.

You can modify this example to print any number of records per page by changing the expression in the Detail Page Break macro 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:

   IIf([TheCounter] mod 11 = 0, "True", "False")

REFERENCES

For more information about SetValue, search for "SetValue Action," and then "SetValue Action" using the Microsoft Access 97 Help Index.

For more information about the Visible property, search for "Visible property," and then "Visible Property (Microsoft Access)" using the Microsoft Access 97 Help Index.

For more information about the Page Break control, search for "page breaks," and then "Add a page break to a report" using the Microsoft Access 97 Help Index.


Additional query words: page break
Keywords : kbusage RptsLyt
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : X86
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 14, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.