Results 1 to 4 of 4
  1. #1
    jyellis is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    20

    Remove Duplicate VBA code not working

    Click image for larger version. 
<br /><script async src=
    Name: aaa.jpg  Views: 11  Size: 34.4 KB  ID: 32573" class="thumbnail" style="float:CONFIG" />


    I have used this code in another tables but it's not working on this table.

    It will not remove the duplicate

    Code:
    Sub Remove_Dup_2ATT()
    On Error Resume Next
      Dim db As DAO.Database, rst As DAO.Recordset
      Dim strDupName As String, strSaveName As String
      
      Set db = CurrentDb()
      Set rst = db.OpenRecordset("tbl_complete_total_extraBillingcodes_ATT")
     
     
      
      If rst.BOF And rst.EOF Then
        MsgBox "No records to process"
      Else
        rst.MoveFirst
        Do Until rst.EOF
          strDupName = rst.Fields(1)
          If strDupName = strSaveName Then
            rst.Delete
          Else
            strSaveName = rst.Fields(1)
          End If
          rst.MoveNext
        Loop
        
        Set rst = Nothing
        Set db = Nothing
        
        Call RestoreData
      End If
    End Sub
    Function RestoreData()
    On Error Resume Next
      Dim strMsg As String
      strMsg = "Duplicates Deleted!" & vbCrLf & vbCrLf
      strMsg = strMsg & "Click cancel to restore the original table?"
      
      If MsgBox(strMsg, vbOKCancel, "Finished") = vbCancel Then
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qryRefreshBP102_Table"
        DoCmd.SetWarnings True
      End If
    End Function
    Access 2016 Pro Plus
    Thank you in advance!

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    A quicker way is to do the following in a procedure
    It ma sound complicated but it will run much faster than looping through a recordset

    First create a query containing the duplicates then group it and for the PK field use First as the criteria.
    Save.
    Create and run a make table query based on that.
    Now join that table to your original table and delete all records from original table also in the one you created.
    Delete the table you created.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What happens - error message, nothing?

    Perhaps you should open the recordset sorted by those 5 fields.

    Set rst = db.OpenRecordset("SELECT * FROM tbl_complete_total_extraBillingcodes_ATT ORDER BY ...")

    Colin has an interesting idea, although if this will be regular process, I would not repeatedly create and delete table, instead insert and delete records to a 'temp table' - table is permanent records are temporary.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Colin has an interesting idea, although if this will be regular process, I would not repeatedly create and delete table, instead insert and delete records to a 'temp table' - table is permanent records are temporary.
    Good point - I'd agree with that to reduce risk of database bloat
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. remove duplicate
    By abraham30 in forum Access
    Replies: 5
    Last Post: 04-23-2015, 04:01 AM
  2. Remove Duplicate Entries in Query Design
    By samanthaM in forum Access
    Replies: 1
    Last Post: 10-27-2013, 11:26 AM
  3. Remove duplicate but keep the latest record
    By Compufreak in forum SQL Server
    Replies: 2
    Last Post: 06-12-2013, 11:55 AM
  4. Replies: 5
    Last Post: 05-24-2013, 11:55 AM
  5. Remove Duplicate
    By rgparpaccessforums in forum Access
    Replies: 3
    Last Post: 04-24-2011, 07:24 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