Autonum problem

[Cathy]

I have an autonum field in table1 that is the relational field to table2. It
is an indexed field in table1 that will not allow duplicates, but is not the
primary key. I need to add and delete records from both table from time to
time. Recently I ran into a problem when I needed to add several records to
table1. The append query kept trying to reuse a deleted autonum and would
error out. I had to rebuild the tables to make the process move forward
again. Why would it attempt to reuse a number instead of choosing the next
highest number? Right now I don't have a lot of records so it wasn't too
much of a problem. It will be a problem if it does it again in the future.
How can I prevent this error from happening in the future?

Thank you,
Cathy

 

[Ken Snell \(MVP\)]

Does the append query contain the autonumber field as one of the output
fields? If so, remove that field from the output field list of the append
query -- the autonumber field will be assigned a value by the table when a
new record is added.

 

[Cathy]

Ken,

The append query does not have the autonumber field as an output field. I
expected to assign a new number. Instead it kept trying to assign an old,
deleted number. I've never had an autonum behave this way before... have you?

Thnks

 

[Ken Snell \(MVP\)]

Reuse of an "obsolete" number could indicate a corruption problem in the
database. Make a backup copy of the database, then run compact and repair on
the database. If that does not work, create a new database file and import
all the objects and data into that new file from the old file.

Another way that you might get this problem is if you append a record by a
query and that query has the autonumber field in the field list. ACCESS /
Jet will let you assign numbers to an autonumber field by an append query,
and this resets the seed of the autonumber field, which means it could then
start using old numbers if your query appended an old value into that field.