Access autonumber not incrementing correctly

[Martin]

Hi everyone,

i have a problem with a autonumber field.

when it increments is sometimes misses some numbers eg.

1
2
3
4
7
8
11
12
13

I didn't delete records number 5, 6, 9 ,10 neither did I press the escape
key for these records.
Can anyone tell me what the problem is? why access auto number is behaving
like this?

I'm using access 2000 and my database is in a shared environment with 5
users.

Please some help,

Martin.

 

[Roger Carlson]

Actually, the autonumber is incrementing just exactly the way that it was
intended. Even though you didn't delete a record or press ESC, on of your
other users probably did. You should not be using an autonumber field to
create sequential numbers. It isn't designed for it. If you must have
sequential numbers without gaps, then you will have to code it yourself.

On my website (see sig below) is a small sample database called
AutonumberProblem.mdb which shows one way to do it. Since you are in a
multi-user environment, use the Multiuser sample.

 

[Martin]

Hi thanks for the reply,
I'll check the samples out.

Just a note, I don't think it's my users fault. In our company we've an
understanding not to press the escape key or delete any records. And they
have been working like this for several moths.
But today I see that I'm missing almost 20 numbers in the sequence and they
are random.
Sometimes 6 numbers after each other. I thought it was a bug I didn't know
about?

Anyway I'll check your examples, thanks you a lot.

Regards,

Martin.

 

[John Vinson]

But today I see that I'm missing almost 20 numbers in the sequence and they
are random.

Running an Append query to add records will often cause gaps of this
type (though gaps of hundreds or thousands are actually more common in
my experience).

Bottom line: if you want gapless sequential numbers - program them
yourself; Access will NOT work. And prohibiting your users from
correcting or deleting errors is not going to be very fruitful in any
case...!