Prevent duplicate first & last name entry

[lkwander]

Have read through all the threads related to this topic and can't find anything to help. Just want a message box to pop up after entry of first and last names in a form if the string already exists in the database. DLookup works wonderfully for validating on one field, but adding the second field is driving me crazy - Can I use DLookup to check more than one field? The following code gives me the error: "Run time error '2001' - You canceled the previous operation."

Private Sub LastName_AfterUpdate()

Dim x As Variant

x = DLookup("[txtLastName]", "[tblVolunteerInfo]", "[txtLast Name]= '" & Me![LastName] & "'" & " And " & "[txtFirstName] = " & Me![FirstName])

On Error GoTo CustID_Err

If Not IsNull(x) Then
Beep
MsgBox "This name already exists in the database. Please check that you are not entering a duplicate person before continuing.", vbOKOnly, "Duplicate Value"
Cancel = True
End If

CustID_Exit:
Exit Sub

CustID_Err:
MsgBox Error$
Resume CustID_Exit

End Sub

thanks,

lkwander

 

[pbaldy]

You can test multiple criteria. You surrounded last name with single quotes, as required since it's a text field, but then didn't do it for first name.

 

[RV]

x = DLookup("[txtLastName]", "[tblVolunteerInfo]", "[txtLast Name]= '" & Me![LastName] & "' And [txtFirstName] = '" & Me![FirstName] & "'")

Use Dcount as you don't need to get the value of a particular field from a specified set of records:

If DCount("*", "[tblVolunteerInfo]", "[txtLast Name]= '" & Me![LastName] & "' And [txtFirstName] = '" & Me![FirstName] & "'") > ) Then
Beep
MsgBox "This name already exists in the database. Please check that you are not entering a duplicate person before continuing.", vbOKOnly, "Duplicate Value"
Cancel = True

RV

 

[lkwander]

perfect!

hi RV,

thanks - the second one worked great!! i'll note the difference between using DCount and DLookup.

(the first bit of code still gave me that same error message "Run time '2001' - You canceled the previous operation.")

lkwander

 

[lkwander]

Weird little error

So now, I'm using this code and it is working great - EXCEPT when I enter a first or last name which contains a ' (ie, O'Tool, O'Malley, O'Hern)...anyone got any ideas for me on how to make this not happen?

Private Sub Last_Name_AfterUpdate()

'Check for duplicate first and last name using DCount

If DCount("*", "[Constituents]", "[Last Name]= '" & Me![Last Name] & "' And [First Name] = '" & Me![First Name] & "'") > 0 Then
Beep
MsgBox "This first and last name already exists in the database. Please check that you are not entering a duplicate constituent before continuing.", vbOKOnly, "Duplicate Value"
Cancel = True
End If

CustID_Exit:
Exit Sub

CustID_Err:
MsgBox Error$
Resume CustID_Exit

End Sub

 

[Banana]

I have my code loaded in BeforeUpdate event, to check if there are previous clients- there should be only one Advocacy record per client so if client returns and this needs to be updated, the existing record should be called up and edited, creating new records only if there are no previous records.

My code is:

If DCount("*", "[Advocacy]", "[LastName]= '" & Me![FirstName] & "' And [FirstName] = '" & Me![FirstName] & "'") > 0 Then
Set DLookup(AdvocacyID, "Advocacy", [me.lastname=lastname]) = AdvocacyID.DefaultValue
Refresh
Else
DoCmd.GoToRecord (acDataForm = "lastname"), "[advocacy]", acNewRec

However, this still creates duplicates record. I had the same problem with the original code I started out with;

If IsNull(DLookup("[lastname]", "[advocacy]", "[lastname]='" & Me.LastName & "'")) Then
Let Me.LastName = strlastname
DoCmd.GoToRecord , [Advocacy], acNewRec
Let Me.LastName.DefaultValue = strlastname
Me.Refresh
Exit Sub
DoCmd.OpenForm "advocacy", , , , acFormAdd, , Me.LastName
Let Forms![Advocacy]!ClientID.DefaultValue = Me.ClientID
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit, , Me.LastName
End If

What am I doing wrong? Thanks

 

[RuralGuy]

If DCount("*", "[Constituents]", "[Last Name]= " & Chr(34) & Me![Last Name] & Chr(34) & " And [First Name] = " & Chr(34) & Me![First Name] & Chr(34)) > 0 Then

 

[Banana]

RG, thanks for your help;

However, this still created duplicate entries plus other errors.

Here's a sample to help things along...

 

[RuralGuy]

You should have zipped up the BackEnd with the FrontEnd.

 

[Banana]

oops-

here's the back end.

 

[RuralGuy]

Here's your two db's back. I made a few changes. I am going to *strongly* recommend you comment out most of your code and put it back in one process at a time, testing as you go. There are a number of things wrong with the code that testing will discover. It looks like you might have a background in VB. VBA is a little different. I gave you some hints in the code I changed and testing little pieces of code for functionality will give you the rest. Have fun!

 

[Banana]

Actually, I grew up programming BASIC, but never really touched VB.

Nonetheless, thanks so much for the help. I'll see what I can do.