Private Sub ABID_AfterUpdate()
On Error GoTo Err_Handler
    
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone

    'Check table for duplicate
    If DCount("ABID", "tblAntibioticsSubjects", "ABID=" & Me.ABID & " AND SubjectID = " & Me.SubjectID) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "This antibiotic has already been selected for this patient.", vbInformation, "Duplicate Antibiotic"
    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 record from many to many table - numeric

Visual Basic