How to break out of loop from error handler?

[deko]

I have a logging routine that's supposed to silently log errors caught by
error handler code on certain functions. The problem is sometimes stuff
happens and the error handler can get caught in a loop. Is there some way
to send a break from VBA code to break out of the loop?

Here's what the error handler code looks like:

[MyForm module]
Private Function MyFunction
On Error GoTo HandleErr
[code here]
Exit_Here:
Exit Function
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "MyForm", "MyFunction"
End Select
Resume Exit_Here
End Function

Here's what modHandler.LogErr looks like:

[modHandler module]
Public Sub LogErr(strErrFrm As strString, strErrFunc As String)
Dim db As DAO.Database
Dim strErrMsg As String
Dim strSql As String
strErrMsg = Err.Description
strSql = "INSERT INTO tblErrorLog ( ErrMsg, ErrFrm, ErrFunc ) " & _
"VALUES (strErrMsg, strErrFrm, strErrFunc);
Set db = CurrentDb
db.Execute strSql
Set db = Nothing
End Sub

If something weird happens, I don't want LogErr to get caught in a loop. I
was thinking of something like this:

Public Sub LogErr(strErrFrm As String, strErrFunc As String)
Dim db As DAO.Database
Dim strErrMsg As String
Dim strSql As String
If Nz(Forms("MyForm")!txtLastErr, Now() - 1) >= (Now() - 0.00001) Then
MsgBox "we're in a loop"
[***code to break the loop?***]
Else
Forms("MyForm")!txtLastErr = Now()
strErrMsg = Err.Description
strSql = "INSERT INTO tblErrorLog ( ErrMsg, ErrFrm, ErrFunc ) " & _
"VALUES (strErrMsg, strErrFrm, strErrFunc);
Set db = CurrentDb
db.Execute strSql
Set db = Nothing
End If
End Sub

Is there some way -- other than Application.Quit :) -- to break out of the
loop? Other options?

Thanks in advance.

[MacDermott]

I don't see anything in your code which could generate a loop.
However, if you did something like this:

[MyForm module]
Private Function MyFunction
On Error GoTo HandleErr
dim rst as dao.recordset
rst.open ....

[code here]
Exit_Here:
rst.close
set rst=nothing
Exit Function
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "MyForm", "MyFunction"
End Select
Resume Exit_Here
End Function

If rst somehow gets set to nothing too early, rst.close will generate an
error, and the handler will resume at Exit_Here, causing rst.close to try to
execute again, and generating another error.

I usually deal with that sort of a loop by putting a condition on the
execution:
If (rst is nothing)=false, then rst.close

Perhaps you can locate the source of your "loop" and address that...
"deko" <de**@nospam.com> wrote in message
news:ka********************@comcast.com...

I have a logging routine that's supposed to silently log errors caught by
error handler code on certain functions. The problem is sometimes stuff
happens and the error handler can get caught in a loop. Is there some way
to send a break from VBA code to break out of the loop?

Here's what the error handler code looks like:

[MyForm module]
Private Function MyFunction
On Error GoTo HandleErr
[code here]
Exit_Here:
Exit Function
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "MyForm", "MyFunction"
End Select
Resume Exit_Here
End Function

Here's what modHandler.LogErr looks like:

[modHandler module]
Public Sub LogErr(strErrFrm As strString, strErrFunc As String)
Dim db As DAO.Database
Dim strErrMsg As String
Dim strSql As String
strErrMsg = Err.Description
strSql = "INSERT INTO tblErrorLog ( ErrMsg, ErrFrm, ErrFunc ) " & _
"VALUES (strErrMsg, strErrFrm, strErrFunc);
Set db = CurrentDb
db.Execute strSql
Set db = Nothing
End Sub

If something weird happens, I don't want LogErr to get caught in a loop. I was thinking of something like this:

Public Sub LogErr(strErrFrm As String, strErrFunc As String)
Dim db As DAO.Database
Dim strErrMsg As String
Dim strSql As String
If Nz(Forms("MyForm")!txtLastErr, Now() - 1) >= (Now() - 0.00001) Then
MsgBox "we're in a loop"
[***code to break the loop?***]
Else
Forms("MyForm")!txtLastErr = Now()
strErrMsg = Err.Description
strSql = "INSERT INTO tblErrorLog ( ErrMsg, ErrFrm, ErrFunc ) " & _ "VALUES (strErrMsg, strErrFrm, strErrFunc);
Set db = CurrentDb
db.Execute strSql
Set db = Nothing
End If
End Sub

Is there some way -- other than Application.Quit :) -- to break out of the
loop? Other options?

Thanks in advance.

[Terry Kreft]

Your too late by the time you get there.

You need to break the loop before the problem

For example, this will loop forever:-
Function xxx()
On Error GoTo xxx_error
Err.Raise 1300 + vbObjectError
xxx_ret:
Err.Raise 1301 + vbObjectError
Exit Function
xxx_error:
Resume xxx_ret
End Function

One way to break this would be:-
Function xxx()
On Error GoTo xxx_error
Err.Raise 1300 + vbObjectError
xxx_ret:
On Error Resume Next
Err.Raise 1301 + vbObjectError
Exit Function
xxx_error:
Resume xxx_ret
End Function

--
Terry Kreft
MVP Microsoft Access
"deko" <de**@nospam.com> wrote in message
news:ka********************@comcast.com...

I have a logging routine that's supposed to silently log errors caught by
error handler code on certain functions. The problem is sometimes stuff
happens and the error handler can get caught in a loop. Is there some way
to send a break from VBA code to break out of the loop?

Here's what the error handler code looks like:

[MyForm module]
Private Function MyFunction
On Error GoTo HandleErr
[code here]
Exit_Here:
Exit Function
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr "MyForm", "MyFunction"
End Select
Resume Exit_Here
End Function

Here's what modHandler.LogErr looks like:

[modHandler module]
Public Sub LogErr(strErrFrm As strString, strErrFunc As String)
Dim db As DAO.Database
Dim strErrMsg As String
Dim strSql As String
strErrMsg = Err.Description
strSql = "INSERT INTO tblErrorLog ( ErrMsg, ErrFrm, ErrFunc ) " & _
"VALUES (strErrMsg, strErrFrm, strErrFunc);
Set db = CurrentDb
db.Execute strSql
Set db = Nothing
End Sub

If something weird happens, I don't want LogErr to get caught in a loop. I was thinking of something like this:

Public Sub LogErr(strErrFrm As String, strErrFunc As String)
Dim db As DAO.Database
Dim strErrMsg As String
Dim strSql As String
If Nz(Forms("MyForm")!txtLastErr, Now() - 1) >= (Now() - 0.00001) Then
MsgBox "we're in a loop"
[***code to break the loop?***]
Else
Forms("MyForm")!txtLastErr = Now()
strErrMsg = Err.Description
strSql = "INSERT INTO tblErrorLog ( ErrMsg, ErrFrm, ErrFunc ) " & _ "VALUES (strErrMsg, strErrFrm, strErrFunc);
Set db = CurrentDb
db.Execute strSql
Set db = Nothing
End If
End Sub

Is there some way -- other than Application.Quit :) -- to break out of the
loop? Other options?

Thanks in advance.

[deko]

> Your too late by the time you get there.

You need to break the loop before the problem

I think you're right. I was looking for a way to break any loop from any
code in a centralized error handler, but that's not going to happen without
Application.Quit.