Others

How to Auto-Generate Custom Sequential Serial Numbers in Access

Aamir Naveed Akram Oct 20, 2026 868 views

Question details

The user needs to automatically generate sequential serial numbers in Microsoft Access that combine a specific text code (like a city code) with a four-digit numeric sequence (e.g., N125-0001, N125-0002).

How to Auto-Generate Custom Sequential Serial Numbers in Microsoft Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Creating new data records via a bound form where selecting a city code triggers the calculation and population of the next available sequence number for that specific city.
Observed behavior: The user is attempting to use the DMax function via VBA in a text box event to calculate the next sequence number, but is struggling with syntax, punctuation, and correctly triggering the form event.

Before you start

Verify that your form controls (the city combo box and the text box for the code) are accurately bound to your table fields, and ensure your table stores the sequential number as a numeric data type separately from the text prefix.

Solution 1Recommended

Use the DMax Function in the After Update Event via VBA

Calculate and format the next sequential number automatically using the DMax function in a form's After Update event.

To reliably generate a custom serial number, use the DMax function to find the highest existing numeric sequence for a specific code prefix, increment it by 1, and format it with leading zeros.

It is best practice to store the numeric sequence and the city code as separate fields in your table, relying on expressions only for formatting the display. Storing the combined string as one field can cause DMax calculations to fail or behave unpredictably.

Open your form in Design View

Right-click your target form (e.g., PIA) in the Navigation Pane and select 'Design View'.

Access the After Update event

Select the city combo box (e.g., CCity) or the code text box (e.g., txtCode). Open the Property Sheet, navigate to the Event tab, and click the ellipsis (...) next to the 'After Update' property to launch the VBA editor.

Enter the DMax expression

Insert the following VBA code to calculate the sequence: Me.txtSerial = Me.txtCode & "-" & Format(Nz(DMax("[Serial]", "POR", "[Code]='" & Replace(Me.txtCode, "'", "''") & "'"), 0) + 1, "0000")

Force the record to save

Add 'Me.Dirty = False' on the following line in the VBA editor to ensure the record is saved immediately after the serial number is assigned.

Use the DMax Function in the After Update Event via VBA

Multi-User Environments: If multiple users will be generating records simultaneously, implement a unique index or use transaction-based validation at the table level to prevent the assignment of duplicate serial numbers.

Free Microsoft Office alternative

Looking for a Lightweight, Everyday Office Suite?

While Microsoft Access handles complex relational database needs, most users require robust tools for documents, spreadsheets, and presentations. WPS Office is a fast, lightweight, and free alternative to the core Microsoft Office suite.

1. Download and install: Visit the official WPS website to download the free installer and follow the quick setup instructions.
2. Open existing documents: Launch WPS Office and open your .docx, .xlsx, or .pptx files directly, enjoying complete format compatibility.
3. Utilize built-in tools: Take advantage of integrated features like PDF conversion, templates, and cloud sync to boost productivity.

Fully compatible with Microsoft Word, Excel, and PowerPoint formats.Free to use with a lightweight installation that saves computer resources.Familiar tabbed interface allows for seamless navigation across tools.Built-in PDF editor lets you view, edit, and convert documents effortlessly.Easy migration enables you to open your existing Office files without losing formatting.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my DMax expression returning a syntax error in Access?

Syntax errors in DMax are usually caused by incorrect quotation marks around text criteria. When passing a string parameter like a city code, you must enclose it in single quotes within the double quotes (e.g., "[Code]='" & Me.txtCode & "'"). Using the Replace function to handle apostrophes prevents further errors.

Can I store the formatted serial number (e.g., N125-0001) as a single text field in my table?

While possible, it is highly discouraged. If you store the combined string as one text field, Access will sort and calculate based on alphabetical rules rather than numeric value, causing the DMax function to fail when you reach higher numbers like 10,000. Always store the numeric sequence separately.

What does the Nz function do in this VBA sequence?

The Nz function converts Null values to a specified value, typically zero. If you are creating the very first record for a specific city code, DMax will not find any existing sequence numbers and will return Null. The Nz function changes that Null to 0, so adding 1 gives you the correct starting serial number of 1.

Office Quick Solutions