Results 1 to 14 of 14
  1. #1
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    183

    Preventing duplicate records

    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.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    183
    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

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    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:

    Code:
    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)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Missinglinq View Post
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    183
    Quote Originally Posted by pbaldy View Post
    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.

  7. #7
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    183
    Quote Originally Posted by Missinglinq View Post
    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:

    Code:
    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?

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I wonder what "doesn't work" means exactly.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    183
    Quote Originally Posted by pbaldy View Post
    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

  10. #10
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Can you show us the actual code that you tried
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  11. #11
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    183
    Quote Originally Posted by Bob Fitz View Post
    Can you show us the actual code that you tried
    Sure..

    Code:
    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

  12. #12
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    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.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  13. #13
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    To do that, try this

    Code:
    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)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  14. #14
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    183
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Preventing Duplicate Entries
    By FormerJarHead in forum Modules
    Replies: 12
    Last Post: 01-29-2015, 10:16 AM
  2. Preventing duplicate entries...
    By graccess in forum Forms
    Replies: 4
    Last Post: 01-26-2014, 09:29 PM
  3. Replies: 8
    Last Post: 09-16-2013, 01:12 PM
  4. Preventing duplicate record
    By wpryan in forum Forms
    Replies: 2
    Last Post: 03-30-2013, 09:43 AM
  5. preventing duplicate records
    By Alliana Gray in forum Access
    Replies: 6
    Last Post: 08-11-2011, 01:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums