Deleting duplicate records

[hkaing79]

I have a form bound to a query. When the user enters the last name, it runs an after update event that checks for duplicates in the table.

It returns the records information to ensure that it really is a duplicate. If the user clicks Yes that it is a duplicate, then the event triggers the Undo command.

But this simply clears the record. The user is still in the new record, so when the user leaves, the form will add a blank record to the table.

Is there another command I should be using instead of the Undo?

 

[dcurtis]

Try using cancel instead of Undo

 

[hkaing79]

The problem is I can't use Cancel. What I have is a form where the user enters FirstName, LastName, and a bunch of other fields.

I'm putting an error check on the LastName After Update event because there's no point in filling the rest of the information if it's a duplicate.

In order to use Cancel = True, I need to use the Before Update or Before Insert. Before Insert fires prematurely because LastName always starts off null. Before Update doesn't add the new record, but it prevents the user from navigating out of the record.

I guess my question is two fold. Which event and which command should I be using?

At the moment, the form works, but if I enter a duplicate record, and click OK to delete, then navigate out, it adds a blank record. I can't put the error trap in the table because the user may not know the first name or the last name. So I can't use required in the table.

Any other suggestions?