Error Handling

[BlueJacket]

Is there a way I can improve this error handling or do I not really need much else for such simple procedures?

Private Sub butNewProperty_Click()

    If gEnableErrorHandling Then On Error GoTo Errhandle

    If Me.Dirty Then
        Me.Dirty = False
    End If

    DoCmd.OpenForm "frmNewProperty", , , , , , Me.txtClientID.Value
    DoCmd.Close acForm, Me.Name
    Exit Sub
    
Errhandle:
    MsgBox "Error " & Err.Number & ": " & Err.Description & _
           " Please make a note of this error and when it occured."

End Sub

Thanks in advance.

 

[pbaldy]

The more traditional method includes an exit handler:

http://www.baldyweb.com/ErrorTrap.htm

but yours is fine for something so simple. I always structure mine as in the link, in part because I use MZ Tools and can add the whole bit with a right-click.

 

[BlueJacket]

If I had variables that I declared earlier in the sub, should I set those to nothing before ending? Do they continue to take up space, even after the sub has ended?

 

[pbaldy]

Variables no. As a general rule, you set to nothing or close objects that you used the word "Set" to set to something, like recordsets. I'll see if I can find a good link that describes which.

 

[The_Doc_Man]

I would make maybe one VERY MINOR change as a courtesy to all who see it.

    MsgBox "Error " & Err.Number & ": " & Err.Description & _
           " Please make a note of this error and the time, " & _
           FormatDateTime( Now(), vbGeneralDate )

If you are going to ask someone to note the time, don't make them look at their watch. Look at the COMPUTER'S watch, which will be closer to the time that the event occurred so that if you have to go whacking through event logs, you will have a pretty good time stamp as a starting point.

 

[NauticalGent]

I have to put a plug in here regarding Paul's error and exit handler advice. I had a similar situation. I took his and Docs advice and now EVERY function and sub in my applications have both handlers no matter how small or simple they are. Life has been much better ever since.

 

[The_Doc_Man]

If Paul doesn't come up with anything to further define object handling, I would suggest that if you had to open it, you should close it (based on the "Everything I Needed to Know, I Learned in Kindergarten" book.) There ARE those who disagree but it rarely hurts to be meticulous.

1. For simple variables that are locally declared, they are built on the program stack in something called a Call Frame. The Exit Sub / Exit Function operation automatically releases all locally declared variables, so no action required.

2. If you use SET object-variable = simple-object-of-specified-type (like, say, a control or a form or a database), just SET object-variable = Nothing. That object variable is simply a pointer to an extant object created by other means. It is not your responsibility to clean up anything but the pointer itself. BUT if the pointer was also locally declared in the sub, see #1 above and you don't need the =Nothing assignment in that case.

3. If you use SET object-variable = NEW( some-sort-of-structure), you might wish to release the structure and then set the object-variable to Nothing. The variable is just a pointer, but it points to a structure that is occupying something called "heap space." If you are careless with this, you CAN fill up heap space eventually. If it was an isolated error trap, a program exit will clean up your heap wholesale. If it is the sort of thing that happens more frequently, you probably want to clean these things up piecemeal just to prevent heap overflow. IF the structure-object variable was locally declared, see #1 above and don't worry abut the =Nothing step, just worry about releasing the structure.

4. If you created an application object like opening an Excel workbook, the controversy is whether you need to actually close the object even if you had opened it. I will not say categorically that you MUST do so, but I don't think it is wrong to be meticulous about it.

If the application object variable was local to the subroutine, some members have said that it automatically closes anyway when you do the Exit Sub (implied or explicit) as noted in #1 above.

If the application object is global to the routine (opening using a Public object-variable or passed in to the subroutine as a formal argument), I might suggest closing it only depending on circumstances. If the routine WAS going to close the app object anyway, then close it. If the routine would have left the object open for updates, your next action depends on what it was that you were trying to do.

Final personal note: remember that I approach this as a belt-and-suspenders type of person, simply because I HATE getting caught with my pants down. Therefore, I close everything in sight unless I know it needs to stay open.

 

[pbaldy]

I found a link with great info...see above.

 

[Galaxiom]

I took his and Docs advice and now EVERY function and sub in my applications have both handlers no matter how small or simple they are. Life has been much better ever since.

Some subs are so trivial that including error handling really is overkill and just gets in the way of reading the code. Remember that errors in procedures without handlers are automatically passed up the calling chain until an error handler is found.

