IF THE ONE RECORD inserted into 2nd table is successful - the next operation should be to DELETE that record out of the "work or 1st or input" table. These are SQL Statements INSERT INTO followed by DELETE FROM
Suggest instead of 'moving' records, simply edit a field that indicates record is 'archived' or 'inactive' and apply filter to exclude from queries. Recommend a date/time field to record date of archive. If field is Null then record is active.
Must have edited my question after you read it. Might review again. If you eliminate 'moving' record, there is no need to determine if anything was successful.
DoCmd.SetWarnings False
countB = DCount("[Customer]", "Materials")
' IF the counts are off by one the insert was successful.
DoCmd.RunSQL "INSERT INTO Materials (Customer, Job, Purchasedate, Etc, Etc) "
countA = DCount("[Customer]", "Materials")
If countA > countB Then rstMaterialsHold.Delete
rstMaterialsHold.MoveNext ' Bring up next record on [CODE]
I'd FIRST go with June's suggestion. Don't use a second table. Just change the status of the record in the first table.
BUT if there are real reasons to do it another way, then I'd go with Paul's method. Build your SQL, use a DAO database variable, do the Execute method of the SQL, and test records affected.
Now a bit of direct advice: Doing an INSERT to table B followed by a DELETE from table A adds TWO database touches, which doubles the chances of problems if you happen to lose the connection between the two queries. Doing an UPDATE will reduce that to one touch and less of a window for errors to creep in.
Public Sub InsertDeleteWithTransaction()
DAO.DBEngine.BeginTrans
On Error GoTo tran_Err
'first query
CurrentDb.Execute "INSERT INTO ...", dbFailOnError
'second query
CurrentDb.Execute "DELETE * FROM .... ", dbFailOnError
DAO.DBEngine.CommitTrans
Exit Sub
tran_Err:
DAO.DBEngine.Rollback
MsgBox "Transaction failed. Error: " & Err.Description
End Sub