New record error, due to archived data??

[kissthis66]

Newbie here looking for some guidance. If this needs to be in another thread/group, please let me know. Wasn't sure where I should post this.

I archived some records from my main Access table but in doing so, now when I try to add a new record to my main table, I get the following error code: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."



If I understand what is happening, the system is creating a record ID from the ID that were archived, IE record 10 was archived, so when I create a new record, its trying to assign it ID 10 as the record spot is empty even though there's one already in the archived table. Is this what's happening and if so how do I avoid it/fix it? I could put all the records back into my main table but then I would be back to dealing with a slower database.

Would you all need to see the macro/code/etc to be able to provide assistance or is there somewhere I can be pointed to read up on?

[ssanfu]

Welcome to the forum...

OK, first, what do you mean by "archive"? Did you move the record from the main table to the archive table so that data in all record fields are removed from the main table?
What is the PK field? Is it an autonumber type field?

Most of the time, a record is not moved to a different table, it is just marked as archived. Forms (at least my forms) use queries to limit the records displayed (never a table).


It would really help if you would post the dB, just need a few records to illustrate the problem.