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

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

Set rsc = Me.RecordsetClone

SubID = Me.SubjectID.Value
stLinkCriteria = "[SubjectID]=" & SubID

    'Check table for duplicate
    If DCount("SubjectID", "tblSubjects", stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "The Subject ID " & SubID & " 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 Subject ID.", vbInformation, "Duplicate Subject ID"
        '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

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

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

Set rsc = Me.RecordsetClone

SubID = Me.SubjectID.Value
stLinkCriteria = "[SubjectID]=" & SubIB

    'Check table for duplicate
    If DCount("SubjectID", "tblSubjects", stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "The Subject ID " & SubID & " 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 (numeric)

Visual Basic