Preventing duplicate contact entries

[ColinWard]

I am using the following code to validate that the person that is being
entered into the database does not already exist. However wnem I test it
by entering myself as a contact(I first checked that I was indeed NOT in
the database), the message still comes up saying that I am in the
database. what am I doing wrong?

Private Sub txtEmailName_Af terUpdate()
On Error GoTo Err_txtEmailNam e_AfterUpdate

If DLookup("EmailN ame", "Contacts", EmailName = Me >>

txtEmailName.Te xt) > 0 Then
FormattedMsgBox "This person is already in the database. This duplicate
entry will not be added.@The information will now be cleared so that you
may enter new data.@", vbOKOnly + vbInformation, "Duplicate Entry"
cbxFollowedUpBy .Value = ""
cbxFollowUpType .Value = ""
txtspecifyConta cttype.Value = ""
CbxSalutation.V alue = ""
txtFirstName.Va lue = ""
txtLastname.Val ue = ""
cbxCompanyName. Value = ""
cbxPosition.Val ue = ""
txtMobilePhone. Value = ""
txtEmailName.Va lue = ""
txtWorkphone.Va lue = ""
txtWorkext.Valu e = ""
txtFaxNumber.Va lue = ""
End If
Exit_Err_txtEma ilName_AfterUpd ate:
Exit Sub
Err_txtEmailNam e_AfterUpdate:
If Err.Number <> 2001 Then
MsgBox Err.Description
Resume Exit_Err_txtEma ilName_AfterUpd ate
Else
Resume Next
End If
End Sub

thank you

Colin Ward

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The correct syntax for DLookup() is:

DLookup("EmailN ame", "Contacts", "EmailName= '" & Me!txtEmailName & "'")

You have to enclose criteria in double-quotes and concatenate it with
the value on the form. Since it is a text value you have to surround
the value in the TextBox w/ single-quotes.

But, it would be more correct to use the DCount() function to evaluate
if the return value is > 0:

If DCount("*","Con tacts","EmailNa me='" & Me!txtEmailName & "'") > 0 Then

...

Also, instead of individually clearing the values from all the controls,
you can use Me.Undo.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJLswYechKq OuFEgEQLpAwCeJT uCAXcbpapACSOyR 1R6M9pYwlEAn1Pq
7gs0EHzmvw0FXp1 WxihQscdR
=axnk
-----END PGP SIGNATURE-----
ColinWard wrote:

I am using the following code to validate that the person that is being
entered into the database does not already exist. However wnem I test it
by entering myself as a contact(I first checked that I was indeed NOT in
the database), the message still comes up saying that I am in the
database. what am I doing wrong?

Private Sub txtEmailName_Af terUpdate()
On Error GoTo Err_txtEmailNam e_AfterUpdate

If DLookup("EmailN ame", "Contacts", EmailName = Me >>

txtEmailName.Te xt) > 0 Then
FormattedMsgBox "This person is already in the database. This duplicate
entry will not be added.@The information will now be cleared so that you
may enter new data.@", vbOKOnly + vbInformation, "Duplicate Entry"
cbxFollowedUpBy .Value = ""
cbxFollowUpType .Value = ""
txtspecifyConta cttype.Value = ""
CbxSalutation.V alue = ""
txtFirstName.Va lue = ""
txtLastname.Val ue = ""
cbxCompanyName. Value = ""
cbxPosition.Val ue = ""
txtMobilePhone. Value = ""
txtEmailName.Va lue = ""
txtWorkphone.Va lue = ""
txtWorkext.Valu e = ""
txtFaxNumber.Va lue = ""
End If
Exit_Err_txtEma ilName_AfterUpd ate:
Exit Sub
Err_txtEmailNam e_AfterUpdate:
If Err.Number <> 2001 Then
MsgBox Err.Description
Resume Exit_Err_txtEma ilName_AfterUpd ate
Else
Resume Next
End If
End Sub

[Bob Quintal]

ColinWard <je*********@ho tmail.com> wrote in
news:40******** *************@n ews.frii.net:

I am using the following code to validate that the person that
is being entered into the database does not already exist.
However wnem I test it by entering myself as a contact(I first
checked that I was indeed NOT in the database), the message
still comes up saying that I am in the database. what am I
doing wrong?

Private Sub txtEmailName_Af terUpdate()
On Error GoTo Err_txtEmailNam e_AfterUpdate

If DLookup("EmailN ame", "Contacts", EmailName = Me >>

txtEmailName.Te xt) > 0 Then

Your DLookup criteria is missing quotation marks, it should be
"Emailname = '" & me.txtEmailName .value & "'"

Note also that the .text property isn't always available
afterupdate, use it beforeupdate.

Note also that me.undo will reverse all the entries that your user
has made in the current record, much easier to understand and to
maintain than all your .value = "" statements.

Also be aware that me.undo may save you problems with blank
records.

Bob Quintal

FormattedMsgBox "This person is already in the database. This
duplicate entry will not be added.@The information will now be
cleared so that you may enter new data.@", vbOKOnly +
vbInformation, "Duplicate Entry"
cbxFollowedUpBy .Value = ""
cbxFollowUpType .Value = ""
txtspecifyConta cttype.Value = ""
CbxSalutation.V alue = ""
txtFirstName.Va lue = ""
txtLastname.Val ue = ""
cbxCompanyName. Value = ""
cbxPosition.Val ue = ""
txtMobilePhone. Value = ""
txtEmailName.Va lue = ""
txtWorkphone.Va lue = ""
txtWorkext.Valu e = ""
txtFaxNumber.Va lue = ""
End If
Exit_Err_txtEma ilName_AfterUpd ate:
Exit Sub
Err_txtEmailNam e_AfterUpdate:
If Err.Number <> 2001 Then
MsgBox Err.Description
Resume Exit_Err_txtEma ilName_AfterUpd ate
Else
Resume Next
End If
End Sub

thank you

Colin Ward

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[ColinWard]

Thanks for your reply. I am currently at home but I will give it a try
monday morning

Colin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!