Prevent duplicate emails

[jimmypur]

Hi

I have a SQL database linked to an Access front end which has (amongst others) the following fields:

ID
LastName
FirstName
zipCode
emailaddress

ID is the primary key and indexed (no duplicates)

The others are varchar text fields.

I would like to do two things

1. Prevent a duplicate email address being entered in the emailaddress field and if the entry then passes that test to

2. Prevent a contact being added if they have the same firstname, lastname and zipcode as somebody (probably them) already in the database.

I can not do 1. by making the emailaddress field indexed no duplicates because there are nulls in the emailaddress field which SQL or access treates as a duplicate.

So it needs a trigger I think. Can any body give me the code or point me in the write direction?

Thanks

pj

 

[THWatson]

pj
1. On the AfterUpdate event for the email field, put DLookup code that checks for an already existing email address that is the same.

2. The same principle applies as in the answer for #1. Here is some code I have from an input form. You should be able to change the table and field names and tweak it to fit your program. The code would go on the AfterUpdate event for whichever is the last of the fields to be entered (if FirstName then LastName, put the code on the LastName...if the other way around, put the code on the FirstName). In this code, I don't check for the zip code but you could add that if you need to.

Dim intAnswer As Integer
Dim txtTemp As Variant
Dim txtTemp2 As Variant
Dim txtTemp3 As Variant
txtTemp = DLookup("[CamperLast]", "tblCamper", "[LeftCamp] = False And [CamperLast] = '" & Forms!frmCampers!CamperLast & "'")
txtTemp2 = DLookup("[CamperFirst]", "tblCamper", "[CamperLast] = Forms!frmCampers!CamperLast And [CamperFirst]='" & Forms!frmCampers!Text50 & "'")
txtTemp3 = txtTemp2 + " " + txtTemp

If Not IsNull(txtTemp2) Then
GoTo FullNameCheck

ElseIf Not IsNull(txtTemp) Then
GoTo LastNameCheck

FullNameCheck:
MsgBox "You already have a Camper with the name " & [txtTemp3] & "." & Chr(13) & Chr(10) & _
    "If the Last Name you entered was correct... " & vbCrLf & "be sure to enter a First Name that is different from " & [txtTemp2] & "." & vbCrLf & vbCrLf & _
    "     Your current entry will be cancelled.", vbOKOnly, "Duplicate Names"
Me.Undo
Me.SiteNbr.SetFocus
Exit Sub

LastNameCheck:
intAnswer = MsgBox("You already have a Camper with the last name " & [txtTemp] & "." & Chr(13) & Chr(10) & "Is this a duplicate entry?", vbYesNo + vbQuestion, "Possible Duplicate")
Select Case intAnswer
    Case vbYes
    MsgBox "Thank you. Entry will be cancelled.", vbOKOnly, "Duplicate entry"
    Me.Undo
    Me.SiteNbr.SetFocus
    Case vbNo
    MsgBox "Thank you. Continue with your entry.", vbOKOnly, "Entry process continuing."
    End Select
End If

Hope that helps.

Tom