I'm very new to programming and have been building my company's inventory database on MS Access 2016. I've been able to get by just fine with macros so far, but I'm trying to run a transaction made of append and delete queries and am struggling with the vba code.
I figured out how to run a transaction where the queries fail on error. However, the append query I'm trying to include in the transaction is drawing values from a blank form, and as far as I can figure out, that means I need to define the query parameters in the code.
So this is the code where the transaction and error handling works:
Private Sub Command0_Click()
Dim ws As DAO.Workspace, db As DAO.Database
Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)
Set qdf = db.QueryDefs
On Error GoTo ErrTrap
ws.BeginTrans
DoCmd.SetWarnings False
db.Execute "TESTQRY1", dbFailOnError
db.Execute "TEST2QRY", dbFailOnError
db.Execute "TESTQRY3", dbFailOnError
ws.CommitTrans
MsgBox ("You have successfully updated the data")
DoCmd.SetWarnings True
Exit Sub
ErrTrap:
ws.Rollback
MsgBox "Rollback needed because:" & vbCr & Err.Description
End Sub
And this is the code that works for actually running the query, but which doesn't have the error handling or transaction working:
Private Sub Add_Click()
Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim ws As DAO.Workspace
Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)
Set qry = db.QueryDefs("APPENDQRY")
qry.Parameters(0) = Forms!LotNumberFrm!txtLotNumber
qry.Parameters(1) = Forms!LotNumberFrm!txtFWNumber
qry.Parameters(2) = Forms!LotNumberFrm!txtExpDate
qry.Parameters(3) = Forms!LotNumberFrm!chkActive
qry.Execute
Exit Sub
End Sub
So basically my issue is that I need to do both of these things at the same time - run the query as part of a transaction with error handling AND define the query parameters in the code.
I've tried to slice and splice these two bits of code together a bunch of different ways without success. Any help would be much appreciated.
