Microsoft KB Archive/179288

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

Article ID: 179288

Article Last Modified on 1/22/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q179288



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



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



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
 Start Microsoft Access and open the sample database Northwind.mdb (or NWIND.MDB if you are using Microsoft Access version 2.0). Click the Reports tab and then click New. 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. Save the report as rptRepeatRecs. Open the rptRepeatRecs report in Design view. On the View menu, click Code.</li>  Under Option Compare Database, type the following statements:

<pre class="fixed_text">       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. </li> Close the module.</li> On the Edit menu, click Select Report.</li> On the View menu, click Properties.</li> Click the OnOpen property box and then click the Build button (...) to the right.</li> In the Choose Builder dialog box, click Code Builder, and then click OK.</li>  Type the following code:

Private Sub Report_Open(Cancel As Integer) intPrintCounter = 1 intNumberRepeats = Forms!PrintForm!TimesToRepeatRecord End Sub </li> Close the module.</li> Click the horizontal Detail bar. If the Detail section's property sheet is not already visible, click Properties on the View menu.</li> Click the OnPrint property box, and then click the Build button (...) to the right.</li> In the Choose Builder dialog box, click Code Builder, and then click OK.</li>  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. </li> Save and close the report.</li></ol>

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.

<div class="references_section">