Error handling withing error handling?

[Tony Vrolyk]

I have a subroutine on a form that is using transactions to undo changes if
there is an error. The rollback is done in the error handling at the end of
the subroutine . However in the body of the subroutine I am using some DAO
to delete a record and it is quite possible and expected that it will fail
on occasion because of related records in other tables. If that is the case
I want it to fail to preserve the record.

My question is this. How do I handle the delete error and continue without
causing the subroutine error handling to fire and therefore rollback the
procedure yet still allow it to fire when other errors occur.

Sample below
Thanks
Tony Vrolyk

'**********sample***********
Private Sub cmdSaveAndDelete_Click()
On Error GoTo HandleErr

Dim wrkDefault As Workspace
Dim rst As DAO.Recordset

Set wrkDefault = DBEngine.Workspaces(0)
wrkDefault.BeginTrans

'Delete Participant record
Set rst = CurrentDb().OpenRecordset("Participants", dbOpenDynaset)

rst.FindFirst "[SSN] ='" & Me.OpenArgs & "'"
If Not rst.NoMatch Then
rst.Delete
End If

rst.Close

'some more DAO
'even more DAO

Set rst = Nothing

wrkDefault.CommitTrans

ExitHere:
Exit Sub

HandleErr:
wrkDefault.Rollback
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & vbCr & Err.Description,
vbCritical, "Form_Participants_CorrectSocial.cmdSaveNew_Click"
End Select
Resume ExitHere

End Sub
'*******************

 

[Dirk Goldgar]

I have a subroutine on a form that is using transactions to undo
changes if there is an error. The rollback is done in the error
handling at the end of the subroutine . However in the body of the
subroutine I am using some DAO to delete a record and it is quite
possible and expected that it will fail on occasion because of
related records in other tables. If that is the case I want it to
fail to preserve the record.

My question is this. How do I handle the delete error and continue
without causing the subroutine error handling to fire and therefore
rollback the procedure yet still allow it to fire when other errors
occur.

Sample below
Thanks
Tony Vrolyk

'**********sample***********
Private Sub cmdSaveAndDelete_Click()
On Error GoTo HandleErr

Dim wrkDefault As Workspace
Dim rst As DAO.Recordset

Set wrkDefault = DBEngine.Workspaces(0)
wrkDefault.BeginTrans

'Delete Participant record
Set rst = CurrentDb().OpenRecordset("Participants", dbOpenDynaset)

rst.FindFirst "[SSN] ='" & Me.OpenArgs & "'"
If Not rst.NoMatch Then
rst.Delete
End If

rst.Close

'some more DAO
'even more DAO

Set rst = Nothing

wrkDefault.CommitTrans

ExitHere:
Exit Sub

HandleErr:
wrkDefault.Rollback
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & vbCr & Err.Description,
vbCritical, "Form_Participants_CorrectSocial.cmdSaveNew_Click"
End Select
Resume ExitHere

End Sub
'*******************

There are a couple of ways to handle this. One way would be to simply
disable the error-handler while you attempt to delete the record, and
then reinstate it afterward:

rst.FindFirst "[SSN] ='" & Me.OpenArgs & "'"
If Not rst.NoMatch Then
On Error Resume Next
rst.Delete
On Error GoTo HandleErr
End If

Another way would be to add some complexity to your error-handling code,
so that instead of rolling back the transaction on every error, it first
checks to see if the error that has occurred is one that should be
ignored. For this purpose, you'd need to identify the specifif error
number to test for, and you'd probably also need to have a variable that
you would set at various locations in your code to indicate what stage
your processing is at. In other words, paraphrasing the logic,

If (Err.Number = <Unable to Delete>) _
And (ProcessingStage = "deleting participant") _
Then
Resume Next
Else
wrkDefault.Rollback
MsgBox "Error " & Err.Number & vbCr & Err.Description, _
vbCritical, _
"Form_Participants_CorrectSocial.cmdSaveNew_Click"
Resume ExitHere
End If