[AccessD] Prevent Duplicates

I do not want the user to duplicate a number already used when entering
a new record. They enter the permit number (text) for the record. I
tried the below code on the BeforeUpdate, AfterUpdate, LostFocus of the
field, but the duplicate MsgBox never pops up telling them it is a
duplicate permit number. They move to another field without the message
telling them to enter a different number & it does not even undo the
entry, it does not let them save the record either (nothing happens when
you click save). Do I have the quotes wrong for a text field around
PermitNumber? Or where should I place the code? Somewhere on the form or
does it go on the field?

 

Virginia

 

Private Sub PermitNumber_BeforeUpdate(Cancel As Integer)

  If Not IsNull(PermitNumber) Then

        If DCount("PermitNumber", "tblPermitLog", "PermitNumber = " &
[PermitNumber]) > 0 Then   

            MsgBox "You have entered a Permit Number that already
exists"

            PermitNumber.SetFocus

            PermitNumber.Undo

        End If

    End If

End Sub