Duplicate data based on multiple fields

  • Thread starter Thread starter SA2002
  • Start date Start date
S

SA2002

I have one table that has the below three fields:
Email 1
Email 2
Email 3

How do I prevent the same email address being entered into ANY of all the
three fields?

For example, when I enter (e-mail address removed) into email 1 and email 2 and email
3, the table/form accepts it and I want it to reject it.
 
You did not say where or how you are entering data into the fields. If you
are entering the data directly to a table, don't.

If you are entering the data in a form (as you should be), you can use the
Before Update event of each of the controls to see if one of the other
controls already contains the value:

Private Sub txtEmail1_Before Update(Cancel As Integer)
Dim strEmailNbr as String

With Me
If .txtEmail2 = .txtEmail1 Then
MsgBox "Duplicate Address found in Email 2"
.txtEmail2.Undo
Cancel = True
ElseIf .txtEmail3 = .txtEmail1 Then
MsgBox "Duplicate Address found in Email 3"
.txtEmail3.Undo
Cancel = True
End If
End Sub

Just change the names for the other two controls.
 
When I add the below code, I receive a compile error (Method or Data member
not found)

Private Sub Email1_BeforeUpdate(Cancel As Integer)

Dim strEmailNbr As String

With Me
If .txtEmail2 = .txtEmail1 Then
MsgBox "Duplicate Address found in Email 2"
.txtEmail2.Undo
Cancel = True
ElseIf .txtEmail3 = .txtEmail1 Then
MsgBox "Duplicate Address found in Email 3"
.txtEmail3.Undo
Cancel = True
End If
End Sub


Any suggestions on how to fix?
 
The example needs to have 3 textboxes named: txtEmail1, txtEmail2 and
txtEmail3. Even you have fields named like this, you should check the name
of the controls.

This is how will do it:


Private Sub txtEmail1_BeforeUpdate(Cancel As Integer)
If Duplicated Then DoCmd.CancelEvent ' Put this code on each
Email textbox (txtEmail1, txtEmail2, txtEmail3)
End Sub

Private Function Duplicated() As Boolean
Dim EM1, EM2, EM3 As String

EM1 = Nz(txtEmail1, "NoEmail1")
EM2 = Nz(txtEmail2, "NoEmail2")
EM3 = Nz(txtEMail3, "NoEmail3")

If EM1 = EM2 Or EM2 = EM3 Or EM3 = EM1 Then
Duplicated = True
Else
Duplicated = False
End If
End Function

Remember that you need 3 text boxes named txtEmail1, txtEmail2 and txtEmail3
to make it works.
 
The problem arises from the fact that you have a flawed design by virtue of
the multiple columns for email addresses. A correct design would be to have
the emails as separate rows in a related table. Say the current table is
called Contacts and has a primary key column ContactID, you should have a
separate table, Emails say, with columns ContactID and Email. In this table
ContactID is a foreign key referencing the primary key of Contacts and the
primary key of Emails is a composite one made up of the ContactID and Email
field. The insertion of a duplicate email address for the same ContactID
would not be possible as this would violate the primary key, whose values
must be unique.

For data entry a Contacts form with an Emails subform would be the usual
arrangement. You can then enter as few or as many emails per contact as
necessary.

If you already have a large number of rows with email addresses in the three
columns it’s a simple task to fill the new Emails table with three 'append'
queries. This would also weed out any duplicate addresses per contact as the
surplus ones would simply be rejected by the 'append' query, inserting only
one of each into the new table. The three email columns in the current table
could then be deleted.

Ken Sheridan
Stafford, England
 
When I applied the code, I received the error message:

Ambigious Name Detected: Duplicated

Any idea on how to fix?
 
I created a blank database to use as practice with just 3 text boxes. Here
is the complete code used:


Private Sub txtEmail1_BeforeUpdate(Cancel As Integer)
If Duplicated Then DoCmd.CancelEvent ' Put this code on each
Email TextBox(txtEmail1, txtEmail2, txtEmail3)
End Sub

Private Function Duplicated() As Boolean
Dim EM1, EM2, EM3 As String

EM1 = Nz(txtEmail1, "NoEmail1")
EM2 = Nz(txtEmail2, "NoEmail2")
EM3 = Nz(txtEmail3, "NoEmail3")

