dlookup using 2 fields

[spnz]

Hi everyone,

I have a form that I use to enter information onto a table.
Lately I have noticed that there are few duplicate names been entered into the db.
The form has 2 txtboxes for the persons name. The table also has the 2 fields.

How can I make a Dlookup check both fields before allowing the new entry?

Thanks for your help.

 

[llkhoutx]

Use a complex criteria field.

My experience is that DLooks are extremely slow. Using a DAO recordset would be much faster.

 

[RuralGuy]

Hi spnz,
You might also ask yourself why there are two fields in the record with the same data!

 

[spnz]

Thanks guys.

The 2 fields don't contain the same data. The first field is Forename and the 2nd is Surname.
I am just wanting to check that with the names combined then there is not a duplicate. e.g Forename John Surname Smith the lookup would look for a John Smith.

IIhoutz do you have an example that may achieve what im trying?

Thank-you

 

[Bat17]

DCount("[PersonID]", "[tblpeople]", "[FirstName] = '" & [FirstNameField] & "' and [lastName] = '" & [lastNameField] & "'")

Watch out for the quotes!

What are you going to do when you do get 2 people with the same name?

Peter

 

[neileg]

If all you want is to prevent duplicates, why not set both fields as a coumpound index set to no duplicates.

However, Peter is spot on in observing that people do have duplicate names...

 

[spnz]

Thanks guys

I know that there might be 2 people with the same name. What I am thinking is that by having a messagebox saying that "This name is already entered, Are you sure you still want to add this name?" Then use a INSERT into statement or if the user presses the NO button then it cancels the insert.

At least then the user is made aware that the name is already on the database.

Thanks I will give your formula a go and let you know