Autonumber doing funny things

[Niniel]

Hello,

I just noticed something very strange - in a table of mine, when I create a new record with the form, the autonumber is an existing number. Say the last record had ID 400, then the "new" record is assigned an autonumber ID of 300.
Of course, record creation fails that way.
But with every attempt, the new autonumber moves one up, so eventually it'll start making unique numbers again.

Does anybody have an explanation for this odd behaviour?

 

[boblarson]

Autonumber is not guaranteed to generate sequential numbers. It just guarantees a unique number. If you ever use replication it gets even more weird. It will generate 239049930 and then -1293884792 and then 293488297, etc.

 

[Niniel]

Oh really? That is weird.
But my problem is different - it's re-creating existing numbers. In other words, I already have 1- 400 [with some holes].

 

[CraigDolphin]

You may want to check out this link:
http://allenbrowne.com/ser-40.html

 

[FoFa]

The SEED got reset somehow. I think if you check the properties on that column on the table it shows what the SEED was. I think you can force it to higher number to start from there.

 

[Niniel]

Thank you!

I "fixed" my problem by pretending to create new records until I went past the last existing ID, but maybe I have a bigger problem here; I'll have to run Allen Browne's code.

I can't find the seed information though, where exactly is that hidden?

 

[FoFa]

Oopsie, was thinking about Sql Server, my mistake.

 

[rpadams]

Simple fix

I ran into this problem when I deleted some early records and their autonumbers (1-200 etc.) in a table of a few thousand. It started to put new autonumbers at about 100 and eventually crashed when existing autonumbers eventually caused duplicates. My autonumbers were not important to the table except for indexing and to prevent duplicates.

My fix was very simple:
1) I made a make-table query to make a table with all of the existing records but without the autonumber field in the target (new) table. Sort on the autonumber field. Run this query.

2) Rename the old table (or delete if you want to live dangerously) to something different.

3) Open the new table in design mode and add the autonumber field. Save this table and then rename as in the original table.

If all goes well, the autonumbers will start at 1 and increment with no problem. Note: This system will NOT work if there is an existing relationship link on the autonumber to other tables. My table was on the "many" side of a one to many relation and the link was on the autonumber of the "one" side table.