AUTONUMBER PROBLEMS

[BillA]

I have a form that will be used to update and add records to a table, pretty
straight forward stuff.
I've been getting an Error every time I try to add a record.
"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
(Error 3022)"

I believe a found the problem. When I attempted to enter data directly into
the table - I noticed the "AutoNumber" field, which happens to be the PK for
the record, was creating a number that already existed. Where the number
should have been 265, instead I get 81... and that number already exists. I
tried it again and I got 82, then 83 and each time I get Error 3022.

Any suggestions in reestablishing this AutoNumber, I'd rather not have to
renumber the existing records as they are related to numerous other tables.

Thank you in advance,
Bill

 

[KARL DEWEY]

First backup the database and then do TOOLS - Database Utilities - Compact
and Repair Database.

 

[BillA]

Hi Karl:
I've tried compacting and repairing... nothing seems to work.

 

[Maurice]

It's a work around but it may work.

Make a backup of your work before trying this.. just to be sure!

1. Create a table with one field - datatype number (integer)
2. Open the table and enter the last autonumber you know exists in the
problem table in your case 265
3. Save the table
4. Create a query using the table you've just created and select the number
field
5. Now make it an Append query and append that one field to your
autonumberfield in the problem table
6. run the query
7. You problem table should now number again from 265 and so on.

saves time renumbering the whole thing.

Still leaves the point that there must have been some corruption otherwise
this wouldn't have happened. So be on the count for making regular backups of
your work.

hth

 

[BillA]

Maurice:
That did the trick... thank you for your time and advice.

Bill