Clear trapped error : especially Attn Douglass Steele

[Guest]

I have an ErrorFunction with a select case in which I trap certain errors and
display appropriate messages for them;

The last thing this function does is call an EndFunction where some basic
application elements are reset in case of code interruption; and in which I
check for untrapped DAO and VBA errors;

In the ErrorFunction after each trapped error message I need to clear the
trapped error before calling the EndFunction, otherwise I get my custom
message AND a second more generic DAO or VBA error message is also displayed.

To avoid this I have tried using "Err.Clear" but this doesn't work. How can
I clear my trapped error before re-directing my code?

Thanks again.

 

[Douglas J. Steele]

I'm not following your setup. You'll need to post your code.

 

[Guest]

Here is the ErrorFunction that is called when errors are trapped (please
correct for line wrapping); note the Err.Clear in the last line, which fails
to clear the error:

Public Function ErrorFunction(argType As Integer, Optional argDesc1 As String)
'DISPLAY MESSAGES FOR TRAPPED ERRORS
Select Case argType
Case Is = 1: MsgBox "The " & argDesc1 & " is a required field!",
vbOKOnly + vbCritical, "FORM INCOMPLETE": End
Case Is = 2: MsgBox "The command was canceled and will not be
performed!", vbOKOnly + vbInformation, "COMMAND CANCELLED!": End
Case Is = 3: MsgBox "A problem was encountered at: '" & argDesc1 & "'!",
vbOKOnly + vbCritical, "FORM ERROR!": End
Case Is = 4: MsgBox "The back-end database is in use and cannot be
backed up at this time. You may continue working.", vbOKOnly + vbInformation,
"DATABASE IN USE": Err.Clear: Call EndFunction(True)
End Select
End Function

Here is the EndFunction that may be called either from the ErrorFunction OR
directly from any number of other procedures:

Public Function EndFunction(argQuit As Boolean)
'RESET BASIC APPLICATION ELEMENTS; CHECK FOR ERRORS;
Dim errObject As DAO.Error
Dim strError As String
If DBEngine.Errors.Count > 0 Then 'DAO errors
For Each errObject In DBEngine.Errors
strError = strError & "DAO Error: " & errObject.Number & ": " &
errObject.Description & vbCrLf
Next errObject
End If
If Err.Number <> 0 Then 'VBA errors
strError = strError & "VBA Error: " & Err.Number & ": " & Err.Description
End If
If Len(strError) > 0 Then
MsgBox strError, vbOKOnly & vbCritical, "ERROR!"
Err.Clear
End If
DoCmd.Hourglass False
DoCmd.SetWarnings True
RefreshDatabaseWindow
DoEvents
If argQuit = True Then End
End Function

Hope this makes more sense...

 

[Douglas J. Steele]

First, get rid of the End statements in your function: I don't know why
they're even in the VBA language!

From the Help file:

"The End statement stops code execution abruptly, without invoking the
Unload, QueryUnload, or Terminate event, or any other Visual Basic code.
Code you have placed in the Unload, QueryUnload, and Terminate events of
forms and class modules is not executed. Objects created from class modules
are destroyed, files opened using the Open statement are closed, and memory
used by your program is freed. Object references held by other programs are
invalidated.

"The End statement provides a way to force your program to halt. For normal
termination of a Visual Basic program, you should unload all forms. Your
program closes as soon as there are no other programs holding references to
objects created from your public class modules and no code executing."

Why are you bothering with the call to EndFunction? You should only need to
call it if an error is raised in your code.

 

[Guest]

I have the end function as a generic finish to many other subs; in some of
which I turn off SetWarning, change the cursor, etc. In these subs, I use a
construct like:

On Error GoTo ErrX
....other code...
ErrX: Call EndFunction

That way I only need one function to handle most errors and reset the
application stuff. For the most part these subs are not connected to forms
other than to be fired by a button or something.

By the time the code gets to ErrorFunction an error has already occurred, so
I need the code to end; by the time it gets to EndFunction, whatever I was
doing is already done, or an untrapped error has occurred; so I need it to
quit.

I have coded a LOT in Excel and I use this as a standard construct and it
works really well; I don't have to build a separate handler for every sub;
everything is funelled through ErrorFunction and EndFunction - all errors are
automatically handled and cleared and all application settings are reset in
one place: end of story.

Perhaps it won't work in Access, but it seems to be so far. In any case,
shouldn't Err.Clear clear the error? or Is there some other method I'm
missing?

 

[Douglas J. Steele]

I don't believe there's anyway to clear the DBEngine.Errors collection.

From the Help file:

"Any operation involving DAO objects can generate one or more errors. As
each error occurs, one or more Error objects are placed in the Errors
collection of the DBEngine object. When another DAO operation generates an
error, the Errors collection is cleared, and the new set of Error objects is
placed in the Errors collection. The highest-numbered object in the Errors
collection (DBEngine.Errors.Count - 1) corresponds to the error reported by
the Microsoft Visual Basic for Applications (VBA) Err object."

What that implies is that a) you don't need to loop through the Errors
collection AND include the Err object, as well as b) you should only loop
through the Errors collection if Err.Number is non-zero.

 

[Douglas J. Steele]

PS.

The normal approach is to have

On Error GoTo ErrorHandler

at the start of each module, and to have ErrorHandler call your generic
error handling routine.

The free MZ-Tools http://www.mztools.com can be used to insert the necessary
code automatically for you.