Hi Al, Thanks for your response.
Let me give you a few scenarios which is what prompted this post. We are a
social service agency that provides assistance for people in need
particularly utility bills. The main client table currently holds about 3,000
client records that we've provided assistance to. The problem that is
happening more often is 1) Clients are getting married or divorced which is
causing a name change and 2) multiple people from the same household apply
for assistance which isnt allowed. When problem number #1 happens, a client
can come in and reapply for services under a new name and it goes unnoticed
(ex. 6/05 Jane Doe gets services, 7/06 Jane Doe is now Jane Smith Doe
applying for services. The 7/06 application says Jane Smith Doe so the intake
specialist looks to find Jane Smith Doe, doesnt find her and creates a new
record even though she was already there as Jane Doe).
I agree with what you're saying about making SSN an indexed field with no
duplicates but unfortunately people commit fraud on applications and often
goes unnoticed which is why a message on the screen showing a name duplicate
and address duplicate check message will at least prompt the intake
specialist to look into it further to make sure that person wasnt entered
already. The messages are not necessarily there to prevent a duplicate
because in some situations they are allowed which is why I havent gone to the
Indexed - No Duplicate solution. I have seen numerous posts on the DLookup
but none that fit my situation and Im a beginner at coding in Access so I'm
using it wrong.
One thing I did was created a "Find Duplicate Query" and then inserted it on
the form as a subform linked by SSN. I then created a text box that has the
following in the Control Source property field:
=IIf([SSN]=[Duplicates subform].[Form]![SSN],"The SSN Already Exists In The
Client Table","")
This works great if a duplicate actually exists however if no duplicate
exists, I get a #Error which I cant get rid of. I know the error is there
because no SSN is in the subform making it blank so if I can find a way to
hide that error, I would be happy. I tried using the IsNull but its not
working and the Nz command wont work because its a text field.
Al said:
scarlton,
I'd go with just the checking the SSN since that's a unique number. Duplicate Last
and
First names can exist, and Addresses are rarely entered exactly the same.
Ex. Robert Smith
123 South Main St or 123 S Main St or 123 So. Main or etc.. etc..
Using FName, or LName or Address will probably cause more false negatives and false
positives than it's worth as a duplicate finder.
You can make SSN an indexed no duplicates field, and after enetering any SSN, Refresh
the form, and you'll know right away that the SSN is a dupe.
Or..
Use the AfterUpdate event of SSN to do a Dlookup against all existing SSNs in your
table, and determine duplicate status that way.
I have a table called tbl-ClientIntake that has many fields including [SSN],
[LName], [FName], [Address], and several others. I would like to create a
field on my form that says "Possible Duplicate" if a user enters a new record
and the [SSN] already exists in the table. Additionally I would like 2 other
form fields to indicate that a client might be a duplicate if their First and
Last names exist and if there is a dupliate in the address field. Any Ideas?