By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,482 Members | 3,157 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,482 IT Pros & Developers. It's quick & easy.

Preventing duplicate contact entries

P: n/a

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!
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
-----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


Nov 12 '05 #2

P: n/a
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!


Nov 12 '05 #3

P: n/a
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!
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.