If THE record insert into 2nd table is successful ... (1 Viewer)
- Thread starter dcavaiani
- Start date
June7
AWF VIP
- Local time
- Today, 02:12
- Joined
- Mar 9, 2014
- Messages
- 5,470
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.
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.
Last edited:
Code:
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]GoTo nextset[/CODE]
- Local time
- Today, 03:12
- Joined
- Aug 30, 2003
- Messages
- 36,125
I'd prefer to avoid the extra trips to the database. Guessing you already have a database variable for the recordset:
Code:
strSQL = "INSERT INTO..."
db.Execute strSQL, dbFailOnError
If db.RecordsAffected > 0 Then
'delete
End If
Last edited:
- Local time
- Today, 05:12
- Joined
- Feb 28, 2001
- Messages
- 27,179
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.
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've got your good things, and you've got mine.
- Local time
- Today, 06:12
- Joined
- May 21, 2018
- Messages
- 8,527
You can also use transactions.
Code:
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