Skip series of autonumbers ?

[David]

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.

 

[John Nurick]

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.

 

[Rolf Østvik]

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.

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>

 

[Allen Browne]

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.

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.