How do I create serial numbered shipping labels using Access Reports?

Justin Swanson 1 Reputation point

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
If CopyCount& < (LabelCopies& - 1) Then
R.NextRecord = False
CopyCount& = CopyCount& + 1
CopyCount& = 0
End If
End If
End Function



{count} votes

2 answers

Sort by: Most helpful
  1. Ken Sheridan 2,496 Reputation points

    Add a text box named txtCounter to the label report's Detail section.

    In the (zero-height) Page Header section's Format event procedure initialize the text box to zero with:

            Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
                Me.txtCounter = 0
            End Sub

    In the Detail section's Format event procedure conditionally increment the value in the text box with:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Me.txtCounter = Me.txtCounter + 1
    If Me.txtCounter = 11 Then
        Me.txtCounter = 0
    End If

    End Sub

    This will give the text box values for 1 to 10 per column.

  2. Ken Sheridan 2,496 Reputation points

    PS: I should also have mentioned that the label report's column layout must be set to down-then-across.

    0 comments No comments