troubleshooting Question

Compile Error: Runtime Error 3167 Record is deleted

Avatar of mato01
mato01Flag for United States of America asked on
Microsoft Access
6 Comments1 Solution909 ViewsLast Modified:
Help Experts

    This function was working, but now that I have placed it behind a button, it is giving me the error message. I had opened up another question, without resolve, so I'm asking this related question from previous communications on the code

Compile Error Runtime Error 3167 Record is deleted.

      If temp = rst![Constraint Number]  Then
Option Compare Database

Function DeleteDuplicates()

    Dim DeleteCount As Long ' Make this a Long if you really have that many records
    Dim temp
    Dim sql As String
    Dim sql2 As String
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim datMax As Date

    '' Change DESC to ASC to keep the older record
    '' Leave DESC to keep the newer record
    sql = "SELECT [tbl_GMNA Constraint Report Output].* FROM [tbl_GMNA Constraint Report Output] ORDER BY [tbl_GMNA Constraint Report Output].[Constraint Number] DESC"

    Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)

    If rst.EOF Then
        MsgBox "There are no records in this table!"
        Exit Function
    End If

    Do Until rst.EOF
      If temp = rst![Constraint Number] Then
        sql2 = "SELECT [tbl_GMNA Constraint Report Output].* FROM [tbl_GMNA Constraint Report Output]"
                  sql2 = sql2 & " WHERE [Constraint Number]='" & rst.Fields("Constraint Number")
                  sql2 = sql2 & "' ORDER BY [tbl_GMNA Constraint Report Output].[Constraint Number] DESC"
                  Set rst2 = CurrentDb.OpenRecordset(sql2, dbOpenDynaset)
                  datMax = rst2.Fields("Date Added").Value & vbNullString
                  'datMax = rst2.Fields("Date Added").Value & vbNullString

            If rst2.RecordCount > 1 Then
            sql2 = "Delete * From [tbl_GMNA Constraint Report Output] WHERE [Constraint Number]=" & Chr(34) & rst("Constraint Number") & Chr(34)
            sql2 = sql2 & " AND [Date Added] <> #" & datMax & "#"
            CurrentDb.Execute sql2
            End If
            DeleteCount = DeleteCount + 1
            temp = rst![Constraint Number]
        End If
    Set rst = Nothing
    Set rst2 = Nothing
    MsgBox "Found and deleted " & CStr(DeleteCount) & " records  - Constraint Process Completed."

End Function
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros