Duplicate Record Fields

[scarlton via AccessMonster.com]

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?

 

[Al Campagna]

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.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[scarlton via AccessMonster.com]

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.

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?

 

[Al Campagna]

scarlton,
If I understand correctly...your general plan makes sense within that scenario.

Are you saying that the Duplicates subform will only display one matching SSN record...
assuming that any previous dupes were deleted from the table?

Because...

=IIf([SSN]=[Duplicates subform].[Form]![SSN],"The SSN Already Exists In The
Client Table","")

always returns just the "first" subform record (IF there is one). My first thought is
that because the sub is linked to the main on SSN that in a No Dupes situation there may
be nothing to interrogate at all. In other words. the sub SSN may not even exist. I'll
check on that further...

For now, try a Dlookup against the table. With 3000 records, that shouldn't be a
problem at all. You could hide the subform, and only show it if the Dlookup is Not Null

Sound like a plan?

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

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.

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?

 

[Al Campagna]

scarlton,
I just tried your code.
It works fine... UNLESS... you have Allow Adds = NO, them it fails with an #Error on a
"no hit".
Probably due to the empty recordset.
So... try the DLookup, which will "hit" or Null.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

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.

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?

 

[scarlton via AccessMonster.com]

You're right, I had to turn the Allow Adds to NO because it was basically
showing a SSN regardless of whether it was unique or not. I found a code that
does mostly what I want which is:

Private Sub SSN_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("SSN", "tbl_ClientIntake", "[SSN]=" & "'" & Me.SSN &
"'")) _
Then
MsgBox "Social Security # " & SSN & " Already Exists In The Client Table!"
' Cancel = True
Else: MsgBox "Social Security # Is Unique"
End If

End Sub

My next question is three-fold:
1) Can I modify the Else: MsgBox "Social Security # Is Unique" part of the
code. The message box shows up if its a dup but I dont need the box to come
up if its not. If its unique, the user will just move to the next field
without any messages popping up.
2) Can this code be added to a text box. Having the message box pop up is OK
for one of the forms but I want the message to appear in a text field on
another form. I dont need anything popping up.
3) If I can get #2 to work, how would I use it with multiple fields. The code
above works with just 1 field (SSN) but going back to the Name and Address
issues, each text box message will have two fields. One with (LName) and
(FName) and the other box would be (Address1) and (Address2)

Thanks again for you help. As I mentioned, coding is fairly new to me.

Steve

scarlton,
I just tried your code.
It works fine... UNLESS... you have Allow Adds = NO, them it fails with an #Error on a
"no hit".
Probably due to the empty recordset.
So... try the DLookup, which will "hit" or Null.

Hi Al, Thanks for your response.
Let me give you a few scenarios which is what prompted this post. We are a

[quoted text clipped - 55 lines]

 

[Al Campagna]

scarlton,
First, I'd do the synatx for your code like this...
If Not IsNull(DLookup("[SSN]", "tbl_ClientIntake", "[SSN] = '" & SSN & "'"))
showing spaces between quotes (for clarity only)
If Not IsNull(DLookup("[SSN]", "tbl_ClientIntake", "[SSN] = ' " & SSN & " ' "))
(email doesn't have inteelisense, so check my syntax, but that "should" do it)

Q2 first, which also answers Q1... TextControl with...

= IIF Not IsNull(DLookup("[SSN]", "tbl_ClientIntake", "[SSN] = '" & SSN & "'")),
"NotUnique", "Unique")

Q3... While it is possible, I would not recommend spending time trying to get that to
work.
Better to create separate textboxes for the SSS, Last/FirstName, Address, etc... and place
them right next to the field involved.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

You're right, I had to turn the Allow Adds to NO because it was basically
showing a SSN regardless of whether it was unique or not. I found a code that
does mostly what I want which is:

Private Sub SSN_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("SSN", "tbl_ClientIntake", "[SSN]=" & "'" & Me.SSN &
"'")) _
Then
MsgBox "Social Security # " & SSN & " Already Exists In The Client Table!"
' Cancel = True
Else: MsgBox "Social Security # Is Unique"
End If

End Sub

My next question is three-fold:
1) Can I modify the Else: MsgBox "Social Security # Is Unique" part of the
code. The message box shows up if its a dup but I dont need the box to come
up if its not. If its unique, the user will just move to the next field
without any messages popping up.
2) Can this code be added to a text box. Having the message box pop up is OK
for one of the forms but I want the message to appear in a text field on
another form. I dont need anything popping up.
3) If I can get #2 to work, how would I use it with multiple fields. The code
above works with just 1 field (SSN) but going back to the Name and Address
issues, each text box message will have two fields. One with (LName) and
(FName) and the other box would be (Address1) and (Address2)

Thanks again for you help. As I mentioned, coding is fairly new to me.

Steve

scarlton,
I just tried your code.
It works fine... UNLESS... you have Allow Adds = NO, them it fails with an #Error on
a
"no hit".
Probably due to the empty recordset.
So... try the DLookup, which will "hit" or Null.

Hi Al, Thanks for your response.
Let me give you a few scenarios which is what prompted this post. We are a

[quoted text clipped - 55 lines]

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?

 

[scarlton via AccessMonster.com]

