If it's important enough, you could always create a new record in code, save the new autonumber value plus 1 to a variable, and then delete the new record you just added. It will create another gap in the sequence, but will ensure that you get the right number even if you deleted records with the highest existing numbers (leaving a gap before the new record). -- Bruce M. Thompson bthmpson@big_NOSPAM_foot.com
"Lee Cartwright" <lee.cartwri...@bull.co.uk> wrote in message
Set the query "autonumber" field to sort desending. And set the query TOP VALUES to 1. This will return the LAST autonumber. Add a calculated field to the query something like: NextNumber: [autonumberID] + 1
If no records have been deleted from the table (for argument's sake) and your last record had an autonumber value of 1000 and you deleted that last record, the last autonumber would be 999. The next autonumber value would be 1001, not 1000 unless you compacted the file after the deletion. You cannot depend on the highest existing autonumber value to be a value 1 less than the next autonumber value. -- Bruce M. Thompson bthmpson@big_NOSPAM_foot.com
"HSherriff" <HSherr...@scana.com> wrote in message
> Set the query "autonumber" field to sort desending. And set the query TOP > VALUES to 1. This will return the LAST autonumber. Add a calculated field to > the query something like: > NextNumber: [autonumberID] + 1
"Lee Cartwright" <lee.cartwri...@bull.co.uk> wrote: >Is there anyway, within a query, to find out what the next number in an >incrementing Autonumber field will be?
In a query, no. In code you can indeed do this. So whenever I need to know the just added ID, for example I'm adding child records in a related table, I use a recordset instead of an append query along with the following code.
Frequently you have to check to see if a record in a table which matches certain critieria exists and get its ID number. If it doesn't exist you need to add it and then get the ID number. Use the following code. Dim MyDB As Database, MyTable As Recordset Set MyDB = DBEngine.Workspaces(0).Databases(0) Set MyTable = MyDB.OpenRecordset("Customers") MyTable.AddNew MyTable("Company Name") = "Finnegan's Foods" MyTable.Update MyTable.Move 0, MyTable.LastModified CustomerID = MyTable("CustomerID") MyTable.Close MyDB.Close Set MyTable = Nothing Set MyDB = Nothing
Tony ---- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm VolStar http://www.volstar.com Manage hundreds or thousands of volunteers for special events.
