Results 1 to 7 of 7
  1. #1
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237

    find if duplicate record exists

    Hi,
    I am using a for to input new data and edit existing data.
    I want to avoid entering duplicates. What code should be put in the Before_Update event that does the following:


    1- check if a duplicate record exists
    2- Cancel the current information already entered in the form (not saving it).
    3- go to that existing record.

    Khalil

  2. #2
    A S MANN is offline Advanced System Analyst
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    India
    Posts
    161
    1; Use Find Duplicate Query Wizard to find Duplicate records existing . Run it in before Update events. Use Dcount ("DuplicateFieldName", " DuplicateQueryName" ) >1 to check
    2:If Dcount ("DuplicateFieldName", " DuplicateQueryName" ) >1 Then
    DoCmd.GoToRecord , , acNext
    Else
    End If
    Use DoCmd.GoToRecord , , acNext to go to Next Records

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    What determines when a record is a duplicate? Is it one field or a combination of fields?
    One quick method is to set a unique INDEX on that field or combination of fields. then, in the error handler, search for the record, move to that record and cancel the update.

    Another method:
    In the form Before Update event:
    Use a record set to determine if the record is unique (not a duplicate). If it is, grab the PK value, cancel the update, then move to the existing record using the PK value. (this is similar to the above method, but doesn't use an unique index and doesn't use the error handler to run the code.)

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by ssanfu View Post
    Use a record set to determine if the record is unique (not a duplicate). If it is, grab the PK value, cancel the update, ...

  5. #5
    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
    Once again...What determines when a record is a duplicate?

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    dashiellx's Avatar
    dashiellx is offline Falconer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Baltimore
    Posts
    49

  7. #7
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Why not just set up an Index on the field in the table and set it to no duplicates? Works every time for me.

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

Similar Threads

  1. Replies: 8
    Last Post: 12-16-2018, 06:45 AM
  2. Find Duplicate Record Issue....U
    By autiger58 in forum Access
    Replies: 2
    Last Post: 06-20-2017, 10:50 AM
  3. Replies: 2
    Last Post: 10-27-2015, 09:12 AM
  4. Replies: 3
    Last Post: 09-01-2015, 04:36 AM
  5. Goto Record when Duplicate Record Exists
    By rlsublime in forum Programming
    Replies: 13
    Last Post: 03-22-2012, 03:46 PM

Tags for this Thread

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