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

Certain types of reports require that each record be printed 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.

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

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

117534  (http://support.microsoft.com/kb/117534/EN-US/ ) ACC1x: How to Print Multiple Labels in Varying Numbers