HOW TO: Repeat Report Records a Specified Number of Times in Access 2000

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 then open the sample database Northwind.mdb.
2. In the Database window, click Reports, 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.
4. Save the report as rptRepeatRecs.
5. Open the rptRepeatRecs report in Design view.
6. On the View menu, click Code.
7. In the Visual Basic Editor, type or paste the following code:

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

8. On the File menu, click Close and Return to Microsoft Access.
9. In the report design for the rptRepeatRecs report, click the Detail bar. If the Property sheet is not already visible, click Properties on the View menu.
10. Click the OnPrint property box, and then click the Build button (...) to the right.
11. In the Choose Builder dialog box, click Code Builder, and then click OK.
12. Type or paste in 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
    					

13. On the File menu, click Close and Return to Microsoft Access.
14. 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 then 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 then click Preview Report in the Actions list. 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, and then click Finish.
8. Save the form as PrintForm, and then 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 that each record appears three times in the report.