How to Restart Child Record Numbers at 1 for Each Parent in Access
Question details
The user needs to configure an Access database so that invoice numbers automatically restart at 1 for each new year or parent record, rather than incrementing continuously.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Managing a one-to-many relationship where child records (invoices) require sequential numbering grouped by their parent record (year).
- Observed behavior
- Using standard AutoNumber generates a continuous sequence across all records, failing to reset per parent and confusing business numbering logic.
Ensure you have backed up your Access database before modifying form events or writing VBA code, and verify that your parent-child table relationships are correctly established.
Use the DMax Function in a Subform's Current Event
Assign sequential numbers dynamically by calculating the highest existing number for a specific parent ID and adding one.
Instead of using the AutoNumber primary key for business numbering (like invoice numbers), you should create a dedicated custom number field. The DMax function can look up the highest existing sequence number for a given parent record and increment it by 1 for the new record.
Open your child table (e.g., Invoices) in Design View and add a new Number field (e.g., 'ActivityCounter') to store the sequential number. Do not use the AutoNumber data type for this.
Open your subform in Design View, navigate to the Property Sheet, select 'Form' from the drop-down, and locate the 'On Current' event.
Click the ellipsis (...) to open the VBA editor and input logic to calculate the next number based on the parent ID criteria. For example: Me.ActivityCounter.DefaultValue = """" & Nz(DMax("ActivityCounter", "ParentActivities", strCriteria), 0) + 1 & """".
If multiple users access the database simultaneously, add VBA error handling to trap duplicate key errors (Error 3022). This ensures that if two users attempt to create a record at the exact same time, the system resolves the conflict by recalculating the DMax value before saving.
Manage Relational Data Efficiently with WPS Office
While Microsoft Access handles complex relational databases, many invoice tracking and sequential numbering tasks can be easily managed using WPS Spreadsheet. WPS Office is a free, lightweight suite that is highly compatible with Microsoft Office formats, offering powerful formulas to replicate complex logic without writing VBA code.
- 1. Download and Install WPS Office: Get WPS Office for free from the official website and install it on your device.
- 2. Export your Access Data: Export your Access tables to .xlsx format so they can be easily manipulated as spreadsheets.
- 3. Automate Sequential Numbering: Open your data in WPS Spreadsheet and use formulas like COUNTIFS to automatically count and restart child record numbers based on parent categories.
Frequently Asked Questions
Can I use AutoNumber to restart at 1 for each year?
No. The AutoNumber data type in Access is strictly designed to provide a globally unique identifier for each record in a table. It continuously increments and cannot be configured to restart automatically based on another field's value.
Should I create a new table for each year's invoices?
It is highly discouraged to create separate tables for each year. Storing identical data structures across multiple tables breaks relational database normalization rules. Instead, store the year in a dedicated field within a single invoice table and use queries to filter by year.
How do I handle duplicate numbers in a multi-user Access database?
In multi-user environments, two users might trigger the DMax calculation at the exact same moment. You must add error-handling routines in your VBA code to trap the duplicate values error (Error 3022) and force the script to recalculate the next available number before committing the record.
