Trying to stop duplicate records

[Fred Boer]

Hello:

I have a table for library patrons. I want to prevent duplicate entries. I
have three fields: PatronFirstName, PatronMiddleName, PatronLastName. In the
past, I've used a DCount with the First and Last names. I've been doing some
revisions. I'd like to be able to include the Middle name. I was having
trouble writing a DCount expression that worked. I thought *bingo*! Why not
just create an index on the three fields, set it to "No Duplicates". But I
can't seem to make that work either.

If all three fields have the same values, the index prevents the record from
saving and throws an error. If the middle name is Null, the index does not
prevent the addition of the duplicate record. Is there a way to use a
compound index to do this?

Thanks!
Fred

P.S. And I thought I was *so* clever to think of the unique compound index
rather than my convoluted code..

 

[Fred Boer]

Ok, well, I've done some more research and I understand why the unique index
cannot work with nulls. So, I guess it is back to the Dcount code. If you
have any ideas on this, please feel free to make a suggestion! However, you
can disregard the original post - the answer to my original question is
clear to me.

Cheers!
Fred

 

[Fred Boer]

Hello again....

Actually, never mind... I've decided that I want to take a different
approach. I mean, you can have people with all three names the same! So, to
find a true duplicate you'd have to include address, etc., and it just gets
way too complicated, right? So I'll just do a simple check on first and last
names (perhaps forcing data entry into each of these fields) and then just
pop up a polite message suggesting the possibility that this might be a
duplicate record, and let the user take whatever action they like....

Until next time!

Fred

 

[Marshall Barton]

Hello again....

Actually, never mind... I've decided that I want to take a different
approach. I mean, you can have people with all three names the same! So, to
find a true duplicate you'd have to include address, etc., and it just gets
way too complicated, right? So I'll just do a simple check on first and last
names (perhaps forcing data entry into each of these fields) and then just
pop up a polite message suggesting the possibility that this might be a
duplicate record, and let the user take whatever action they like....

Until next time!

Fred

Talking to yourself again, Fred? Maybe it's time for a
break ;-)

 

[Fred Boer]

It's so enjoyable to talk with someone so incredibly intelligent and
attractive! I could do it for hours! <g>

Fred

P.S. A break was definitely in order!