My project has a table that has multiple fields for recording information about a client. Four of those fields must be unique for each client and, therefore, I need to prevent duplicate records from being created. I've successfully created a Before Add event that checks two of the Fields (LegalName and DBA) but the other two fields are more complicated and I don't know how to deal with them. Fields 3 and 4 are based on Industry Codes unique to each client; however, some clients don't have such codes assigned to them. Since this is a mandatory field, we've set a policy that a hyphen "-" be entered for those that don't have such codes. In hindsight, this may not have been such a good idea but it is what it is.
As a result, I do not know how to create an event that checks these values for existing records while ignoring values of “-“.
For clarity, all four fields must be checked for uniqueness. I cannot have any of the fields containing duplicate information, regardless if some matches and some does not. It is not an “And” situation but an “Or” one.
Here are my fields that cannot have duplicates (all are indexes):
Field1 = LegalName
Field2 = DBA
Field3 = IndustryCode
Field4 = IndustryCode2
Here is my code that checks Field1 and Field2:
'********** Check if specific record exists ************
dim rsExists
set rsExists = dal.Clients.Query("LegalName='" & values("LegalName") & "' or DBA='" & values("DBA") & "'","")
if not rsExists.eof then
message = "<font color=red>A Client with this Legal Name or DBA already exists!
Please check the Client list before adding a new record.</font>"
BeforeAdd = false
else
BeforeAdd = True
end if
rsExists.Close : set rsExists = Nothing
Question 1: How do I include Fields 3 and 4 into "rsExists" while ignoring entries of "-".
Question 2: How do I customize the message so that I can tell the user which of the four fields contained duplicate information?
Question 3: How do I apply this on the Edit page to prevent existing records from becoming duplicates?
Thank you.
