Others

How to Generate Sequential Numbers Starting at 100 in Access

Phi Hung Vo Sep 25, 2026 869 views

Question details

The user needs to create sequential customer numbers beginning at 100 in a numeric field (NewNo) within an Access 365 Companies table, without relying on the AutoNumber data type.

How to Generate Sequential Numbers Starting at 100 in Access

Product: Microsoft Access 365
Device & OS: not provided
Scenario: Assigning custom sequential customer numbers dynamically when new records are added.
Observed behavior: The field requires a custom sequential numeric value starting at 100 generated on-the-fly, avoiding prepopulated empty records.

Before you start

Ensure you have a backup of your database and exclusive access to the form's design view before implementing custom sequential numbering routines.

Solution 1Recommended

Generate Numbers Dynamically Using VBA and DMax

Instead of prepopulating records, calculate the next sequential number only when a new customer is added using database functions.

Prepopulating many empty records in anticipation of adding customers can lead to database bloat and numbering errors. It is a best practice to generate one new customer number at the exact moment you need to add a customer.

Open Form in Design View

Open the form used for data entry into your Companies table in Design View.

Access the Form's Event Properties

Select the form properties, navigate to the Event tab, and locate the 'Before Insert' or 'Before Update' event.

Add VBA Code

Click the ellipsis (...) to open the VBA editor and use the DMax and Nz functions to find the highest current number. Use code similar to: Me.NewNo = Nz(DMax("NewNo", "Companies"), 99) + 1. This ensures the first record starts at 100.

Save and Test

Save the VBA module and the form, switch to Form View, and add a new record to verify the NewNo field generates 100 (or the next available number).

Generate Numbers Dynamically Using VBA and DMax

Multi-User Environments: If multiple users are adding records simultaneously, handle possible conflicts by placing the generation code at the moment the record is saved (Before Update) rather than when entry begins.

Solution 2

Utilize a CustomNumber Sample Database Template

Leverage a pre-built sample database that handles configurable starting numbers and multi-user conflicts automatically.

Free Microsoft Office alternative

Looking for a Lightweight Alternative for Your Office Tasks?

While WPS Office does not include a database management tool like Microsoft Access, it offers excellent free alternatives for Word, Excel, and PowerPoint. If you need to manage customer data without complex coding, WPS Spreadsheet provides robust data sorting, filtering, and calculation features.

1. Download the Installer: Visit the official WPS Office website to download the free installer.
2. Install the Software: Run the downloaded file and follow the on-screen instructions to complete the setup.
3. Manage Data Easily: Open WPS Spreadsheet to effortlessly manage, sort, and format your customer data lists as a simple alternative to complex databases.

Free to use with a familiar, intuitive interface.Fully compatible with Microsoft Word, Excel, and PowerPoint formats.Lightweight installation that runs smoothly on most devices.Excellent for managing flat-file customer lists and data via WPS Spreadsheet.

microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I prepopulate empty records for customer numbers?

Prepopulating empty records can cause database bloat, indexing issues, and skipped numbers if records are deleted or abandoned before being fully filled out by a user.

Can I use the AutoNumber data type to start at 100?

While you can technically force an AutoNumber to start at a specific value using an Append query, it is generally recommended to use a custom numeric field and VBA (like DMax) if you require strict sequential numbers, as AutoNumbers are designed for unique identifiers, not continuous counting.

How do I prevent duplicate numbers in a multi-user Access database?

In multi-user environments, generate the sequential number at the very last moment before the record is saved (such as the BeforeUpdate event) and implement error handling to retry the numbering if a simultaneous write collision occurs.

Office Quick Solutions