Checking for duplicate before inserting record into Access

Question

I have a form where I don't want the user to be able to enter a contact with the same name.

The form has a save button. To check for a duplicate, part of that button's code is :-

If Me.DataEntry = False Then
Else
    'Check for duplicate contact
    If DCount("[ContactID]", "tblContactsNew", "[first_name] = " & "'" & Me.FIRST_NAME & "'" & "And [Surname] = " & "'" & Me.SURNAME & "'") <> 0 Then
        MsgBox "A contact with these details already exists:-" & vbCrLf, vbOKOnly + vbExclamation
        End
    End If
End If

which is before :-

DoCmd.RunCommand acCmdSaveRecord

If the count <> 0, the message is displayed. But if I then click my Cancel button, there is still a record added for the duplicate.

The code for the cancel button is :-

If Me.Dirty Then
Me.Undo
End If
DoCmd.Close acForm, Me.Name

How can I prevent the duplicate record being written?

Erik A Erik A · Accepted Answer · 2017-10-13T10:12:40

I'd migrate all that code to the forms Form_BeforeUpdate event.

In your save button code, the only thing you need is DoCmd.RunCommand acCmdSaveRecord

Then, in your Form_BeforeUpdate event:

If Me.DataEntry = False Then
    Cancel = True 'Don't save the form I guess
Else
    'Check for duplicate contact
    If DCount("[ContactID]", "tblContactsNew", "[first_name] = " & "'" & Me.FIRST_NAME & "'" & "And [Surname] = " & "'" & Me.SURNAME & "'") <> 0 Then
        MsgBox "A contact with these details already exists:-" & vbCrLf, vbOKOnly + vbExclamation
        Cancel = True 'Cancel the update
        End Sub
    End If
End If

You can add a global to the form, set that to True in your cancel button, and don't save if it is True

Checking for duplicate before inserting record into Access

1 Answers