I have been using the code below that was so kindly provided to me by a superior UA user:

Private Sub dcheck_num_BeforeUpdate(Cancel As Integer)

' dimension varibles
Dim stCheck As String
Dim stAccount As String

' check there's an account number, else exit
If IsNull(Me.acc_num) Then
Exit Sub
' check there's a check number, else exit
ElseIf IsNull(Me.dcheck_num) Then
Exit Sub
End If

' assign values to our variables
stCheck = Me.dcheck_num.Value
stAccount = Me.acc_num.Value
' perform actual verification
If stCheck = DLookup("[dcheck_num]", "checks", "acc_num='" & stAccount & "'") Then
' notify user check is a dupe
MsgBox "Check number already exist in the system"
' clear all changes
Me.Undo
End If
End Sub


The code is used in a form that contains the following elements:

tied to table "checks"
fields are "acc_num" and "dcheck_num"

The code is used to make sure that two check numbers are not entered for the same account number. The code was working fine, but I realized (after a week) that it was only working on some account numbers. This is crazy to me and I have no explanation. If anyone sees a problem with this code or has alternate suggestions, please help me out, as I have no idea what to do. Thanks in advance for any help!!!!