why did a table autonumber reset itself?

[Scott]

hello there - I have quite a complex database made up of loads of tables,
one of which has a field which is an autonumber - used to identify jobs we do
for a customrt; it counted up normally up to about 1090 and then when I added
a new job it jumped down to 1! The problem being that it would therefore
replicate existing records. Not a huge problem as I rectified it by exporting
a copt of the table, deleting it and its relationships and the imported and
recreated the relationships - this made autonumber play as it should.

But the Million dollar question - why did autonumber drop like that? just
one more of Mr Gates' f*ck ups?

 

[Lynn Trapp]

What version of Access? What Service Pack?

 

[Larry Daugherty]

Hi Scott,

I don't know why your autonumber got clobbered but I do know that you are
mis-using an autonumber field to create human readable data. The Autonumber
data type exists solely to generate unique numbers for use as surrogate
keys. Any other use will lead to problems.

I recommend that you generate your own numeric sequence and manage it with
your code.

HTH

 

[Brian]

hello there - I have quite a complex database made up of loads of tables,
one of which has a field which is an autonumber - used to identify jobs we do
for a customrt; it counted up normally up to about 1090 and then when I added
a new job it jumped down to 1! The problem being that it would therefore
replicate existing records. Not a huge problem as I rectified it by exporting
a copt of the table, deleting it and its relationships and the imported and
recreated the relationships - this made autonumber play as it should.

But the Million dollar question - why did autonumber drop like that? just
one more of Mr Gates' f*ck ups?

This is a long shot, but I used to experience this on a table where I had a
negative value in the autonumber column. Don't ask me why - all I will say
is that it seemed like a good idea at the time - but one effect of this was
exactly what you describe: the automumber would periodically reset itself to
1.