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

SteveL13
SteveL13 used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
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

Author

Commented:
I assume you mean the beforeupdate event of the form, correct?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Yes.
Distinguished Expert 2017
Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial