title: Allow data to delete from one table to add to another table
body: <p>I have posted about this table and form multiple times and I know I shouldn't do it this way. I am having trouble with my code. I need for when the ToGo hits 0 then the row on the WorkOrders table moves to the Done WorkOrders table and then deletes out of the WorkOrders table. The code I had before was</p>
  <pre><code>Private Sub ItemCode_Dirty(Cancel As Integer)
  Dim DB As DAO.Database
  Dim rs As DAO.Recordset
  
  Set DB = CurrentDb
  
  Set rs = DB.OpenRecordset(&quot;Select ToGo from WorkOrders where ItemCode =&quot; &amp; Me.ItemCode)
  
  With rs
      .Edit
      !ToGo = !ToGo - 1
      .Update
      .Edit
      
  End With
  
  End Sub
  </code></pre>
  <p>This was just making my code decrease by 1 every scan. So, I added the rest of the code to do the switch to a different table and then delete, but now my ToGo won't decrease at all. This is the updated code I have now with the if statement.</p>
  <pre><code>Private Sub ItemCode_Dirty(Cancel As Integer)
  Dim DB As DAO.Database
  Dim rs As DAO.Recordset
  Dim i As Integer
  Dim rsOld As DAO.Recordset
  Dim rsNew As DAO.Recordset
  
  Set DB = CurrentDb
  
  Set rs = DB.OpenRecordset(&quot;Select ToGo from WorkOrders where ItemCode =&quot; &amp; Me.ItemCode)
  Set rsNew = CurrentDb.OpenRecordset(&quot;SELECT * FROM WorkOrders&quot;)
  Set rsOld = CurrentDb.OpenRecordset(&quot;Select * from WorkOrders where ItemCode =&quot; &amp; Me.ItemCode)
  
  If ToGo &gt; 0 Then
      With rs
          .Edit
          !ToGo = !ToGo - 1
          .Update
          .Edit
      
      End With
  Else
      rsNew.AddNew
      For i = 0 To rsOld.Fields.Count - 1
      rsNew.Fields(i).Value = rsOld.Fields(i).Value
      Next
      rsNew.Update
      DoCmd.SetWarnings False
      DoCmd.RunSQL &quot;DELETE * FROM WorkOrders WHERE ItemCode= &amp; Me.ItemCode
      DoCmd.SetWarnings True
  
      rsNew.Close
      rsOld.Close
      Set rsNew = Nothing
      Set rsOld = Nothing
  End If
  
  End Sub
  </code></pre>
  <p>Can someone let me know if this is even possible.</p>