Expert guide

Access AutoNumber Gaps and Reset: What to Do (and Not Do)

AutoNumber is for uniqueness. Sequential document numbers need their own design.

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: AutoNumber gaps are normal: values are consumed by cancelled new records, deletions and failed appends. Use AutoNumber only as an internal primary key and never show it as an invoice or order number. To reset an empty table's seed, compact; to set the next value, use ALTER TABLE ... ALTER COLUMN ID COUNTER(start, 1). For gap-free, formatted document numbers, use a counter table incremented when the document is finalised. This is standard in our Access database design work.

A user notices invoice 1047 is followed by 1049 and asks where 1048 went. In many Access databases the answer is that the invoice number is an AutoNumber, and someone started a new invoice and pressed Escape. Auditors and customers dislike gaps in invoice sequences, and the fix is not to fight AutoNumber but to stop using it for something it was never designed to do.

How AutoNumber Really Works

  • The next value is allocated as soon as a new record is started (the first keystroke on a new row), not when it is saved.

  • If the new record is cancelled, that value is gone.

  • Deleting records never frees their numbers.

  • An append query that fails part way can consume values for rows never written.

  • Values are unique within the table; that is the only promise.

Resetting the Seed

Empty table: compact

After deleting all rows from a table (for example a test or staging table), Compact and Repair resets the AutoNumber so the next record starts at 1.

Set a specific next value

ALTER TABLE tblOrders ALTER COLUMN OrderID COUNTER(10000, 1);

Run it as a data-definition query or with CurrentDb.Execute. The first number is the next seed, the second the increment. Never set it lower than the current maximum, or new records will collide with existing keys. Back up first, and do it with nobody else in the database.

Fix a seed that went wrong

Occasionally, after importing or corruption, the seed falls below existing values and new records fail with duplicate key errors. Find the maximum (DMax), then set the seed to max + 1 with ALTER COLUMN COUNTER.

Why Not to Renumber Existing Records

  • Primary keys are referenced by child tables; renumbering breaks relationships or requires cascading updates.

  • Numbers printed on documents, emails and external systems would no longer match.

  • There is no business benefit: users should not see AutoNumbers at all.

Gap-Free Document Numbers: The Counter Table

tblCounters: CounterName (PK, Text), Year (Number), LastValue (Number)

Public Function NextNumber(ByVal counterName As String) As String
    Dim ws As DAO.Workspace, db As DAO.Database, rs As DAO.Recordset
    Dim yr As Integer, n As Long, tries As Integer
    yr = Year(Date)
    Set ws = DBEngine.Workspaces(0): Set db = CurrentDb
Retry:
    On Error GoTo Locked
    ws.BeginTrans
    Set rs = db.OpenRecordset("SELECT * FROM tblCounters WHERE CounterName='" & _
             counterName & "'", dbOpenDynaset)
    rs.Edit
    If rs!Year <> yr Then rs!Year = yr: rs!LastValue = 0     ' restart each year
    rs!LastValue = rs!LastValue + 1
    n = rs!LastValue
    rs.Update
    ws.CommitTrans
    rs.Close
    NextNumber = "INV-" & yr & "-" & Format(n, "0000")         ' e.g. INV-2026-0042
    Exit Function
Locked:
    ws.Rollback
    tries = tries + 1
    If tries < 10 Then
        Dim t As Single: t = Timer: Do While Timer < t + 0.2: DoEvents: Loop
        Resume Retry
    End If
    Err.Raise vbObjectError + 1, , "Could not get the next number: " & Err.Description
End Function

  • Call NextNumber when the invoice is posted or finalised, not when the draft is created.

  • Store the result in a separate InvoiceNo field with a unique index.

  • Keep the AutoNumber InvoiceID as the internal key for relationships.

  • If an issued number must be cancelled, void the document instead of deleting it, so the sequence stays complete.

Why Not DMax + 1?

ApproachMulti-user safeGap-freeNotes
AutoNumber shown to usersYesNoGaps from cancelled records
DMax(...) + 1 on form openNoMostlyTwo users can get the same number
DMax + 1 at save with unique index and retryMostlyYesAcceptable for low volume
Counter table in a transaction (above)YesYesRecommended; supports yearly reset and prefixes

SQL Server Back Ends

With SQL Server, identity columns behave like AutoNumber (gaps are normal), and SEQUENCE objects or a counter table with locking provide document numbers. Plan this during migration; see AI-assisted Access to SQL Server migration. For an invoicing database built this way, see our Access invoice and payment tracker.

FAQ

Access AutoNumber FAQ

Gaps, resets and invoice numbers

AutoNumber values are assigned when a new record starts, not when it is saved. Cancelled new records, deleted records, failed appends and some compact operations all leave gaps. This is normal and expected: AutoNumber guarantees uniqueness, not a continuous sequence.

For an empty table, Compact and Repair resets the seed. To set a specific next value, run ALTER TABLE tblName ALTER COLUMN ID COUNTER(5000, 1); in a query. Be careful: setting the seed below existing values causes duplicate key errors.

No. Invoice, order and certificate numbers usually need to be sequential without gaps, have a format (such as INV-2026-0001) and sometimes restart each year. Generate them separately from the primary key, using a counter table updated safely when the document is finalised.

Yes. AutoNumber fields with New Values set to Random produce random positive and negative numbers. Incremental AutoNumbers can also wrap to negative after reaching the Long Integer maximum, which is extremely rare in practice.

Store the last used number in a counter table and increment it inside a transaction when the document is finalised, retrying if the record is locked. Assign the number at save or post time, not when a user starts typing, so cancelled drafts do not consume numbers.