Transactions - Error Handler

[patjones]

ADezii,

This is extremely helpful. It's relevant to an issue that I'm having right now in my check tracking database where I have three separate procedure calls to: save employee, save address, save check.

Now, I notice that the .Rollback command is put in the error handler. But in my case, the data processing is taking place inside these various procedures. So if I do this:

Private Sub cmdSave_Click()
 
On Error GoTo Err_cmdSave_Click
 
wrkCurrent.BeginTrans
 
'Employee not yet in the database
SavePerson
 
'New employee's address, or update the address to an existing employee
SaveAddress Forms!frmAddOrEdit
 
'Commit check edits or new check to the table
SaveCheck
 
wrkCurrent.CommitTrans
 
Exit_cmdSave_Click:
    Exit Sub
 
Err_cmdSave_Click:
    MsgBox "Error in subroutine cmdSave_Click: " & Err.Description, vbOKOnly + vbExclamation, "Check Tracking"
    Resume Exit_cmdSave_Click
 
End Sub

...don't I need to put the Rollback in the error handlers for each of the three respective procedures?

Let me know if I need to clarify my situation. Thanks.

Pat

** Edit **
This question pertains to an article (DAO Transaction Processing - What is it?).

[ADezii]

I think what you are looking for are 'Nested Transactions'.
http://support.microsoft.com/kb/177138

[NeoPa]

Pat, your guess is correct I think. Typically a .Rollback is included in an error handler if, as is generally the case, the transaction should be aborted at that point (as I believe is the case in your code).

[patjones]

Thanks!

What I did was to give both the calling and called procedures access to an integer variable (intErrNum) that the called procedure(s) will set to the error number if an error should occur. The calling procedure then checks to see if it has a value or not and does the rollback or commit appropriately.

Private Sub cmdSave_Click()
 
On Error GoTo Err_cmdSave_Click
 
Set wrkCurrent = DAO.DBEngine.Workspaces(0)
intErrNum = 0
 
wrkCurrent.BeginTrans
blnInTrans = True
 
'New employee and/or address?
If blnNewPerson Then SavePerson
If blnNewPerson Or blnUpdateAddress Then SaveAddress Forms!frmAddOrEdit
 
'Commit new check or modifications to the table
SaveCheck
 
If intErrNum = 0 Then
    wrkCurrent.CommitTrans
Else
    wrkCurrent.Rollback
End If
 
Exit_cmdSave_Click:
    Exit Sub
 
Err_cmdSave_Click:
    If blnInTrans Then wrkCurrent.Rollback
    MsgBox "Error in subroutine cmdSave_Click: " & Err.Description, vbOKOnly + vbExclamation, "Check Tracking"
    Resume Exit_cmdSave_Click
 
End Sub

[NeoPa]

That can certainly work Pat. In the circumstances though, I'd consider triggering an error instead, and have the called procedures set up not to handle errors. It seems to fit the concept of handling errors more naturally than setting values in your code.

I suppose that depends on how you think about code though, so this is just a suggestion. Not to be taken as implying your solution is wrong at all.