Hi everyone,
I designed an Access database with forms so that our HR depts can enter and track covid records on employees. I was just getting to roll this out in live when a user reported that their entries were creating duplicate records in the table.
I never could figure out why it would randomly duplicate a record, but I did find a fix by setting the report date indexed setting to Yes (No Duplicates). I should have turn this setting on in the beginning since employees would never have multiple records with the same report date. Unfortunately, Access won't let me save this setting with records currently in the table. I get the message:
"the changes you requested 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."
The only way Access lets me turn this setting on is if I delete all the records in the table. Since I don't really have a field to uniquely identity each row (employees can have multiple covid records), I just used an ID field as a primary key with autoincrement number. I tried deleting all the reports and importing them back in, but I get the same error. I remove all duplicate records and still have the error.
I don't understand why it would complain about duplicate records or the index when the table has been flushed out. I tried just deleting the ID field completely with no PK and that didn't work either. At this point, I don't know what to do beyond just manually rekeying all the records and I'm not sure that would work either.
Is my only option to just create a brand new table? Thankfully my table only has 50 records so it's not too late to do a redesign here.