ACC: How to Skip Used Mailing Labels and Print Duplicates
ID: Q95806
|
The information in this article applies to:
-
Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
This article describes how to print multiple copies of the same mailing
label, and how to use a partially used page where only some of the labels
are available.
This article assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information
about Visual Basic for Applications, please refer to your version of the
"Building Applications with Microsoft Access" manual.
NOTE: Visual Basic for Applications is called Access Basic in Microsoft
Access versions 1.x and 2.0. For more information about Access Basic,
please refer to the "Introduction to Programming" manual in Microsoft
Access version 1.x or the "Building Applications" manual in Microsoft
Access version 2.0
NOTE: This article explains a technique demonstrated in the sample
files, RptSampl.exe (for Microsoft Access for Windows 95 version 7.0)
and RptSmp97.exe (for Microsoft Access 97). For information about how
to obtain these sample files, please see the following articles in the
Microsoft Knowledge Base:
Q145777 ACC95: Microsoft Access Sample Reports Available in Download Center
Q175072 ACC97: Microsoft Access 97 Sample Reports Available in Download Center
MORE INFORMATIONPrinting 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, Microsoft Access prints one label on each of 20 pages.
On a dot matrix printer, using single column labels, you can work around
this behavior by defining each label as a separate page. However, you
cannot use this method for laser printers or multiple-column labels. To
work around this behavior, use the step-by-step procedure described below.
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 Microsoft Access to use the remaining
labels on a partially used page. Microsoft 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.
Step-by-Step Procedure to Solve Both Problems
The Microsoft Access report generator provides powerful hooks that allow
control 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 code listed below is generic. You can attach it to any Mailing Label
report to print multiple copies and to skip used labels if needed. To use
the example, you need to have a mailing label report called MyLabels.
- Create a new module, and place the following lines in the Declarations
section:
'*********************************************************
'Declarations section of the module.
'**********************************************************
Option Compare Database
Option Explicit
Dim LabelBlanks&
Dim LabelCopies&
Dim BlankCount&
Dim CopyCount&
'==========================================================
' 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& = 1
End Function
'===========================================================
' The following function sets the variables to a zero
'===========================================================
Function LabelInitialize ()
BlankCount& = 0
CopyCount& = 0
End 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 If
End Function
- Open the report named MyLabels in Design view and add the following line
to the OnPrint property of the Detail section:
=LabelLayout(Reports![MyLabels])
- Add the following line to the OnOpen property of the MyLabels Report:
=LabelSetup()
- Although typically labels do not have a report header, add a Report
Header and Footer to the report by selecting Report Header/Footer from
the View menu (or Format menu in version 2.0 or Layout menu in version
1.x). Then, add the following line to the OnFormat property of the
report header:
=LabelInitialize()
- 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 the
first page (BlankCount) and then asks how many of each label you want
printed (CopyCount).
When the report header is formatted, it calls the LabelInitialize()
function, so when you switch from preview to print, the BlankCount and
CopyCount fields are set to zero. As each label is formatted, the
LabelLayout() function adjusts the NextRecord and MoveLayout properties
to skip used labels and print the desired duplicates.
Additional query words:
Keywords : kbusage
Version : WINDOWS:1.0,1.1,2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto
|