Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Retrieving AutoNumber field from an Access Database
- Thread starter ptipat
- Start date
You can't directly access the autonumber "seed" value stored in the database, but there is a way you can deduce its probable value.
Create a query that selects MAX(autonumber field) from the table. If you create it as a saved query, you can use DLookup() in VBA code to get its value. Alternatively, you can create it dynamically in code, using a DAO QueryDef, and then use OpenRecordset to retrieve the single output row.
I say probable value, because this gives you the last number actually added to the table, but this is not necessarily the last number generated. If you're in a table or query datasheet, or a form, based on the table, and you start to enter values in the "new record", Access will immediately generate the next value in the autonumber field. If you then Undo your changes, the record isn't saved, but the autonumber seed has already been incremented and won't be reset. The number that was generated will not be used.
In a multiuser database, there's also a possibility that another user is currently adding a record when you run the query, in which case the seed will have been incremented but the MAX() query won't find a row with that number.
Compacting a database causes each autonumber seed in each table to be reset to the highest value currently in the table. So immediately after compacting, or immediately after adding a record to the table, the MAX() query will give an accurate result (provided no other user is adding a record, that is). Rick Sprague
Create a query that selects MAX(autonumber field) from the table. If you create it as a saved query, you can use DLookup() in VBA code to get its value. Alternatively, you can create it dynamically in code, using a DAO QueryDef, and then use OpenRecordset to retrieve the single output row.
I say probable value, because this gives you the last number actually added to the table, but this is not necessarily the last number generated. If you're in a table or query datasheet, or a form, based on the table, and you start to enter values in the "new record", Access will immediately generate the next value in the autonumber field. If you then Undo your changes, the record isn't saved, but the autonumber seed has already been incremented and won't be reset. The number that was generated will not be used.
In a multiuser database, there's also a possibility that another user is currently adding a record when you run the query, in which case the seed will have been incremented but the MAX() query won't find a row with that number.
Compacting a database causes each autonumber seed in each table to be reset to the highest value currently in the table. So immediately after compacting, or immediately after adding a record to the table, the MAX() query will give an accurate result (provided no other user is adding a record, that is). Rick Sprague
UncleJack
Programmer
- Sep 20, 2001
- 344
Because of the problems outlined by Rick you might be better off NOT to use autonumber, but use program assigned numbers instead. Most people don't use autonumber fields for real data, but only as line counters where the values are really irrelevant anyway. If you choose to use program controlled numbers then you can be assured of knowing the last number generated simply by a DMax operation. Program controlled numbers are usually generated with a DMax + 1 operation.
Uncle Jack
Uncle Jack
- Status
Similar threads
- Locked
- Question