If EM1 = EM2 Or EM2 = EM3 Or EM3 = EM1 Then
Duplicated = True
Else
Duplicated = False
End If
End Function

Private Sub txtEmail2_BeforeUpdate(Cancel As Integer)

If Duplicated Then DoCmd.CancelEvent ' Put this code on each
Email TextBox(txtEmail1, txtEmail2, txtEmail3)
End Sub

Private Function Duplicated() As Boolean
Dim EM1, EM2, EM3 As String

EM1 = Nz(txtEmail1, "NoEmail1")
EM2 = Nz(txtEmail2, "NoEmail2")
EM3 = Nz(txtEmail3, "NoEmail3")

If EM1 = EM2 Or EM2 = EM3 Or EM3 = EM1 Then
Duplicated = True
Else
Duplicated = False
End If
End Function

Private Sub txtEmail3_BeforeUpdate(Cancel As Integer)

If Duplicated Then DoCmd.CancelEvent ' Put this code on each
Email TextBox(txtEmail1, txtEmail2, txtEmail3)
End Sub

Private Function Duplicated() As Boolean
Dim EM1, EM2, EM3 As String

EM1 = Nz(txtEmail1, "NoEmail1")
EM2 = Nz(txtEmail2, "NoEmail2")
EM3 = Nz(txtEmail3, "NoEmail3")

If EM1 = EM2 Or EM2 = EM3 Or EM3 = EM1 Then
Duplicated = True
Else
Duplicated = False
End If
End Function
 
The Duplicated function should only be defined once.

Private Sub txtEmail1_BeforeUpdate(Cancel As Integer)
If Duplicated Then DoCmd.CancelEvent ' Put this code on each
Email TextBox(txtEmail1, txtEmail2, txtEmail3)
End Sub

Private Sub txtEmail2_BeforeUpdate(Cancel As Integer)

If Duplicated Then DoCmd.CancelEvent ' Put this code on each
Email TextBox(txtEmail1, txtEmail2, txtEmail3)
End Sub

Private Sub txtEmail3_BeforeUpdate(Cancel As Integer)

If Duplicated Then DoCmd.CancelEvent ' Put this code on each
Email TextBox(txtEmail1, txtEmail2, txtEmail3)
End Sub

Private Function Duplicated() As Boolean
Dim EM1, EM2, EM3 As String

EM1 = Nz(txtEmail1, "NoEmail1")
EM2 = Nz(txtEmail2, "NoEmail2")
EM3 = Nz(txtEmail3, "NoEmail3")

If EM1 = EM2 Or EM2 = EM3 Or EM3 = EM1 Then
Duplicated = True
Else
Duplicated = False
End If
End Function
 
Thank you Douglas, that fixed the problem! Is there a way to add a message
box that pops up that says: That entry will create a duplicate record.

As the code stands now, it just won't allow the person to move to the next
record...?
 
Sorry, I didn't look closely enough at the code in the BeforeUpdate events.
It should be

Private Sub txtEmail1_BeforeUpdate(Cancel As Integer)

If Duplicated Then
MsgBox "That's a duplicate entry"
Cancel = True
End If

End Sub

Private Sub txtEmail2_BeforeUpdate(Cancel As Integer)

If Duplicated Then
MsgBox "That's a duplicate entry"
Cancel = True
End If

End Sub

Private Sub txtEmail3_BeforeUpdate(Cancel As Integer)

If Duplicated Then
MsgBox "That's a duplicate entry"
Cancel = True
End If

End Sub


You'll note I left the Email line out of there because I had no idea what it
does. You'll also need to change the Duplicated function. As it stands, it's
going to complain if you don't put different email addresses in all three
text boxes.

Private Function Duplicated() As Boolean
Dim EM1 As String, EM2 As String, EM3 As String

EM1 = Nz(txtEmail1, vbNullString)
EM2 = Nz(txtEmail2, vbNullString)
EM3 = Nz(txtEmail3, vbNullString)

If (EM1 = EM2 And EM2 <> vbNullString) Or _
(EM2 = EM3 And EM3 <> vbNullString) Or _
(EM3 = EM1 And EM3 <> vbNullString) Then
Duplicated = True
Else
Duplicated = False
End If

End Function
 
Back
Top