Access: Find next incremental value for an AutoNumber field in Access 2003/XP/2000/97

Question: I'm using Access 2003/XP/2000/97 and VB6 as the front-end. I need to get the next value in the AutoNumber field before inserting the next record using SQL. How can I find out what the next value will be in an AutoNumber field?

Answer: You can usually find the next value in an AutoNumber field by finding the max value and then adding one. This works if the AutoNumber field is SEQUENTIAL and not random.

Below is a function that will find the next incremental value in an AutoNumber field. This function will work as long as no record has been started and cancelled after the last record was created, and as long as no records at the end of the sequence have been deleted.

Function NextValue() As Long

Dim db As Database
Dim Lrs As DAO.Recordset
Dim LSQL As String

Set db = CurrentDb()

LSQL = "select max(Supplier_ID) as maxvalue from Suppliers"

Set Lrs = db.OpenRecordset(LSQL)

If Lrs.EOF Then
NextValue = 1
Else
NextValue = Lrs("maxvalue") + 1
End If

Lrs.Close
Set Lrs = nothing

End Function

This function determines the next incremental value for the Supplier_ID field in the Suppliers table. You will need to change the table and column names to reflect your table naming.

Note:

There is one problem with this method. If records have been deleted from the table, the NextValue function may return the incorrect value.

For example, if you had 15 records in your table and then deleted all of the records. The NextValue function above would return 1 as the next number when in reality, the next number that will be assigned to your AutoNumber field is 16.

If you are in a situation where you need to maintain more control over an Auto Incrementing field, you may want to consider generating your own. We've created 3 different examples that you may want to consider:

Create a sequential number that you can control instead of using an AutoNumber field

Create a sequential number that you can control using a format such as AGR-0001

Create a sequential number that you can control using a format such as A05-0001

If after trying the NextNumber function, you receive a "not defined" error on the "Dim db as Database" declaration, you will need to follow some additional instructions.