Text box validation

[robertm600635]

I have a form with several text boxes that I want to check for duplicate
values in the table. The PK inthe table is an Autonumber. What I want to do
is not allow the FirstName LastName FileNo and SSN to be the same for any two
records. The combination of these fields would garuntee that the same
individual would not be allowed to have two records created.

 

[Jeff Boyce]

You could set a unique index in the underlying table on those four fields.
This would disallow the entry of duplicates (but only after the fact --
after the user entered values and tried to save them).

Another approach would be to give the user an unbound combobox that lists
the values that already exist. That way, they can check first to see if the
one they want is already there.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[robertm600635]

I have a combo box that lists the values (this is how the users select an
individual) but I can't always get them to check it. I was hoping for a VBA
solution that would check the fields (maybe against a query if the fields)
and display a message box if the record already exsists. Some sort of
multiple field validation.

 

[Jeff Boyce]

Again, if you'll add a 4-field unique index to the underlying table, the
users will not be able to add "duplicates".

You can tell them "use the combobox and look it up first, or enter your data
and get told it already exists -- your choice!" When you put it that way,
it is clearly in their interest to use the combobox first. But if they
forget, or are just plain obstinant, they still get reminded...

Regards

Jeff Boyce
Microsoft Office/Access MVP