Allow Correction of Duplicate Values in Primary Key

[Steve D]

I have a form with multiple primary keys and when attempting to enter
duplicate values I get error 3022, which I expect. I would like to give the
user the option to fix or delete the record but I seem to get stuck in this
error until I change it to a non duplicate value, only then can I delete the
record. I would like to use the On Error function to create a msgbox that
will allow the user to choose fix or delete. If they choose delete I need the
code that will delete the record without making the user change the value.
Any help would be appreciated.

 

[Jeanette Cunningham]

Hi Steve,
have you tried this approach?
In the before update event of the form, write some code that will find if
the record just entered but not yet save is a duplicate.
If it would make a duplicate, then you put the line
Cancel = True on the next line of the code in the before update event.
Once your code cancels the update, you should find that you avoid the errro
that is currently plaguing you.
Hope that makes sense.
The idea is to examine the record before it can be saved, check for possible
duplicate yourself and if you find that saving it would create a duplicate,
you cancel the save by using the line
Cancel = True in the code that you have in the before update event of the
form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Steve D]

Thank you. How would I be able to check if there will be a duplicate record
created if I am using multiple primary keys?
--
Thank You,
Steve

Hi Steve,
have you tried this approach?
In the before update event of the form, write some code that will find if
the record just entered but not yet save is a duplicate.
If it would make a duplicate, then you put the line
Cancel = True on the next line of the code in the before update event.
Once your code cancels the update, you should find that you avoid the errro
that is currently plaguing you.
Hope that makes sense.
The idea is to examine the record before it can be saved, check for possible
duplicate yourself and if you find that saving it would create a duplicate,
you cancel the save by using the line
Cancel = True in the code that you have in the before update event of the
form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jeanette Cunningham]

You need to build a DCount or DLookup statement or something similar to
check if the data about to be added to the table already exists.
Look at the fields with the data your form wants to add to the table and
work out what range of values would create a duplicate entry for the
critical fields.
You might be able to build a single query to use - it would contain all the
fields that could be involved in creating duplicates. The DCount or DLookup
needs to check this query to see if the combination of values your form
wants to save, already exist in that query.

If that query already has a record with the same combination of values that
your form wants to add to the table, then you have a duplicate about to
happen.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Thank you. How would I be able to check if there will be a duplicate
record
created if I am using multiple primary keys?