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_AfterUpdate()
On Error GoTo Err_txtEmailName_AfterUpdate

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

txtEmailName.Text) > 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 = ""
txtspecifyContacttype.Value = ""
CbxSalutation.Value = ""
txtFirstName.Value = ""
txtLastname.Value = ""
cbxCompanyName.Value = ""
cbxPosition.Value = ""
txtMobilePhone.Value = ""
txtEmailName.Value = ""
txtWorkphone.Value = ""
txtWorkext.Value = ""
txtFaxNumber.Value = ""
End If
Exit_Err_txtEmailName_AfterUpdate:
Exit Sub
Err_txtEmailName_AfterUpdate:
If Err.Number <> 2001 Then
MsgBox Err.Description
Resume Exit_Err_txtEmailName_AfterUpdate
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("EmailName", "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("*","Contacts","EmailName='" & Me!txtEmailName & "'") > 0 Then

...

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

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

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

iQA/AwUBQJLswYechKqOuFEgEQLpAwCeJTuCAXcbpapACSOyR1R6M9 pYwlEAn1Pq
7gs0EHzmvw0FXp1WxihQscdR
=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_AfterUpdate()
On Error GoTo Err_txtEmailName_AfterUpdate

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

txtEmailName.Text) > 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 = ""
txtspecifyContacttype.Value = ""
CbxSalutation.Value = ""
txtFirstName.Value = ""
txtLastname.Value = ""
cbxCompanyName.Value = ""
cbxPosition.Value = ""
txtMobilePhone.Value = ""
txtEmailName.Value = ""
txtWorkphone.Value = ""
txtWorkext.Value = ""
txtFaxNumber.Value = ""
End If
Exit_Err_txtEmailName_AfterUpdate:
Exit Sub
Err_txtEmailName_AfterUpdate:
If Err.Number <> 2001 Then
MsgBox Err.Description
Resume Exit_Err_txtEmailName_AfterUpdate
Else
Resume Next
End If
End Sub

[Bob Quintal]

ColinWard <je*********@hotmail.com> wrote in
news:40*********************@news.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_AfterUpdate()
On Error GoTo Err_txtEmailName_AfterUpdate

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

txtEmailName.Text) > 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 = ""
txtspecifyContacttype.Value = ""
CbxSalutation.Value = ""
txtFirstName.Value = ""
txtLastname.Value = ""
cbxCompanyName.Value = ""
cbxPosition.Value = ""
txtMobilePhone.Value = ""
txtEmailName.Value = ""
txtWorkphone.Value = ""
txtWorkext.Value = ""
txtFaxNumber.Value = ""
End If
Exit_Err_txtEmailName_AfterUpdate:
Exit Sub
Err_txtEmailName_AfterUpdate:
If Err.Number <> 2001 Then
MsgBox Err.Description
Resume Exit_Err_txtEmailName_AfterUpdate
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!