Hi Al, I'm entering the code below but I keep getting a error that says "The
expression you entered has a function containing the wrong number of
arguments" I think because there's 2 open parenthesis brackets but 3 closed
brackets. Also, am I to assume that this code gets entered as a control
source property for the text box or does it go in another property field.
Thanks again.

scarlton,
First, I'd do the synatx for your code like this...
If Not IsNull(DLookup("[SSN]", "tbl_ClientIntake", "[SSN] = '" & SSN & "'"))
showing spaces between quotes (for clarity only)
If Not IsNull(DLookup("[SSN]", "tbl_ClientIntake", "[SSN] = ' " & SSN & " ' "))
(email doesn't have inteelisense, so check my syntax, but that "should" do it)

Q2 first, which also answers Q1... TextControl with...

= IIF Not IsNull(DLookup("[SSN]", "tbl_ClientIntake", "[SSN] = '" & SSN & "'")),
"NotUnique", "Unique")

Q3... While it is possible, I would not recommend spending time trying to get that to
work.
Better to create separate textboxes for the SSS, Last/FirstName, Address, etc... and place
them right next to the field involved.

You're right, I had to turn the Allow Adds to NO because it was basically
showing a SSN regardless of whether it was unique or not. I found a code that

[quoted text clipped - 42 lines]

 

[scarlton via AccessMonster.com]

Hi Al, I got the message to work below by adding a ( after the =IIF but its
coming up NotUnique for everyone. I intentionly left a couple client records
with duplicate SSN's to test it out and regardless of whether you click on a
client with 1 or 2 SSN's entered into the tbl_ClientIntake table, the message
always says "NotUnique" Any ideas?

scarlton,
First, I'd do the synatx for your code like this...
If Not IsNull(DLookup("[SSN]", "tbl_ClientIntake", "[SSN] = '" & SSN & "'"))
showing spaces between quotes (for clarity only)
If Not IsNull(DLookup("[SSN]", "tbl_ClientIntake", "[SSN] = ' " & SSN & " ' "))
(email doesn't have inteelisense, so check my syntax, but that "should" do it)

Q2 first, which also answers Q1... TextControl with...

= IIF Not IsNull(DLookup("[SSN]", "tbl_ClientIntake", "[SSN] = '" & SSN & "'")),
"NotUnique", "Unique")

Q3... While it is possible, I would not recommend spending time trying to get that to
work.
Better to create separate textboxes for the SSS, Last/FirstName, Address, etc... and place
them right next to the field involved.

You're right, I had to turn the Allow Adds to NO because it was basically
showing a SSN regardless of whether it was unique or not. I found a code that

[quoted text clipped - 42 lines]

 

[Al Campagna]

scarlton,
Please don't "clip" any of the "relevant" information from previous threads. On a
long problem like this we'll need to see the whole sequence of our problem determination.

And... always include the code you just tried. Actually cut & paste your code into
your email reply.

Yes, there was a typo on my previous post. I had left the old IF portion at the
beginning of the IIF. As I wrote...
This new code should go into an unbound text control, in the ControlSource. (name it
ex. SSNStatus)
(Let's use the form SSN value by usuing the full address this time in the Where...
should avoid any "quote" mistakes.
And let's turn it around a bit for a more "direct" logic...)
=IIf(IsNull(DLookUp("[SSN]","tbl_ClientIntake","[SSN] =
Forms!frmYourFormName!SSN")),"Unique","Not Unique")

I tested just the "synatx"... it's OK.
I also tested the logic... I ran a test with a sample form I had, using my fields with
the same method, and it works.

I can only guess that there's something different bewteen what weve established here,
and what you have setup in reality. SSN must be the "name" of the text control that is
bound to your table's SSN field, and it must be on the Main form in this setup

Try this code, do your best to check out that each object called in the code is
correct, and if it still doesn't work, send me the .mdb file through my website via
"Contact" below.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Hi Al, I got the message to work below by adding a ( after the =IIF but its
coming up NotUnique for everyone. I intentionly left a couple client records
with duplicate SSN's to test it out and regardless of whether you click on a
client with 1 or 2 SSN's entered into the tbl_ClientIntake table, the message
always says "NotUnique" Any ideas?

scarlton,
First, I'd do the synatx for your code like this...
If Not IsNull(DLookup("[SSN]", "tbl_ClientIntake", "[SSN] = '" & SSN & "'"))
showing spaces between quotes (for clarity only)
If Not IsNull(DLookup("[SSN]", "tbl_ClientIntake", "[SSN] = ' " & SSN & " ' "))
(email doesn't have inteelisense, so check my syntax, but that "should" do it)

Q2 first, which also answers Q1... TextControl with...

= IIF Not IsNull(DLookup("[SSN]", "tbl_ClientIntake", "[SSN] = '" & SSN & "'")),
"NotUnique", "Unique")

Q3... While it is possible, I would not recommend spending time trying to get that to
work.
Better to create separate textboxes for the SSS, Last/FirstName, Address, etc... and
place
them right next to the field involved.

You're right, I had to turn the Allow Adds to NO because it was basically
showing a SSN regardless of whether it was unique or not. I found a code that

[quoted text clipped - 42 lines]

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?