Microsoft KB Archive/207664

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

Article ID: 207664

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q207664



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

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

IN THIS TASK
SUMMARY
 * Create the Report and the Code Behind the Report
 * Create a Form That Specifies How Many Times to Repeat the Records



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.

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.

back to the top

Create the Report and the Code Behind the Report
 Start Microsoft Access, and then open the sample database Northwind.mdb. In the Database window, click Reports, and then click New. In the New Report dialog box, click AutoReport: Columnar, and base the report on the Shippers table. Then click OK. Save the report as rptRepeatRecs . Open the rptRepeatRecs report in Design view. On the View menu, click Code.  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 </li> On the File menu, click Close and Return to Microsoft Access.</li> 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.</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 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 </li> On the File menu, click Close and Return to Microsoft Access.</li> Save and close the report.</li></ol>

back to the top

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.

back to the top

Additional query words: inf multiple repeat records duplicate more than once

Keywords: kbhowto kbhowtomaster KB207664

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.