MS Access 2003: Find next incremental value for an AutoNumber field
This MSAccess tutorial explains how to find the next incremental value for an autonumber field in Access 2003 (with step-by-step instructions).
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.
Advertisements