When it comes to functions that are being called by a query, the traditional error handler is not the best solution because it will stop with the message on every record that causes the error. Better to return an error code to the query unless you particularly enjoy clicking OK repeatedly.

This can be a quite common situation where the function argument is passed a Null or a value of a type it is not designed to receive. Some developers will use a Variant and test for that inside the function. However this isn't really best practice, firstly because it doesn't define what type is passed and secondly because it wastes time and resources in the function.

Think of the built in VBA functions. They don't generally accept Variant parameters.

 

[Galaxiom]

Is there a way I can improve this error handling or do I not really need much else for such simple procedures?

If gEnableErrorHandling Then On Error GoTo Errhandle

This test placed in every procedure is a runtime overhead that can be avoided with a facility provided in VBA for this exact kind of purpose.

Investigate Conditional Compilation.

 

[BlueJacket]

@Galaxiom

I don't think I'm quite understanding the uses of that code. How I currently understand it, it would be written something like this:

Const conErrorHandling = 1


Private Sub butNewProperty_Click()

If conErrorHandling = 1 Then

    On Error GoTo Errhandle

    If Me.Dirty Then
        Me.Dirty = False
    End If

    DoCmd.OpenForm "frmNewProperty", , , , , , Me.txtClientID.Value
    DoCmd.Close acForm, Me.Name
    Exit Sub
    
Errhandle:
    MsgBox "Error " & Err.Number & ": " & Err.Description & _
               vbCrLf & "Please make a note of this error and when it occured." & _
               vbCrLf & " " & FormatDateTime(Now(), vbGeneralDate)

Else
    If Me.Dirty Then
        Me.Dirty = False
    End If

    DoCmd.OpenForm "frmNewProperty", , , , , , Me.txtClientID.Value
    DoCmd.Close acForm, Me.Name

End If

End Sub

It seems like I'm writing twice the code and it still has to check if the constant = 1.

 

[The_Doc_Man]

No, I think you missed the point.

You will always want to have control_Click handlers.

Inside the handler, there is a way (not the way you showed) to tell Access DO NOT COMPILE THIS CODE and then enclose your error handling inside the appropriate markers.

