Label Serial Numbers 1-10 with VBA Code and Report

Justin Swanson 21 Reputation points
2021-02-16T18:18:40.197+00:00

I am using the following code along with a report to generate shipping labels based on a Work Order number, autonumber field.

The labels are placed on each unit, and the units are placed in a box, 10 units per box. How can I alter my code so that the labels are numbered 1-10 in sequence? There are 30 labels per sheet, so each column would need to be labeled 1-10, with 3 columns/boxes per sheet...

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

Thanks,

Justin68660-label-report-snip.jpg

Developer technologies Visual Basic for Applications
0 comments No comments
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.