Following up on my post:

I thought we could look at how we can determine the next AutoNumber Value that will be assign when a new entry is made in a table.
 

The Problem

The issue in the context of this discussion is there can be 2 distinct values retrieved from a table. Sometimes, they may even be the same value, but not necessarily!

1. Create a simple table with a AutoNumber PK field and a Text field
2. Add 10 records
3. Delete the last 7 records

So currently, you should have a table with three entries with a PK value of 1 through 3.

So, what is the next number?

Is it 4, OR is it 11!

That is the dilemma and only you can answer as to which value you are after. Both are right and both are wrong, depending on your need. You are afterall the master of your db. 🙂

What we need to understand is 4 may be the next number in the sequence, but the AutoNumber’s counter seed has been ‘indexed’ to 11 and remains at 11 even after we delete records. The only way to reset the counter back to 4 would be to perform a Compact and Repair or use the ResetAutoIncrementCounter function from the article mentioned above.
 

The Solution(s)

The Immediate Next Number

If we want the next value, so 4 (from the initial example).

One approach could be to use DMax, something like:

Nz(DMax("AutoNumberFieldName", "TableName"), 0) +1

Another approach would be to create a query using MAX.

SELECT Max(AutoNumberFieldName) AS MaxValue FROM [TableName];

The Next AutoIncrement Value

Now, if on the other hand we want to know what value Access will be issuing considering things like cancelled entries, deleted record, so accounting for gaps, then we have to turn towards ADO.

So, if we were after finding a value of 11 (from the initial example).

We can create a function like:

'---------------------------------------------------------------------------------------
' Procedure : Tbl_PKNextNumber
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Retrieve the next number for an AutoNumber Field
'               Retrieve the next seed value, not the maximum + 1 value!
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Early Binding -> Microsoft ADO Ext for DDL and Security Object Library
'             Late Binding  -> none required
' References:
'   https://learn.microsoft.com/en-us/sql/ado/reference/adox-api/adox-object-model?view=sql-server-ver16
' Acknowledgment: I originally saw this technique in a forum thread by MVP HansUp
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sTableName    : Name of the table
' sColumnName   : Name of the AutoNumber/AutoIncrement field
'
' Usage:
' ~~~~~~
' ? Tbl_PKNextNumber("Table1", "ID")
'   Returns -> 806 'Which is the value of the PK that will be used with the next entry
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2011-07-19
' 2         2023-02-14              Update URLs of Refs and Copyright
'---------------------------------------------------------------------------------------
Public Function Tbl_PKNextNumber(ByVal sTableName As String, _
                                 ByVal sColumnName As String) As Long
    On Error GoTo Error_Handler
    #Const ADOX_EarlyBind = False    'True => Early Binding / False => Late Binding
    #If ADOX_EarlyBind = True Then
        Dim oCat              As ADOX.Catalog
        
        Set oCat = New ADOX.Catalog
    #Else
        Dim oCat              As Object
        
        Set oCat = CreateObject("ADOX.Catalog")
    #End If

    Set oCat.ActiveConnection = CurrentProject.Connection
    Tbl_PKNextNumber = oCat.Tables(sTableName).Columns(sColumnName).Properties("Seed")

Error_Handler_Exit:
    On Error Resume Next
    Set oCat = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: Tbl_PKNextNumber" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Which we can then simply use by doing:

? Tbl_PKNextNumber("Table1", "ID")

and that returns the next value that will be assigned when a new entry is made.
 

Useful Resources

ADOX Object Model - ActiveX Data Objects (ADO)

ADOX Object Model