Multiple Field Index Problem

[zufie]

Hi,

I created a Multiple Field Index containing the fields: First Name,
Middle Initial, Last Name to prevent duplicate names to be added
(prevent duplicate records to be added)

I only required the fields: First Name, Last Name as the Middle
Initial is rarely added to records.

Under Index Properties next to "Unique" I selected "Yes" on the drop
down box.

What I do not understand is why the Multiple Field Index allows some
names to be added?!

Can you tell how to fix this.

As always, I am grateful for your help,

John

 

[Jerry Whittle]

Please show us examples of names that you don't think should be added along
with the names that should be blocking them.

 

[John W. Vinson]

Hi,

I created a Multiple Field Index containing the fields: First Name,
Middle Initial, Last Name to prevent duplicate names to be added
(prevent duplicate records to be added)

How will you deal with my friends Fred L. Brown, his son Fred L. Brown, and
the unrelated Fred L. Brown?

I only required the fields: First Name, Last Name as the Middle
Initial is rarely added to records.

Under Index Properties next to "Unique" I selected "Yes" on the drop
down box.

What I do not understand is why the Multiple Field Index allows some
names to be added?!

If you have "Ignore Nulls" set then records containing a NULL middle initial
will be... ignored, that is, not included in the index. As a result John <no
middle initial> Doe and John <no middle initial> Doe will not be seen as
duplicates.

I'd really recommend a different approach: use the Form's BeforeUpdate event
with DLookUp to see if there is an existing record with this name and WARN the
user, giving them an option to cancel or insert the record. Names simply are
not unique and you cannot reliably set up your database assuming that they
are.

 

[zufie]

How will you deal with my friends Fred L. Brown, his son Fred L. Brown, and
the unrelated Fred L. Brown?




If you have "Ignore Nulls" set then records containing a NULL middle initial
will be... ignored, that is, not included in theindex. As a result John <no
middle initial> Doe and John <no middle initial> Doe will not be seen as
duplicates.

I'd really recommend a different approach: use the Form's BeforeUpdate event
with DLookUp to see if there is an existing record with this name and WARN the
user, giving them an option to cancel or insert the record. Names simply are
not unique and you cannot reliably set up your database assuming that they
are.

I am such a dork!

I figured it out.

My multiple field index works fine.

If the name contained in the table has a space before the first or
last name then the name has to be entered on the form in the exact
manner.

That is with a space before the first or last name.

For example, if Joe appears in the table as (space)"_ Joe" and the
user tries to add the name exactly as it appears in the table (in this
case with a blank space before name Joe then the multiple field index
will prevent the name from being added.

If the name is entered without a space before the name "Joe" then the
multiple field index will allow the name to be added as it is
different from the name that appears in the table.

I hope I explained that correctly.

Thanks,

John

 

[John W. Vinson]

If the name contained in the table has a space before the first or
last name then the name has to be entered on the form in the exact
manner.

Well... yes. Computers are of course very literal minded!

If your users routinely do this, you might want to consider using the
AfterUpdate event of the textboxes to replace the user's entry into
txtLastName with Trim(Me![txtLastName]), for example.

I'm still concerned that you haven't addressed the very real issue that a
unique Index on first, middle, last name *simply does not reflect reality*. I
once worked with Dr. Lawrence David Wise and his colleague Dr. Lawrence David
Wise. Larry was a tall blond affable chemist, L. David a stocky dark taciturn
biologist - but they got paychecks from the same company.

You are MAKING A MISTAKE. Don't fix the way you're creating this unique index
- *don't do it*!