Others

How to Print Sequential Product Labels in Microsoft Access

Ayan MasoodAyan Masood Oct 12, 2026 868 views

Question details

The user needs to print a variable number of copies for an Access report while generating sequential serial numbers with a fixed prefix and leading zeros.

How to Print Sequential Product Labels in Microsoft Access
Product
Microsoft Access
Device & OS
not provided
Scenario
Generating and printing sequential product labels dynamically from database records.
Observed behavior
The user requires a method to dynamically generate multiple records per label and format the output numbers correctly for printing.
Before you start

Before implementing these solutions, ensure you have backed up your Access database and identified the fields containing your required copy count and starting serial numbers.

Solution 1Recommended

Use a Temporary Numbers Table (TblNums) for Sequential Records

Create a temporary numbers table to generate the exact number of required records for each label without using complex VBA code.

A numbers table (TblNums) is a simple utility table containing a single column of sequential integers. By linking this to your main data query, you can multiply rows based on the required copy count and increment your serial numbers accordingly.

1
Create the Numbers Table

Create a new table named 'TblNums' with a single numeric field. Populate it with sequential numbers starting from 1 up to the maximum number of label copies you anticipate needing.

2
Set Up Main Table Fields

Ensure your main product table includes separate fields for the fixed prefix, the starting serial number, and the required copy count for the labels.

3
Design the Query

Create a new query incorporating both your main table and 'TblNums'. Do not join the tables directly. Instead, add a criteria under the 'TblNums' number field: <= [RequiredCopyCount].

4
Format the Sequential Label

Add a calculated field in your query to format the label output. Use an expression like: LabelOutput: [Prefix] & Format([StartingSerialNumber] + [TblNums].[Number] - 1, '00000'). Bind your label report to this query.

Use a Temporary Numbers Table (TblNums) for Sequential Records
Tip: Using a numbers table is often faster to process and easier to maintain than writing custom loops for report generation.
Free Microsoft Office alternative

Manage Sequential Data and Labels Easily with WPS Office

If Microsoft Access database queries feel too complex for your labeling needs, WPS Office offers a free, lightweight, and user-friendly alternative. You can manage sequential numbering instantly in WPS Spreadsheet and use the Mail Merge feature in WPS Writer to batch print product labels without writing any VBA code.

Free and lightweight alternative to Microsoft OfficeFully compatible with Microsoft Excel (.xlsx) and Word (.docx) formatsGenerate sequential numbers instantly with the drag-and-fill toolIntuitive Mail Merge feature for batch printing labels effortlessly
microsoft office alternative - wps office

Frequently Asked Questions

How do I format numbers with leading zeros in Microsoft Access?

You can use the Format() function in your queries or VBA code. For instance, using Format([YourNumber], '00000') will ensure that a number like 123 is padded to display as 00123.

How can I combine a fixed text prefix with a sequential number?

Store the fixed prefix separately from the numeric portion in your table. When querying the data for your label, concatenate them using the ampersand (&) operator, such as 'PREFIX-' & Format([SerialNumber], '00000').

What is a TblNums table and why do I need it?

TblNums is a simple utility table containing a single column of sequential numbers (e.g., 1 to 1000). It is used in Microsoft Access to duplicate records in a query based on a specific copy count criteria, making it the ideal non-coding solution for printing multiple product labels.