SOS.... AutoNumbering Issue

[Tammy]

Hi All

I've come across an issue that I can't figure out. I have a database that is accessible to my entire group but today I went to add a new record and it is duplicating the PK Autonumber field and then saying there is duplicates in the Primary Key field. It won't allow me to add any new records. I'm at AutoNumber 59 but when I add a new record now it assigns 13 which was used some time ago. Then the message saying I've got a duplicate in the primary key and you can't save this record at this time message comes up.

Any Idea?

Seriously stressed.



Tammy

[June7]

Never encountered this before.

Try Compact & Repair to see if that fixes issue.

[Tammy]

this database is a split database. The back end is most likely where the issues resides but I'm afraid of what will happen if I run the Compact and Repair on it. I'm afraid all the linking will get messed up. I could copy the backend and test the compact and repair on it I suppose as a test.

[June7]

Start with that.

[Tammy]

That may have fixed it. I just did a test and it looks like the numbering is now working. I'll keep my fingers crossed for the live database.

[John_G]

To what extent are you using VBA code to add or delete records? Adding records and explicitly specifying an autonumber value may LOOK like it works, but it can cause serious problems, one of them being the error you were getting.

[Tammy]

I'm not using VBA code. I think the problem stemmed from a sub form with a query that related to the PK from the main table. I put a delete button on this sub form which allowed people to delete the record but I believe it traced it back to the main table through the PK and deleted the record from the main table. Hope that makes sense. I've since adjusted the query so it doesn't link to the PK of the main table but the FK from the secondary table.

Thanks All.

Tammy