title: Delete record from table in Access
body: <p>I am working with two tables <em>Mitigations</em> and <em>New Control IDs</em> in an Access database. The <em>Mitigations</em> table has a form. As part of that form, a control <em>ID</em> can be added to the <em>New Control IDs</em> table. My code for that works fine. I am trying to add the ability to delete a record from the <em>New Control IDs</em> table via the <em>Mitigations</em> form.</p>
  
  <p>The user will enter a string in <em>Text55</em> and after updating that field, the corresponding record in the <em>New Control IDs</em> table should be deleted.</p>
  
  <p>Here is the code I have for that:</p>
  
  <pre class="lang-vb prettyprint-override"><code>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] = " &amp; Me.[Mitigation ID].Value &amp; ") AND ([Archer Control ID] = '" &amp; Me.[Text55].Value &amp; "') 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
  </code></pre>
  
  <p>The above code successfully finds all of the records in <em>New Control IDs</em> table that meet the criteria and deletes them. Thank you!</p>