sql - Delete record from table in Access -

i working 2 tables mitigations , new control ids in access database. mitigations table has form. part of form, control id can added new control ids table. code works fine. trying add ability delete record new control ids table via mitigations form.

the user enter string in text55 , after updating field, corresponding record in new control ids table should deleted.

here code have that:

private sub text55_afterupdate()  'removing record archer control id new control ids table dim dbnewinitiatives dao.database dim rstmitigations dao.recordset dim strsql string      set dbnewinitiatives = currentdb     strsql = "select * [new control ids] ([mitigation id] = " & me.[mitigation id].value & ") , ([archer control id] = '" & me.[text55].value & "') order [id]"     set rstmitigations = dbnewinitiatives.openrecordset(strsql, dbopendynaset)      while not rstmitigations.eof         rstmitigations.delete         rstmitigations.movenext     loop  rstmitigations.close  set rstmitigations = nothing set dbnewinitiatives = nothing  end sub 

the above code finds of records in new control ids table meet criteria , deletes them. thank you!

consider running delete action query using database.execute method without need of dao recordset:

set dbnewinitiatives = currentdb strsql = "delete [new control ids]" _            & " ([mitigation id] = " & me.[mitigation id].value & ")" _            & " , ([control id] = '" & me.[text55].value & "')"  dbnewinitiatives.execute strsql, dbfailonerror