Delete record from table in Access

Question

I am working with two tables Mitigations and New Control IDs in an Access database. The Mitigations table has a form. As part of that form, a control ID can be added to the New Control IDs table. My code for that works fine. I am trying to add the ability to delete a record from the New Control IDs table via the Mitigations form.

The user will enter a string in Text55 and after updating that field, the corresponding record in the New Control IDs table should be deleted.

Here is the code I have for that:

Private Sub Text55_AfterUpdate()

'removing record with Archer Control ID from New Control IDs table
Dim dbNewInitiatives As DAO.Database
Dim rstMitigations As DAO.Recordset
Dim strSQL As String

    Set dbNewInitiatives = CurrentDb
    strSQL = "SELECT * FROM [New Control IDs] WHERE ([Mitigation ID] = " & Me.[Mitigation ID].Value & ") AND ([Archer Control ID] = '" & Me.[Text55].Value & "') ORDER BY [ID]"
    Set rstMitigations = dbNewInitiatives.OpenRecordset(strSQL, dbOpenDynaset)

    Do While Not rstMitigations.EOF
        rstMitigations.Delete
        rstMitigations.MoveNext
    Loop

rstMitigations.Close

Set rstMitigations = Nothing
Set dbNewInitiatives = Nothing

End Sub

The above code successfully finds all of the records in New Control IDs table that meet the criteria and deletes them. Thank you!

Answer 1

Consider running a DELETE action query using the Database.Execute method without the need of a DAO recordset:

Set dbNewInitiatives = CurrentDb
strSQL = "DELETE FROM [New Control IDs]" _
           & " WHERE ([Mitigation ID] = " & Me.[Mitigation ID].Value & ")" _
           & " AND ([Control ID] = '" & Me.[Text55].Value & "')"

dbNewInitiatives.Execute strSQL, dbFailOnError