Infinite Loop in ErrorHandler That Requires Hard Shutdown

[robert d via AccessMonster.com]

I use unbound forms and DAO. I followed the recommendations of some others
and have come up with the following Exit Handler and Error Handler (this is
just an example; rstFlag states whether the recordset is open and bInTrans
states whether BeginTrans is turned on)

On Error GoTo Err_Ctrl

Dim wst As DAO.Workspace
Dim dbt As DAO.Database
Dim rst As DAO.Recordset

Exit_Sub:

If rstFlag = True Then
rst.Close
Set rst = Nothing
dbt.Close
Set dbt = Nothing

If bInTrans Then 'Rollback if the transaction is active.
wst.Rollback
End If

Set wst = Nothing
rstFlag = False
End If

DoCmd.SetWarnings True
Exit Sub

Err_Ctrl:
DoCmd.Hourglass False
errMsgStr = ""
ctrlfnctnm = "Save_Edit_Button"
'Call global error handler
Call BuyInfoForm_err(Err.Number, Err.Description, Err.Source, ctrlfnctnm,
errMsgStr)
Resume Exit_Sub

I recently added the transaction conditional statement in the exit handler.
I had a problem with the code where I began the transaction but then forgot
to commit or roll it back in the body of the code. When the code got to rst.
Close in the Exit handler, I got an error. Okay, I fixed the error, but
the problem with the error was that it created an infinite loop. The Exit
Handler error called the Error Handler which then tried to call the Exit
Handler which then called the Error Handler and so on.

I thought I was handling my Error Handling and Exit Handling correctly. But
I see now that if there is an error in the Exit Handler it creates an
infinite loop.

What am I doing wrong?

Thanks.

 

[Dirk Goldgar]

I use unbound forms and DAO. I followed the recommendations of some
others and have come up with the following Exit Handler and Error
Handler (this is just an example; rstFlag states whether the
recordset is open and bInTrans states whether BeginTrans is turned on)

On Error GoTo Err_Ctrl

Dim wst As DAO.Workspace
Dim dbt As DAO.Database
Dim rst As DAO.Recordset

Exit_Sub:

If rstFlag = True Then
rst.Close
Set rst = Nothing
dbt.Close
Set dbt = Nothing

If bInTrans Then 'Rollback if the transaction is active.
wst.Rollback
End If

Set wst = Nothing
rstFlag = False
End If

DoCmd.SetWarnings True
Exit Sub

Err_Ctrl:
DoCmd.Hourglass False
errMsgStr = ""
ctrlfnctnm = "Save_Edit_Button"
'Call global error handler
Call BuyInfoForm_err(Err.Number, Err.Description, Err.Source,
ctrlfnctnm, errMsgStr)
Resume Exit_Sub

I recently added the transaction conditional statement in the exit
handler. I had a problem with the code where I began the transaction
but then forgot to commit or roll it back in the body of the code.
When the code got to rst. Close in the Exit handler, I got an error.
Okay, I fixed the error, but the problem with the error was that it
created an infinite loop. The Exit Handler error called the Error
Handler which then tried to call the Exit Handler which then called
the Error Handler and so on.

I thought I was handling my Error Handling and Exit Handling
correctly. But I see now that if there is an error in the Exit
Handler it creates an infinite loop.

What am I doing wrong?

Thanks.

You should either ignore errors once you reach the exit code, like this:

Exit_Sub:
On Error Resume Next

or else set a flag that indicates where you are in the procedure -- for
example, that you are in the exit code -- and let your error handler
check that flag to determine where to Resume execution.

 

[robert d via AccessMonster.com]

Thank you, Dirk:

I'm going to have to add this to all of my procedures (probably at least 300
to 400 total).

Is there a way to have Access do a find one line and replace with two. Or
how could I code VBA to do this.

So what I want is:

Find: Exit_Sub:

Replace: Exit_Sub:

I use unbound forms and DAO. I followed the recommendations of some
others and have come up with the following Exit Handler and Error

[quoted text clipped - 51 lines]

Thanks.

You should either ignore errors once you reach the exit code, like this:

Exit_Sub:
On Error Resume Next

or else set a flag that indicates where you are in the procedure -- for
example, that you are in the exit code -- and let your error handler
check that flag to determine where to Resume execution.

 

[Dirk Goldgar]

Thank you, Dirk:

I'm going to have to add this to all of my procedures (probably at
least 300 to 400 total).

Is there a way to have Access do a find one line and replace with
two. Or how could I code VBA to do this.

So what I want is:

Find: Exit_Sub:

Replace: Exit_Sub:
On Error Resume Next

I don't know any way to get the Find and Replace dialog to let you
include a new-line in the replace text. Maybe someone else will jump in
with a way, but I don't see it.

You could, however, replace

Exit_Sub:

with

Exit_Sub: On Error Resume Next

and it would work OK, despite not being prettily laid out.

When it comes to programming such a find and replace using VBA, I know
you could do it, but I think you'd need an external database or add-in
to run that code in, as otherwise you'd be modifying the VB project in
which the code was running. But have a look at MZ-Tools
(http://www.mztools.com/) and see what they have in the way of add-ins
for the VBA environment. It wouldn't surprise me at all to find that
they have a freeware add-in to do what you want.