Unique Primary Keys not so Unique

[Imdabaum]

I was working on a database and created a query for a report. However when I was finished with the query i sent it back and the user informed me that he could no longer enter data into the application. I tried it and it keeps telling me that the new record would duplicate the Primary Key.

I checked the table design for the table and the Primary key an autonumber that is set to long integery, new values = increment, format= nothing, caption =nothing, Indexed= Yes(No Duplicates), Smart tags = nothing

Thought something was wrong. So I tried entering data directly into the table and the autonumber is incrementing but for some reason it is starting 200 record IDs back. The latest id is 3224 and the new one has been incrememnting from 3194... Do I have to go through 28 more errors before it catches up?

The more important question is what would have caused this?:banghead:

[OBP]

It sounds like a Compact & Repair is needed.

[Imdabaum]

What's the thumb down next to the post mean?

[Imdabaum]

Okay this problem is resurfacing. I compacted and repaired and nothing seems to be fixing it this time. Any other ideas?

[OBP]

Is the Autonumber Indexed with "No Duplicates"?

[Imdabaum]

Yep that was the first thing I checked after compacting and repairing.

[geekgirlau]

Try creating an append query where you set the value of the autonumber field to the next number (and dummy data for any other required fields). Now delete the dummy record that you just created. You should find if you now try to insert a new record the autonumber is correct.

[Imdabaum]

That took a long time but eventually I got the counter up where it needed to be. Dummy data and lots of deleting.