Duplicated autonumber. Need urgent help

[alexasha]

Hi,
I have a database with two linked tables (Main and Encounter). Encounter
table had autonumber column. Based on that number I am able to print reports
for that particular records. For some reason, starting today, any additional
record in encounter table creates duplicate autonumber. I did not change
setting in that column. Please help me to correct that

 

[alexasha]

I have 2003. I did try repairing it. I do not know whether it helped. It
looks like autoumber was reset to 91 from 156. So I copied and deleted one
record until it reached autoID 157. I do not know whether this was the right
thing to do. Any advice how to make sure that it is fixed and prevent future
problems, except to backup daily.

 

[John Vinson]

Hi,
I have a database with two linked tables (Main and Encounter). Encounter
table had autonumber column. Based on that number I am able to print reports
for that particular records. For some reason, starting today, any additional
record in encounter table creates duplicate autonumber. I did not change
setting in that column. Please help me to correct that

Be sure that you have downloaded all the current Microsoft updates to
Office: this was a well-known and persistant bug in several versions
of Access, but AFAIK it's now been fixed.

You might find more information by going to

http://www.granite.ab.ca/access

and searching for "Autonumber"; Tony has some good references on
causes and cures of this type of problem.

John W. Vinson[MVP]

 

[alexasha]

thanks for the info. I think my office is uptodate.
Is it possible to set the autonumber, for example make it start not from 1
but from 100?

 

[alexasha]

Or is it possible to completely remove that autonumber column and create a
new one?

 

[david epsom dot com dot au]

Not fixed: broken again or still broken: Append queries
now always reset the autonumber, downwards as well as
upwards:

http://support.microsoft.com/default.aspx?scid=kb;en-us;884185

So you can use an append query to reset the autonumber
seed, or, here is the Allen Brown example of how to reset
the autonumber seed using code:
http://allenbrowne.com/ser-40.html

(david)

 

[alexasha]

I am quite sure that append query was the cause of that. I just created two
append queries yesterday. I will go through support article to figure out the
problem, but most likely would not run append query on table with autonumber.
Thanks

 

[Allen Browne]

Alexasha, grab the code from this article:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

The code examines all tables in your database, identifies the AutoNumber
field, and checks if it is set negative or below the maximum. If so, it
resets it to 1 more than the highest existing number, after confirmation.