ACC1x: How to Print Multiple Labels in Varying Numbers

ID: Q117534


The information in this article applies to:


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:

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


  2. Open the MyLabels report in Design view and set the detail section's OnPrint property to the following line:
    
          =LabelLayout(Reports![MyLabels]) 


  3. From the Layout menu, choose Report Hdr/Ftr. Then, set the report header section's OnFormat property to the following line:
    
          =LabelInitialize() 


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