Next Autonumber (1 Viewer)
- Thread starter Gismo
- Start date
bob fitz
AWF VIP
- Local time
- Today, 08:15
- Joined
- May 23, 2011
- Messages
- 4,719
Not sure about using a query but you could use DMax() function to determine the current highest number from the table. However, I believe that an autonumber field does not necessarily increase but any set value and indeed could actually decrease.
Why do you need to get the autonumber before it has even been created
Why do you need to get the autonumber before it has even been created
- Local time
- Today, 02:15
- Joined
- Feb 28, 2001
- Messages
- 27,156
This isn't something you can query. However, ...
Read this article from Allen Browne: http://allenbrowne.com/ser-40.html
In it, he shows how to RESET the autonumber seed. But to reset it, you have to first be able to READ it. Which he details.
Note also that it only works if your Autonumber field is set to Increment, not Random.
Read this article from Allen Browne: http://allenbrowne.com/ser-40.html
In it, he shows how to RESET the autonumber seed. But to reset it, you have to first be able to READ it. Which he details.
Note also that it only works if your Autonumber field is set to Increment, not Random.
Minty
AWF VIP
- Local time
- Today, 08:15
- Joined
- Jul 26, 2013
- Messages
- 10,369
I'd be very wary of implementing anything that relies on resetting an autonumber - If you upscale to use SQL server you would not be able to do this. Much better to use the Autonumber as it was intended a unique record ID. Nothing more nothing less.
I never cease to be amazed at the lengths people go to to try and get a intact record number sequence without gaps, which still doesn't really mean anything. Step away from the vehicle...
I never cease to be amazed at the lengths people go to to try and get a intact record number sequence without gaps, which still doesn't really mean anything. Step away from the vehicle...
- Local time
- Today, 08:15
- Joined
- Jul 9, 2003
- Messages
- 16,278
The problem is accountants auditors and the tax man don't like to see missing records from things like invoice lists. However if you apply this requirement to a paper system you are always going to get damaged or defaced invoices. The solution is to always include them. That way, you don't have any missing numbers, so the taxman, accountant, whoever is/are happy. It might be that when you create a record you Mark it as an "Empty Record" "Pending"... If it doesn't get used or filled in, you just leave it.
- Local time
- Today, 02:15
- Joined
- Feb 28, 2001
- Messages
- 27,156
Concur with Minty. In a properly designed database using autonumber PKs, there is never a need to know the next autonumber, which might be wrong anyway in a shared database since the moment you sample that "next" number, someone can come behind you and TAKE it, bumping the next number up by one.
If you are using Autonumber, that is an example of a synthetic key (as opposed to a natural key). If a synthetic key is in use, it has NO MEANING except as a unique number to tell the difference between two records in the same table. If you are trying to infer or assert ANY OTHER MEANING, you are misusing it.
If you are using Autonumber, that is an example of a synthetic key (as opposed to a natural key). If a synthetic key is in use, it has NO MEANING except as a unique number to tell the difference between two records in the same table. If you are trying to infer or assert ANY OTHER MEANING, you are misusing it.
I am using a orders and orders detail separate from stock detail. I don't want to auto stock number on both so I want to know what is the next stock detail number to be utilized in the order table. when order is accepted the detail will be copied into the stock detail table.
I use the autonumer as a system generated batch number for stock received. so, I got it to work. I get the next stock detail auto number to display in my order detail table then I populate that back to the corresponding autonumber . works like a dream