Duplicated autonumber. Need urgent help

[Guest]

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

 

[Joseph Meehan]

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

What version of Access do you have?

First make a copy and try a repair and compact on the copy.

 

[Guest]

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]

 

[Guest]

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?

 

[Guest]

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)

 

[Guest]

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

 

[Joseph Meehan]

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.

You may want to start a new database and import the data into the new
database allowing the new database to create and renumber the problem field.

I suggest you may not want to use Autonumber for that use. Autonumbers
are designed to provide unique numbers. It in not designed to provide
numbers in order and for a number of reasons may not do so. As a result
using them in any application where the user sees the numbers is likely to
end up with confusion.

There are other ways of providing the numbers you want depending on the
particual application.