ACC: How to Repeat Report Records a Specified Number of Times

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

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

Certain types of reports require printing each record a specific number of times. These reports include labels that are used for picking, shipping and invoicing, or tear-off forms that are meant for multiple recipients.

MORE INFORMATION

The following example demonstrates how to use a text box on an unbound form to specify the number of times each record should be printed in the report.

Create the Report and the Code Behind the Report

  1. Start Microsoft Access and open the sample database Northwind.mdb (or
     NWIND.MDB if you are using Microsoft Access version 2.0).

  2. Click the Reports tab and then click New.

  3. In the New Report dialog box, click AutoReport: Columnar and base the
     report on the Shippers table. Then click OK.

     If you are using Microsoft Access version 2.0, click Shippers in the
     Select A Table/Query drop-down list in the New Report dialog box; then
     click the Report Wizards button. In the Report Wizards dialog box,
     click AutoReport, and then click OK.

  4. Save the report as rptRepeatRecs.

  5. Open the rptRepeatRecs report in Design view.

  6. On the View menu, click Code.

  7. Under Option Compare Database, type the following statements:

        Option Explicit
        Dim intPrintCounter As Integer
        Dim intNumberRepeats As Integer

     Note that if you are using Microsoft Access version 7.0 or later,
     Option Explicit may already exist in the Declarations section.

  8. Close the module.

  9. On the Edit menu, click Select Report.

 10. On the View menu, click Properties.

 11. Click the OnOpen property box and then click the Build button (...) to
     the right.

 12. In the Choose Builder dialog box, click Code Builder, and then click
     OK.

 13. Type the following code:

        Private Sub Report_Open(Cancel As Integer)
           intPrintCounter = 1
           intNumberRepeats = Forms!PrintForm!TimesToRepeatRecord
        End Sub

 14. Close the module.

 15. Click the horizontal Detail bar. If the Detail section's property
     sheet is not already visible, click Properties on the View menu.

 16. Click the OnPrint property box, and then click the Build button (...)
     to the right.

 17. In the Choose Builder dialog box, click Code Builder, and then click
     OK.

 18. Type the following code:

        Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
           ' Note: intNumberRepeats and intPrintCounter are initialized
           ' in the report's OnOpen event.
           If intPrintCounter < intNumberRepeats Then
              intPrintCounter = intPrintCounter + 1
              ' Do not advance to the next record.
              Me.NextRecord = False
           Else
              ' Reset intPrintCounter and advance to next record.
              intPrintCounter = 1
              Me.NextRecord = True
           End If
        End Sub

     Note that this procedure may be called Detail1_Print in Microsoft
     Access version 2.0.

 19. Save and close the report.

Create a Form That Specifies How Many Times to Repeat the Records

  1. In the Database window, click the Forms tab, and then click New.
     Create a new, blank form in Design View that is not based on any table
     or query.

  2. Add a text box to the form, and name it TimesToRepeatRecord.

  3. Make sure that the Control Wizards button on the toolbox is depressed;
     then add a command button to the form.

  4. In the Command Button Wizard dialog box, click Report Operations in
     the Categories list and click Preview Report in the Actions list. Then
     click Next.

  5. When you are asked which report you would like the command button to
     preview, click rptRepeatRecs, and then click Next.

  6. When you are asked if you want text or a picture on the button, click
     Text, and then click Next.

  7. Name the button PreviewReport. Then click Finish.

  8. Save the form as PrintForm, and switch to Form view.

  9. Type "3" (without the quotation marks) in the TimesToRepeatRecord text
     box, and then press ENTER.

 10. Click Preview Report.

     Note that Microsoft Access opens the rptRepeatRecs report and each
     record appears three times in the report.

REFERENCES

For information about repeating labels in Microsoft Access 1.x, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q117534
   TITLE     : ACC1x: How to Print Multiple Labels in Varying Numbers