If THE record insert into 2nd table is successful ...

[dcavaiani]

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

 

[June7]

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.

What code are you using to INSERT record? If you need to verify the record saved, RunSQL will let you know. If you use CurrentDb.Execute, can use dbFailOnError option and error handler code. https://www.databasejournal.com/fea...36_2/Executing-SQL-Statements-in-VBA-Code.htm

In either case, DLookup is always available.

 

[pbaldy]

I agree with June7. If your situation really requires it, you can use RecordsAffected to test if the insert was successful, if so perform the delete.

 

[dcavaiani]

either way, i need first to know if insert was successful ... can't get that figured out ... in Access 2002

 

[June7]

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.

 

[dcavaiani]

RunSql - but messages suppressed. i may try dcount b4 and after

 

[plog]

DCount("[ID]", "YourTable")

INSERT INTO YourTable....

DCount("[ID]", "YourTable")

IF the counts are off by one the insert was successful.

 

[dcavaiani]

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]

SET
GoTo nextset[/CODE]

 

[pbaldy]

I'd prefer to avoid the extra trips to the database. Guessing you already have a database variable for the recordset:

strSQL = "INSERT INTO..."
db.Execute strSQL, dbFailOnError
If db.RecordsAffected > 0 Then
  'delete
End If

 

[The_Doc_Man]

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.

 

[MajP]

You can also use transactions.

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