Microsoft KB Archive/117534

{|
 * width="100%"|

ACC1x: How to Print Multiple Labels in Varying Numbers

 * }

Q117534

-

The information in this article applies to:


 * Microsoft Access versions 1.0, 1.1

-

SUMMARY
This article describes how to print multiple copies of the same mailing label in varying numbers for each record.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual. This article also assumes that you are familiar with designing reports in Microsoft Access.

MORE INFORMATION
When you choose Print from the File menu, you can choose to print multiple copies of the same report. But when you try to print a single mailing label 20 times, Microsoft Access prints one label each on 20 pages. On a dot- matrix printer, using single-column labels, you can work around this problem by defining each label as a separate page. However, you cannot use this method for laser printers or multiple-column labels. The following example demonstrates how to work around this behavior.

Notes:


 * The sample code below is generic. It can be used with any mailing label report.
 * The sample code below assumes that you have a mailing label report named MyLabels. The report should have a Quantity field that stores a value representing the number of copies you want for each label.
 * In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

  Create a new module and enter the following sample code:

     '********************************************************** 'Declarations section of the module '**********************************************************

Option Compare Database Option Explicit

Dim LabelCopies& Dim CopyCount&

'==========================================================     ' The LabelSetup function uses the value of the Quantity ' field in the mailing label report to determine how many ' copies of each label to print. '==========================================================      Function LabelSetup LabelCopies& = Val(IIF(IsNull(Reports!MyLabels!Quantity),0,_ Reports!MyLabels!Quantity)) If LabelCopies& < 1 Then LabelCopies& = 1 End Function

'==========================================================     ' The LabelInitialize function sets the CopyCount& ' variable to zero. '==========================================================     Function LabelInitialize CopyCount& = 0 End Function

'==========================================================     ' The LabelLayout function counts the number of copies ' for each mailing label when it is printed. '==========================================================     Function LabelLayout (R As Report) Dim x        x=LabelSetup If CopyCount& < (LabelCopies& - 1) Then R.NextRecord = False CopyCount& = CopyCount& + 1 Else CopyCount& = 0 End If     End Function   Open the MyLabels report in Design view and set the detail section's OnPrint property to the following line:

     =LabelLayout(Reports![MyLabels])   From the Layout menu, choose Report Hdr/Ftr. Then, set the report header section's OnFormat property to the following line:

     =LabelInitialize  Set the Height property for both the report header and report footer sections to 0.

When you print the report, the LabelInitialize function in the report header sets the label count to zero. Then, as each label is formatted, the LabelLayout function adjusts the NextRecord property to print the number of labels determined by the LabelSetup function.