Hash Deleted and autoincrement by 2 (1 Viewer)

[Shaimaa T]

Hi All,

When I have a record that I copy and paste into a new record with one column having a unique constraint , I display message "Cannot insert duplicate code" then if not all values in the record are inserted , I get a #deleted in all columns

Refreshing , I find that the id is incremented by 2 instead of one.

That is if the previous record has ID 20 , the #deleted record has ID 22 .

Anyone has an explanation and ideas to avoid the #deleted which happens if not all columns are filled(an explanation for this too please)

 

[Cotty42]

Your issue is with the Unique field.

When you copy and Paste a record you will violate the unique field constraint so Access will delete the newly created record. There is no way around this using copy and paste. You probably need to create a query / SQL (APPEND) to copy the all of the fields to a new record and then generate the new unique identifier.

I guess the double increment is as a result of the above unique identifier conflict.

 

[Shaimaa T]

What tells Access to delete the record ? Is there something in Access/SQL that tells to delete a record if a duplicate column is found for the column with unique constraint?

 

[Cotty42]

I guess it is hard coded into Access to eliminate unique identifier violations. So you either have to remove the unique identifier requirement or find another way of creating a duplicate record without the unique identifier.