Not allow data entry in a new record if situation already exists.

I have 3 text fields on a form.  They are txtCode, txtLastName, and txtCombined.  txtCombined puts the 1st two fields together.  So, for example, if I enter "1234" (is a text field),  in the first field and "Johnson" in the 2nd field then txtCombined displays 1234Johnson.

But if I have already entered that combination in a previous record I want to display a message to the user letting them know that combination already exists and undo the two field entries so the user can try again.

I've tried all kinds of beforeupdate and afterupdate events and oncurrent events but nothing is working.  Thoughts?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
You can use the BeforeUpdate event:

Cancel = CBool(DCount("*", "YourTable", "[Code] = '" & Me!txtCode.Value & "' And [LastName] = '" & Me!txtLastName.Value & "'"))

If Cancel = True Then
    MsgBox "That combo exists."
End If

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
I assume you mean the beforeupdate event of the form, correct?
Gustav BrockCIOCommented:
1. Storing the same piece of data twice is not necessary as well as being bad practice.  You can always concatenate the two fields in a query if that's how you want to view them.
2. If you need the values of a combination of fields (up to 10 separate fields) to be unique, create a unique index.  Open the index dialog.  On the first available blank line, give the new index a name.  Select the first column and choose the unique option.  On the next empty line, skip over the index name and enter the next column and so on for up to a maximum of 10 fields.  Leaving the index name blank tells Access that a field belongs to the previously named index.  You can't create multi-field indexes by using the table design view as you can for single field indexes.  You must use the Index dialog box.  You can however create multi-field primary keys by using holding down the Cntl key and selecting multiple other fields.  However, it is usually easier to have a single field PK so people use the autonumber as their PK and create one or more unique indexes to implement business logic.
3. Gus gave you a method to check for duplicates using the Form's BeforeUpdate event.  This is the best place to do this check because you can Cancel the update if a duplicate is found.  Even with the unique index, which will prevent the duplicate from being added, many people like to do the validation them selves so that they can give the user a "nice" error message.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.