Prevent Duplicate Records from being entered into a Form

[bethabernathy]

Hi - I think this should be easy, but I am not getting anywhere with this. I have a field in a table named "CustomerID". I have set the properties of this field at Indexed to "Yes (No Duplicates)". I have a form for users to enter data into this field. The form does not allow users to enter duplicate records in the "CustomerID" field. I need to find a way to set it so if a user attempts to enter a duplicate record in the "CustomerID" field they get a message saying that record already exists or something like that. At this point, if a user tries to enter a duplicate value and then exists the form, the data is just not saved. Any ideas on how to get a message to appear?

Thanks, Beth beth@integratedresourcemgmt.com

 

[KyleS]

Beth,
We could check to see if a matching ID exist when the user exits the field, and if so, give them your message.

Private Sub txtCustomerID_LostFocus()
If Not IsNull(DLookup("CustomerID","tblCustomers","CustomerID = " & Me!txtCustomerID)) Then
'Give them your message here
Msgbox "Blah Blah Blah"
End If
End Sub Kyle