The pound-sign (#) denotes a meta-statement that isn't part of the code that would be compiled, but rather directs the VBA compiler on how to do something.

You put the conditional marker in the front of the module.

Then inside each routine, you might enclose the stuff related to error handling in the conditional part.

...
#Const IncludeDebug = 1
...

Private Sub XYZ_Click()

#IF IncludeDebug = 1 Then

On Error Goto HandleError

#END IF

... more code

    Exit Sub

#IF IncludeDebug  = 1 THEN

HandleError:
... do your error handling code
    Resume Somewhere

#END IF
...
Rest of subroutine

Then, if you have defined the constant =1, any code inside the conditional compile blocks denoted by #IF and #END IF will be compiled. If not, they won't. You do this when you build in a lot of safety code for an untested database, but you can change your mind later and simply change that CONST declaration to use 0 instead of 1. When you do that, the condition code does not get compiled and thus the error handling is no longer there. You have to be careful to assure that the non-conditional code is not bracketed, that's all.


Note further that any code you think will ALWAYS need testing can be written with no conditional compilation bracketing and thus you will always have the error tests present.

 

[static]

Don't expect users to log errors for you. You can write them to a dedicated table easily enough.

Private Sub Command0_Click()
On Error Resume Next
    
    Err.Raise 13
    
    Select Case Err.Number
    Case 0
    Case Else
        logerr Environ("username"), Me.Name, ActiveControl.Name, _
            Err.Number & ":" & Left(Err.Description, 255)
    End Select
End Sub

'standard module
Public Sub logerr(ParamArray details() As Variant)
    Const sql1 As String = "insert into errlog (errtime,erruser,errform,errobj,errmsg) "
    Dim sql2 As String
    sql2 = "values (now(),'" & Join(details, "','") & "')"
    
    CurrentDb.Execute sql1 & sql2
End Sub

 

[The_Doc_Man]

Static is right that you should include the event in an error log - but it is my experience that if you tell a user to call for help, it will get your attention to the problem quicker than if you had to rely on YOU reading the audit file when you get around to it.

No disrespect intended there, static, but if was worth logging, it might well have been worth reporting, and only BlueJacket would know which errors were worth it.

 

[BlueJacket]

@ The_Doc_Man

Aahhh. I see now. Very cool. I can see the benefits of that. Even though the outcome is the same, the way I have it currently, even when I don't want the error handling to happen, VBA is still compiling that code. I guess saving VBA from compiling that code makes it just that much more efficient?

@Static

I've been thinking about implementing an error log as well. I probably will since it just seems like best practice.
With that code, are you limiting the Err.Description to 255 characters to make sure it fits in a text field? If you didn't have that in place, would it not already cut off the description at 255 characters?

 

[static]

@docman
In my experience users only report errors that seem important to them. That's not necessarily errors that are important to the applications health or programmers interest.

@bluejacket
It was a quick example (and badly written). You should avoid memo fields at all costs in Access.
255 is the max length of a text field so it should be the first 255 of the description minus the error number (+formatting) or better yet record the number and description in separate fields.

 

[The_Doc_Man]

Two answers here:

1. Not compiling the code means the module runs VERY SLIGHTLY faster. But if you have a lot and I mean A LOT of subroutine calls because of having designed a highly modular chunk of code, the "ON ERROR GOTO xxxx" is executable and adds its contribution to run-time. Let's be honest - you might not ever see a run-time benefit for something that is only a couple of lines difference. But if you need extensive error handling or recovery during debugging, something that involves a lot of code that is way bigger than the simple examples here, that might become an issue.

Where I used this to GREATEST effect wasn't in an error handler but instead in a case where for debugging I included extra logging messages, each one essentially saying "Code passed waypoint 12A" so that I could trace sections where stuff happened and know that the code blew up between waypoint 12A and waypoint 12B. Once it was all working, just don't compile the waypoint code and suddenly my event logs shrink to half their normal daily size.

2. As to the text field, I usually truncated it after 100 bytes because (a) most messages aren't THAT long and (b) if I have the error number in a separate field I can look it up using the great Google brain easily enough anyway.

 

[Scott_Hall]

MZ-Tools was mentioned above, which is how I do it.

I have two ErrorHandlers, one for simple code and one for Recordset/Object code.

The only real difference is that the RS/Obj handler has a different exit method to remind me to SET any objects = Nothing and close any RSets.


The RS Handler:

On [SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]Error [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]GoTo[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Error_Handler
dim rs as DAO.Recordset
　
set rs=currentdb.openrecordset("XXXXX",dbopendynaset)
　
<Code>
　
Error_Handler_Exit:
[/SIZE][SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]On[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]Error[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]Resume[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]Next
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]If [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]Not[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] rs [/SIZE][SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]Is[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]Nothing [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]Then[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] rs.Close: [/SIZE][SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]Set[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] rs = [/SIZE][SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]Nothing[/COLOR][/SIZE][/COLOR][/SIZE]
[COLOR=#008000]' HourGlass?
[/COLOR][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Application.Echo True?[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Exit Sub or [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]Exit Function[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Error_Handler:
  Debug.Print "[/SIZE][SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]Error[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] in FORMMODULEREPORT-FUNCTIONSUB: " &   err.Number & " - " & err.Description & " at Line: " & Erl
[/SIZE][SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]Call[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] LogError("FORMMODULEREPORT", "FUNCTIONSUB", err.Number,   err.Description, Erl) 
[/SIZE][SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]Resume[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Error_Handler_Exit
[/SIZE]

and the simpler version:

On [SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]Error [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]GoTo[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Error_Handler
　
<Code>
　
Error_Handler_Exit:
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]' HourGlass?
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Application.Echo True?
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Exit Sub or [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Exit Function[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Error_Handler:
  Debug.Print "[/SIZE][SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]Error[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] in FORMMODULEREPORT-FUNCTIONSUB: " &   err.Number & " - " & err.Description & " at Line: " & Erl
[/SIZE][SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]Call[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] LogError("FORMMODULEREPORT", "FUNCTIONSUB", err.Number,   err.Description, Erl) 
[/SIZE][SIZE=2][COLOR=#000080][SIZE=2][COLOR=#000080]Resume[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Error_Handler_Exit
[/SIZE]

The comments are in place just to remind me of things like shutting off the Hourglass or turning Screen Painting back on.


Edit- I forgot to mention that both call a routine that logs the error into a table and alerts me. Previously I used an EH that took a screenshot and had the user enter some info before it emailed. My users loathed it.

 

[pbaldy]

Interesting. I use a single error handler. I include the "set to nothing" type cleanup lines in code templates, so I just have to cut/paste them from wherever they get dropped when I add the template code to the error handler.

 

[Scott_Hall]

Paul,

I've done that as well. Honestly I will probably combine mine into one with a couple of commented lines, but currently my plate overfloweth.