Autonumber mess

[Adam]

Hi All,

I have a database that is experiencing problems with 2 tables. There's
an AuditMaster table and an AuditResults table, with a one to many
relationship.

The Key for AuditMaster is AuditID which is an autonumber.

It was index with no duplicates allowed but this has come off
somewhere down the line.

There are now duplicate AuditID's...

The only way I can see to fix this problem is to have the Autonumber
start from 2500, where it is currently at 900~.

Is it possible to have the autonumber commence from 2500 now?


Thank you

Adam

 

[Joseph Meehan]

Hi All,

I have a database that is experiencing problems with 2 tables. There's
an AuditMaster table and an AuditResults table, with a one to many
relationship.

The Key for AuditMaster is AuditID which is an autonumber.

It was index with no duplicates allowed but this has come off
somewhere down the line.

There are now duplicate AuditID's...

The only way I can see to fix this problem is to have the Autonumber
start from 2500, where it is currently at 900~.

Is it possible to have the autonumber commence from 2500 now?


Thank you

Adam

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.

 

[Arvin Meyer [MVP]]

There is a bug in Access 2000 (since fixed) which occasionally allows a
repeat of an autonumber. The problem is first noticed because the index on a
primary key will not allow a duplicate. The fix is to change the autonumber
column to a Long Integer, then add a new autonumber column, add a new Long
Integer column (new foreign key) in the many side table, and do an update
query to get the new Primary Key into the many side table. Once you are
satisfied that everything is working properly, you can delete the original
fields. When doing this (and I've had to do it twice in the last 4 years)
always make a backup first.