How to Calculate a Year-Based Sequential Order Number in Microsoft Access
Question details
The user needs to generate an OrderNumber in an Access continuous form, formatted as a two-digit year followed by a four-digit sequence (e.g., YY-NNNN) that resets at the start of each year.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Creating a sequence number generator in a multi-user database that avoids duplicate key conflicts during simultaneous data entry.
- Observed behavior
- Attempting to generate the sequence using default value expressions fails to reliably populate the field without requerying the form, which is problematic for multi-user environments.
Ensure you have full design access to your Microsoft Access database and understand basic VBA logic, as creating a reliable multi-user sequence requires adding code to form events rather than relying on simple default values.
Use VBA on the Form's Before Insert Event
This is the most reliable method for multi-user databases. By generating the sequence number at the exact moment a record is saved, you minimize the risk of two users receiving the same number.
Storing the year and the sequence number in two separate table fields prevents complicated string extraction. You can later concatenate them for display purposes.
Using the Before Insert event ensures the number is generated right before the record is saved, reducing conflicts. To fully handle conflicts, you must also add duplicate-key error trapping.
Open your target table in Design View. Create two numeric fields: one named 'OrderYear' (Integer) and one named 'OrderSeq' (Long Integer).
Open your continuous form in Design View. Select the form itself, open the Property Sheet, navigate to the 'Event' tab, and click the ellipsis (...) next to 'Before Insert'.
In the VBA editor, write code to find the highest sequence for the current year. Use: NextSeq = Nz(DMax("OrderSeq", "OrderT", "OrderYear = " & Year(Date)), 0) + 1.
Assign the calculated values to your bound fields: Me!OrderYear = Year(Date) and Me!OrderSeq = NextSeq.
Use DMax Expression for Default Value (Single-User Only)
If you are the only user of the database, you can use a DMax expression directly in the form's control properties to fetch the next number.
Looking for a Free Office Suite? Try WPS Office
While Microsoft Access is a powerful tool for complex database management, WPS Office provides an excellent, free, and lightweight suite for all your document, spreadsheet, and presentation needs. It's fully compatible with Microsoft Office formats and helps streamline your daily office tasks without heavy subscription fees.
Frequently Asked Questions
Can I use an AutoNumber field to generate a YY-NNNN sequence?
No. AutoNumber fields in Access are designed solely to provide unique identifiers for database records. They cannot be formatted to reset every year, nor can they guarantee sequential order without occasional gaps.
Why does my sequence number occasionally skip values?
If a user begins entering a record (which calculates the next sequence) but cancels the entry or encounters a system error, that specific sequence number is often discarded. This is normal in database environments to prevent record deadlocks.
How do I handle duplicate key errors when two users save simultaneously?
In your VBA code, you must include an error handler that specifically traps Error 3022 (duplicate index). When this error is caught, the code should instruct Access to recalculate the DMax function for the next available number and automatically retry the save operation.
