HOW TO: Skip Used Mailing Labels and Print Duplicates in Access 2000

This article was previously published under Q231801
This article has been archived. It is offered "as is" and will no longer be updated.
Advanced: Requires expert coding, interoperability, and multiuser skills.

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


This article describes how to print multiple copies of the same mailinglabel, and how to use a partially used page where only some of the labelsare available.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

back to the top

Printing Multiple Copies of the Same Label

When you click Print on 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, Access prints one label on each of 20 pages.

On a dot matrix printer, using single column labels, you can work aroundthis behavior by defining each label as a separate page. However, youcannot use this method for laser printers or multiple-column labels. Towork around this behavior, use the step-by-step procedure described below.

back to the top

Using Labels That Would Otherwise Be Wasted

After printing labels, you usually end up with a partially used last page.There is no built-in mechanism in Access to use the remaining labels on a partially used page. Access always starts on a new page. On a dot matrix printer, you can adjust the top of form manually. But you cannot do that on laser printers. To solve this problem, use the step-by-step procedure described below.

back to the top

Step-by-Step Procedure to Solve Both Problems

The Access report generator provides powerful hooks that allowcontrol over the finished product. By calling a function from the OnFormat property of the report's detail section, you can alter the MoveLayout, NextRecord, and PrintSection properties to leave blank spaces or print multiple copies on the same page.The following code is generic. You can attach it to any Mailing Labelreport to print multiple copies and to skip used labels if needed. To usethe example, you need to have a mailing label report called MyLabels.
  1. Create a new module, and place the following lines in the Declarations section:
    '*********************************************************   'Declarations section of the module.'**********************************************************Option Compare DatabaseOption ExplicitDim LabelBlanks&Dim LabelCopies&Dim BlankCount&Dim CopyCount&					
  2. Type the following functions:
    '==========================================================   ' The following function will cause an input box to   ' display when the report is run that prompts the user   ' for the number of used labels to skip and how many   ' copies of each label should be printed.'===========================================================Function LabelSetup ()   LabelBlanks& = Val(InputBox$("Enter Number of blank labels to skip"))   LabelCopies& = Val(InputBox$("Enter Number of Copies to Print"))   If LabelBlanks& < 0 Then LabelBlanks& = 0   If LabelCopies& < 1 Then LabelCopies& = 1End Function'===========================================================   ' The following function sets the variables to a zero'===========================================================Function LabelInitialize ()   BlankCount& = 0   CopyCount& = 0End Function'===========================================================   ' The following function is the main part of this code   ' that allows the labels to print as the user desires.'===========================================================Function LabelLayout (R As Report)   If BlankCount& < LabelBlanks& Then      R.NextRecord = False      R.PrintSection = False      BlankCount& = BlankCount& + 1   Else      If CopyCount& < (LabelCopies& - 1) Then         R.NextRecord = False         CopyCount& = CopyCount& + 1      Else         CopyCount& = 0      End If   End IfEnd Function					
  3. Open the report named MyLabels in Design view and add the following line to the OnPrint property of the detail section:
  4. Add the following line to the OnOpen property of the MyLabels report:
  5. Although typically labels do not have a report header, add a reportheader and footer to the report by clicking Report Header/Footer onthe View menu. Then, add the following line to the OnFormat property of the report header:
  6. Set the Height property for both the report header and report footer to 0.
When you print the report, the report calls the LabelSetup() function,which first asks you to enter the number of used labels to skip on thefirst page (BlankCount) and then asks how many of each label you wantprinted (CopyCount).

When the report header is formatted, it calls the LabelInitialize()function, so when you switch from preview to print, the BlankCount andCopyCount fields are set to zero. As each label is formatted, theLabelLayout() function adjusts the NextRecord and MoveLayout properties to skip used labels and to print the desired duplicates.

back to the top

For a Microsoft Access 2002 version of this article, see 299024.

This article explains a technique demonstrated in the samplefile, RptSmp00.mdb. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:
231851 ACC2000: Microsoft Access 2000 Sample Reports Available in Download Center

back to the top


Article ID: 231801 - Last Review: 12/05/2015 14:30:47 - Revision: 3.2

Microsoft Access 2000 Standard Edition

  • kbnosurvey kbarchive kbhowto kbhowtomaster kbprogramming kbusage KB231801