473,320 Members | 1,814 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Preventing duplicate contact entries


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
3 2141
-----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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: CJM | last post by:
How do people go about preventing the user from submitting a form for a 2nd time? For example, the user submits a form, clicks on the back button, and the submits the form again. I have used...
3
by: andreas.maurer1971 | last post by:
Hi all, since a few years I use the following statement to find duplicate entries in a table: SELECT t1.id, t2.id,... FROM table AS t1 INNER JOIN table AS t2 ON t1.field = t2.field WHERE...
18
by: Elroyskimms | last post by:
I have a table using an identity column as its Primary Key and two columns (table reduced for simplicity) EmployeeNumber and ArrivalTime. CREATE TABLE ( IDENTITY (1, 1) NOT NULL , (10)...
9
by: Catherine Jo Morgan | last post by:
Can I set it up so that a certain combination of fields can't contain the same entries, on another record? e.g. a combination of FirstName/LastName/address? Or FirstName/LastName/phone? Or...
3
by: ColinWard | last post by:
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...
2
by: Allen Anderson | last post by:
Hi, I'm trying to design contact (names and addresses) tables in an Access database. Some of the contacts represent vendors, some are board members of the organization, some are donors, some...
5
by: Manish | last post by:
The topic is related to MySQL database. Suppose a table "address" contains the following records ------------------------------------------------------- | name | address | phone |...
3
by: humblemally | last post by:
Goodmorning all - I created a form where you enter employee skillsets. There are about 15 different skills and the employees choose the skills they have from the list. I have created a field...
9
by: rjshrader | last post by:
I have a table (tblStatus) with three fields (CustomerID, StatusType and StatusDate). I use an unbound form with three text boxes to enter data into the table when a command button (cmdSave) is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.