First Published 15 Nov 2023
This article was prompted partly by a recent request at UtterAccess forum and by related code sent to me by my colleague Xavier Batlle.
The Label Wizard provided with Access provides a simple method of printing labels for a wide variety of commercially available label sheets.
Whilst this can work well, the wizard does not allow you to print multiple labels for the same person or allow you to handle printing on partly used label sheets.
The example app supplied with this article provides additional functionality much of which is not available using the wizard.
It is based on a cut down version of a label printing form from my School Data Analyser app and includes various options to select from including:
• Selection of data sources
• Name or address labels
• Choice of label size (Avery L7160/L7163)
• Free form input text
• Printing multiple labels for the same person
• Leaving blanks to manage printing on partly used label sheets
The example app has a single form and four reports (name/address labels for each label size). The form looks like this:
The default label options are as shown above. A thumbnail image acts as a guide showing what the report will look like.
You can also select from three different groups of students or select an individual student. A single listbox is used for all options with its properties modified in code.
Click the Print Labels button to either preview the report or send it direct to the printer (checkbox not ticked)
The next screenshot shows an example address report for a year group with no duplicate labels and no blanks.
In the next screenshot, name labels have been selected for a tutor group using a different label size. The thumbnail image is updated accordingly
Two blank spaces have been specified to complete the rest of a partly used sheet.
When the Print Labels button is clicked for a name label report, you are given the option to add a single line of text:
The next screenshot shows an the name label report with the selected options shown above:
This type of label report was also very useful for placing on exam desks during public exams such as GCSEs
In the next example, five name labels will be printed for a selected student with 4 blanks.
This is the report generated:
1. All data is for a fictitious school, Mendip Heights. All student data is also fictitious.
2. Many thanks to Xavier Batlle for supplying the following code to print duplicate labels and leave blanks.
Option Compare Database
'Report code by Xavier Batlle / minor modifications by Colin Riddington
Dim BlankLabels As Integer
Dim NumberOfLabels As Integer
Dim LabelsCount As Integer
Dim BlankLabelsCount As Integer
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If BlankLabelsCount < BlankLabels Then
Me.NextRecord = False
Me.PrintSection = False
BlankLabelsCount = BlankLabelsCount + 1
ElseIf LabelsCount <(NumberOfLabels - 1) Then
Me.NextRecord = False
LabelsCount = LabelsCount + 1
LabelsCount = 0
Private Sub Report_Open(Cancel As Integer)
NumberOfLabels = Nz(Forms!frmPrintLabels!cboLabelNo, 0)
BlankLabels = Nz(Forms!frmPrintLabels!cboBlank, 0)
LabelsCount = 0
BlankLabelsCount = 0
Click to download the example app: Manage Label Printing v1.2.zip Approx 1.3 MB (ACCDB - zipped)
Download and unblock the zip file.
For more details, see my article: Unblock downloaded files by removing the Mark of the Web
Unzip and save the ACCDB file to a trusted location.
Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 15 Nov 2023
Return to Code Samples Page
Return to Top