ACC1x: How to Print Multiple Labels in Varying Numbers

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

REFERENCES

Microsoft Access "Language Reference," version 1.0, page 313

Additional query words: report duplicate label on format


Keywords          : kbusage RptLabel 
Version           : 1.0 1.1
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: April 6, 1999