Autonumber out of wack

[Sherwood]

Hi

I have a client with a table that has an autonumber field.
They went to add a new record (It was already poulated with records) and
got a dup error.
Somehow the autonumber generated by access is less than the existing
largest autonumber field

(i.e. records 1 thru 100 has sutonumber matching record number, but adding
a new record access puts 50 in the autonumber field. Duh.)

Is there a VB snipit to set the autonumber > than the # of records in the
table
(i.e. set autonumber to 101)

Thanks,
Sherwood

 

[Larry Daugherty]

I suspect that there are interesting aspects to your design and/or the use
of your application that you haven't told us.

Bear in mind that the Autonumber field's only purpose in life is to generate
a unique number in that field. Notice that the autonumber is not guaranteed
to be sequential. If you use that autonumber for any human readable purpose
you are almost guaranteed to come to grief in time (sounds like you might
already be there). If you need to generate a meaningful sequence, you
should generate it with your own code.

You might do a Find or Google search on microsoft.public.access on
Autonumber to see the above message repeated a few times with some
additional explanations. There is also essentially the same information on
http://mvps.org/access

HTH

 

[Theo]

I had this same problem happen acouple of times. I had to
make a copy of the table, delete the old and rename the
copy to the original table name. Then the autonumber
worked correctly. This fix only causes a problem if ther
are relationships to the table. then you have to also
delete and recreate all the relationships.