Reject duplicate field with number datatype

[HGoodloe]

I have a form with some text boxes and one of them is for a Code Number (number datatype). The form also contains an Add New Record button. I’m trying to code the Code Number field to reject any duplicate numbers. Yes, I’m aware that there’s an option in the field table properties that will allow for duplicates and no duplicates. But in this case, I would like to code the Code Number text box on the form to reject duplicate numbers along with a message box. Is this possible?

 

[dhookom]

Did you try this reply from the other thread where you asked this question?

You can use something like this for new records. I'm not sure if this is an issue for existing records.
[CODE vba]
Private Sub Code_Number_BeforeUpdate(Cancel As Integer)
If Form.NewRecord Then
If Not IsNull(DLookup("code_Number", "County_Rec", "Code_Number=" & Me.Code_Number.Text)) Then
MsgBox "Duplicate Code Number."
Cancel = True
End If
End If
End Sub [/code]


Duane
Hook'D on Access
MS Access MVP