duplicate entries

[Lauren B]

I have a database where users are inputing customers who are identified by a
unique Customer_ID (this is the primary key). If a user attempts to enter a
primary key that is already in use, they will get the following message
after completing the entire form and attempting to save the entry:

"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try again."

Is there a way to code the form (perhaps in the after update property of the
Customer_ID text box "TxtCustomer_ID") so that when a user enters a
Customer_ID that is already in use, a message box will appear immediately
(as opposed to after the user already completes the entire form) that reads
something like "Customer ID already in use, please select a new name."

Thank you in advance for any assistance.

LB

 

[Guest]

hi,
yes. in the before update event of the cust id text box
put something like this
if dlookup("[cust_ID]","yourtable", "cust_ID = '"&
Me.txtcustID &"'" then
msgbox "Customer ID already in use, please select a new
name."
exit sub
end if
this will alert and kill code execution.