Preventing duplicate records

[dniezby]

Okay, so I would like to check my table beforeUpdate to see if firstname, lastname, email are exactly the same as another entry. If so, I'll put up a "record already exists" MsgBox. I was just going to have a prevent duplicates for the email but then I realized that some people share emails. So I would have two (or more) using the same email and last name but firstname would be different.



I'm assuming I would have to code it in the BeforeUpdate event?

I know I will have to do an IF statement but how do I format it?

If Me.Firstname, Me.Lastname, Me.email = record already exists? then do something else saverecord.


Thanks AGAIN in advance.

[pbaldy]

This might help:

http://www.baldyweb.com/BeforeUpdate.htm

In your case, the test could use a DCount() with a criteria using your 3 fields:

If DCount(...) > 0 Then

More on the DCount() syntax:

DLookup Usage Samples

[dniezby]

Okay that just made me even more confused.

I do remember doing this a long time ago and Dlookup sounds REALLY familiar. It's just not coming back to me yet.


Sent from my iPhone using Tapatalk

[Missinglinq]

My mind is still boggling over multiple people using a single email address...but anyway...I've done this kind of thing before, in my early days, and while it's an unorthodox approach (I've never seen it done this way, anywhere else) it works just fine!

Replacing TableName, FirstName, LastName, and EmailAddress with the actual names of your Table, Fields and Textboxes:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("*", "TableName", "FirstName & LastName & EmailAddress = '" &   Me.FirstName & Me.LastName & Me.EmailAddress & "'") > 0 Then
 Cancel = True
 MsgBox "This Combination Already Exists!"
 Me.Undo
End If

End Sub

Linq ;0)>

[pbaldy]

My mind is still boggling over multiple people using a single email address

My wife and I use the same address for a lot of things. I've seen others do the same thing (JoeAndJane@Whatever.com). Many of our employees don't have their own addresses, they share one, like everybody in cab dispatch shares CabDispatch@OurCompany.com. Of course those aren't meant for personal use.

[dniezby]

My wife and I use the same address for a lot of things. I've seen others do the same thing (JoeAndJane@Whatever.com). Many of our employees don't have their own addresses, they share one, like everybody in cab dispatch shares CabDispatch@OurCompany.com. Of course those aren't meant for personal use.

Exactly! I see it too often.

[dniezby]

My mind is still boggling over multiple people using a single email address...but anyway...I've done this kind of thing before, in my early days, and while it's an unorthodox approach (I've never seen it done this way, anywhere else) it works just fine!

Replacing TableName, FirstName, LastName, and EmailAddress with the actual names of your Table, Fields and Textboxes:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("*", "TableName", "FirstName & LastName & EmailAddress = '" &   Me.FirstName & Me.LastName & Me.EmailAddress & "'") > 0 Then
 Cancel = True
 MsgBox "This Combination Already Exists!"
 Me.Undo
End If

End Sub

Linq ;0)>

Hmm, this doesn't work for me. I wonder what I did wrong?

[pbaldy]

I wonder what "doesn't work" means exactly.

[dniezby]

I wonder what "doesn't work" means exactly.

It doesn't do anything. A record just gets inserted as normal.


Sent from my iPhone using Tapatalk

[Bob Fitz]

Can you show us the actual code that you tried

[dniezby]

Can you show us the actual code that you tried

Sure..

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.Modified = Now()

If DCount("*", "Contact", "First & Last & Email = '" &   Me.First & Me.Last & Me.Email & "'") > 0 Then
 Cancel = True
 MsgBox "This Combination Already Exists!"
 Me.Undo
End If

End Sub

[Bob Fitz]

I've tested the code and it seems to work as it should.

Have you made sure that your code does actually run when it should.

[Missinglinq]

To do that, try this

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.Modified = Now()

Msgbox Now()

If DCount("*", "Contact", "First & Last & Email = '" &   Me.First & Me.Last & Me.Email & "'") > 0 Then
 Cancel = True
 MsgBox "This Combination Already Exists!"
 Me.Undo
End If

End Sub

After entering a duplicate record does a messagebox with date and time pop up?

If not, does any code run?

Linq ;0)>

[dniezby]

I figured a way to prevent duplicates. One of my required fields is unique to every entry. I just changed that field to required and no duplicates.
Thanks for all the help.