Skip series of autonumbers ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a VBA roiutine that adds records to a table. This table has an autonumber key

There are a series of numbers that are reserved and I need to skip allocationg those autonumbers. How is this done?

Essentially I need to run my routine until I come to the first reserved autonumber, then start off again after the last reserved autonumber.
 
Hi David,

Either use an append query to add dummy records with those numbers so
they are already in use; or else change the field type to Long and write
your own VBA routine to assign the numbers you want.
 
I have a VBA roiutine that adds records to a table. This table has an
autonumber key.

There are a series of numbers that are reserved and I need to skip
allocationg those autonumbers. How is this done?

Essentially I need to run my routine until I come to the first reserved
autonumber, then start off again after the last reserved autonumber.
Click to expand...

You should create your own autonumber sequence. The autonumber sequence
from Accesss is just there to give you a unique number (for that table).
You can not guarantee that it will be sequential because it may miss some
numbers if you cancel a new record. And you cant't easily skip some
numbers.

To create your own sequence of numbers you could roughly do this.
Create two tables
- a table containing valid ranges
- a table containing last used number
(it could also contain a history of used numbers)
Your autonumber function have to do this
- Get a lock on one of the tables
(so that only one frontend can increase the autonumber at a time.
Not needed if is only one frontend)
- Get last used number (from table)
- Increment the number
- Check if it is within allowed range, and set it to an allowed value
if it is not allowed
- Store it in the table with last used number
- Release the lock
- Return value to caller

http://www.mvps.org/access/general/gen0025.htm
If you need more info then search for
user defined autonumber
on <http://groups.google.com>
 
See:
Set AutoNumbers to start from ...
at:
http://allenbrowne.com/ser-26.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

David said:
I have a VBA roiutine that adds records to a table. This table has an autonumber key.

There are a series of numbers that are reserved and I need to skip
Click to expand...
allocationg those autonumbers. How is this done?
Essentially I need to run my routine until I come to the first reserved
Click to expand...
autonumber, then start off again after the last reserved autonumber.
 
Back
Top