why has my autonumber primary key suddenly lost count?

[philwalker531]

i am using autonumber as a primary key so that each 'job' has a different number and it shows it on the form as YORKS**** ( *being the autonumber)

all of a sudden it has stopped working!

i am upto record 175 on my table and up to autonumber YORKS0225 on my form, if i go to add a record is assigns YORKS0117 and wont let me save it as it already exists????

i am using office 2007 for the front end and the back end is on a seperate storage device as i use my laptop to access the same database through a different front end on my laptop.

ive tried google and got completely flummoxed!

can anyone suggest anything???

 

[Rabbie]

Sounds like your DB may be corrupted. Try doing a compact & repair and see if that fixes the problem.

 

[philwalker531]

is that something i do the table (back end) or the whole database?

 

[DCrake]

What is the last autonumber in the table? bet it's not 175.Maybe you have deleted some records in the past but the number is still reserved.

 

[philwalker531]

i dont understand sorry ?!? my reference field which is the autonumber is currently at 226, there are 174 records in the table. i have deleted records in the past from both the table and via the form and not had any problems

 

[DCrake]

How are you determining what the next number will b in the sequence

YORK????

 

[philwalker531]

How are you determining what the next number will b in the sequence

YORK????

thats what the autonumber is ( i think/hope?) it should go up by one every record.

 

[philwalker531]

i have just opened my front end and tried to add a record, each time it failed but added one number!, i have now opened and closed the databse 120 odd times and the autonumber has caught back up with the records! and all good again. thanks for your efforts

 

[boblarson]

Just an FYI - If you CARE what an AUTONUMBER gives you, then you are using it for the WRONG REASON. An Autonumber just guarantees you an UNIQUE number. And that is ALL it should be used for. If you care about sequencing then you should NOT use autonumber and should instead use your own numbering using DMax+1 (and with multiple users there is a method which uses a table with a single record to ensure that nobody requests the same number. You can search for that here on the forum).

But again, you say it is fixed now but you should NOT be using autonumbers it if you care what sequence it is in.

 

[DCrake]

There is something dreadfully wrong if you need to open and close a database 120+ times to correct your problem. You need to identify the culprit and cure that.

 

[gemma-the-husky]

this is a documented error (ie, too low an autonumber, generating insert collisions)

to fix this

a) try compact and repair on the back end. this should reset the next number seed correctly.

if this fails then
b) write an append query to force a new item in with the next aoutonumber, then delete it. this should successfully reseed the autonumber correctly


this should fix this issue - but as others have said, you should not/cannot rely on an autonumber generating an intact numeric sequence. if you need one you need a different solution.

 

[philwalker531]

Just an FYI - If you CARE what an AUTONUMBER gives you, then you are using it for the WRONG REASON. An Autonumber just guarantees you an UNIQUE number. And that is ALL it should be used for. If you care about sequencing then you should NOT use autonumber and should instead use your own numbering using DMax+1 (and with multiple users there is a method which uses a table with a single record to ensure that nobody requests the same number. You can search for that here on the forum).

But again, you say it is fixed now but you should NOT be using autonumbers it if you care what sequence it is in.

thanks for your help............. but explain it to me like im a little slow!! how do i change my table to have my reference number different everytime and not uise it as a primary key?

 

[John Big Booty]

Keep your autonumber field as a record ID, and add a new field to your table for your reference number. You can then use Dmax() +1 to increment your reference number when a new record is added.