Private Sub LastName_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Handler

Dim LN As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

LN = Me.LastName.Value
stLinkCriteria = "[LastName]=" & "'" & LN & "'"

    'Check table for duplicate
    If DCount("LastName", "tblPatients", stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "The last name " & LN & " is already in use." & _
        Chr(13) & Chr(13) & "Please check to see if this patient has already been entered. " & _
        "If not, then assign the patient a different number.", vbInformation, "Duplicate Last Name"
        'Go to the duplicate record
        'rsc.FindFirst stLinkCriteria
        'Me.Bookmark = rsc.Bookmark
    End If

Set rsc = Nothing

Exit_Handler:
    Exit Sub
Err_Handler:
    If Err.Number = 94 Then
        ' Invalid use of Null error
        Resume Exit_Handler
    Else
    End If
End Sub

Check for duplicate value (text)

text

Visual Basic