Error Checking

[isladogs]

I'm not sure I avoided error handling with a sense of pride. But I did take pride in the code I used to prevent errors and the resultant lack of errors reported by the users.
I prefer my own method of - for every step that requires interaction with a user or any transfer of data, internally or externally, check that it will work faultlessly.

I was referring to the comments made by the OP.
We all take pride in checking our code carefully to try and ensure it works correctly. However as my post stated there are errors that will arise in normal use. I gave two examples but there are others. No matter how good our code, error handling is necessary to manage such situations.

 

[isladogs]

This is a pet peeve of mine, and I'm dealing with fellow employees. I've had a vehicle maintenance app running for a long time. I added functionality to sync new repair orders with an outside vendor. It threw an error if this didn't happen properly. My error handling threw up an error that included "notify Paul...". It started failing when the vendor changed something, but nobody told me. Their repair order was still being created and that's all they cared about. Maybe I should implement your email method.

I do recommend it. I used CDO to send the emails to me 'silently' in order to prevent further disrupting the program flow.
The time consuming part was adding the code needed to show precisely what was being done in order to trigger each error.
However, the time spent as more than repaid in terms of time saved in being able to fix any issues quickly.

It also had a side benefit with one client who had been reporting 'random' errors that nobody else could replicate. My approach gave confirmation that all such errors were due to network interruptions (e.g. due to using wifi despite my clear instructions to hardwire all connections) and not my code

 

[NauticalGent]

A very good article on this subject.

 

[NauticalGent]

Below is an excerpt from the Microsoft Access 2010 Programmer’s Reference (ISBN: 978-0-470-59166-6). A very handy book that has taught me a lot. Although I do not agree with everything mentioned in the quote, it is interesting to see how other experts think of the practices mentioned in the thread.

The whole article on Error Handling is informative and it was the first place I saw the additional "Resume" statement used in the Error Handler.

Issues in Error Handling
Some developers try to enhance their error handling with writing log fi les or sending e-mail. There are some issues involved with these error-handling techniques, as explained in the following sections.

Don’t Use Error Handling with Logging.

Some developers write code to insert an error log record into a table or text fi le when an error occurs. The idea is to be able to analyze when and where errors have occurred by querying this table long after the errors happened. However, this technique has some issues.

Access does not provide a way to determine the name of the procedure that is currently running. Because any error logging routine needs to know which procedure caused the error, you need to manually code the name of the current procedure into each error routine. That is labor intensive and prone to errors.
The benefit t of error logging is questionable because few errors should be happening after your code has been tested and delivered. Errors should be rare enough that your users will let you know when they happen. You can always ask them to capture a screenshot if you want to see the details. Some types of errors cause the attempt to log them to fail. Examples include loss of network
connectivity, and disconnected storage hardware. Your user may see additional unrelated
errors, or you could be lulled into thinking that all errors are logged, when they may not be.
If your code is running in a managed environment, it may be benefi cial to log errors to the System
Event Log. For more information on this, refer to Chapter 20. The bottom line is that spending the
time to log unexpected errors to a table or text fi le is not recommended. This is one of those cases
where the benefi ts usually don’t outweigh the costs.
Don’t Use Error Handling That Sends E-Mail
Another interesting way to track the errors that are occurring in an application is to add code to the
error-handling routines that “phone home” when an error occurs. Specifically, the application builds
and sends an e-mail to you (the developer) whenever an unexpected error occurs. This is usually
done with the SendObject method, although there are other ways to utilize MAPI (mail application
programming interface) directly.
This approach has the same problems listed in the preceding section, plus a few more:
‰ Your code needs to be able to send an e-mail using an installed e-mail client. There is
always a possibility that there is no e-mail client installed, or it is not compatible with your
e-mailing code.
‰ Some e-mail clients (for example, Microsoft Outlook) have code to protect against viruses
using the e-mail program to propagate themselves to other computers. If an outside program
(in this case, yours) tries to send an e-mail, a warning message displays, alerting the user that
a virus may be attempting to send e-mail. That isn’t what you want your user to see when
your application is running.
As with error handling with logging, this technique is probably more trouble than it is worth.

 

[gemma-the-husky]

I've not read the whole thread, but here's a few areas that may cause problems.

Absolutely ANY disk read or write. Files may not exist, may be in use, may unexpectedly be empty, may be the wrong file for the process, may be formatted badly, may contain bad data.

Any division, just in case you are dividing by zero.

Any possibility of a numeric overflow.

Any possibility of duplicate records or other indexing failures. You either need to pre-read, or have error handling, surely.

Sense checking, although this isn't quite error handling.

Violation of business rules (or logic rules). You try to sell an out of stock item.

Or even - Prevent some fool running a query that deletes or updates data that wasn't supposed to change.

And even more importantly, maybe - When you get an error, how can you safely recover from the error.

 

[gemma-the-husky]

I almost never us error checking, other than using "Resume on Error", which is in extremely rare occasions is the only way to get the code to run.

In millions of executions, I don't remember a single indecent that error checking would have made any differences.
Just continuing to cultivate my pariah persona. What are your thoughts?

From your original post.
In passing, how would you know? If you ignore every error, how can you be sure that the data doesn't include nulls, orphan records and so on? How can you be sure that a data extract accounts for every record that should be accounted for. That's one of the main issues with databases. Nobody ever sees all the data.

I only ever "on error resume next" when I know the error isn't critical. eg try to delete a temporary object that may or may not exist.

on error resume next
delete object

and even then, I might still prefer belt and braces, and test whether the object exists beforehand, and test that the deletion worked.

 

[Isaac]

@Thales750

I think you're confusing real error handling vs. On Error Resume Next.

You say you WANT errors to happen so you know about them. Of course - everyone does. It sounds like you believe that "error handling" means putting "on error resume next", which is, of course, the most foolish way that nobody should handle errors almost ever - that's not what we mean.

In fact, most people's error handling includes methods, sometimes even sophisticated ones, (think Smiley coder's stuff), to report errors in the most meaningful and informative way possible.

But it's just beyond the pale to imagine that you don't have to include anything, that your code will work perfectly.

And yes, harsh as it may sound, there are definitely people[person] on AWF who think they sh*t gold bricks, and say as much in their sig.
Oh well - the important thing isn't to change their minds, pride goes before a fall anyway, the important thing is to avoid being like that onesself...
Which is why I try to admit my wrongs as often as possible....I have said before and will say again, I think humility is one of the most critical elements of a developer's personality. Without it, you will have a difficult time ingesting all of the feedback from the people whose shoulders you are standing on....which means a) you'll learn less, b) you'll have a distorted idea of your own expertise, which leads to more of a)

So yeah - the "I don't have to handle errors" is the result of the lack of humility and questioning one's own work: 2 key elements of a good developer.

All I say is: Keep any opinion you want, just try to avoid admitting this "no error handling" viewpoint in a job interview

PS this thread is like.......crazy! I see this thread as crazier than half the stuff in the Watercooler and Politics forum!
Next we'll be saying we don't back things up, or compile, or put our pants on one leg....

 

[NauticalGent]

on't back things up, or compile, or put our pants on one leg....

yeah, and....?

 

[pbaldy]

Next we'll be saying we don't back things up

Backups?!? We don't need no stinking backups!!

 

[Isaac]

LOL. Seriously it's true, this thread is nuttier even what I mentioned, since I've seen a number of db's being run uncompiled, and of course we've all made mistakes on not backing up enough.

Personally I JUMP into my pants in midair like an Olympian - but that's easy since I still wear them relaxed fit, like it's 1999...

 

[The_Doc_Man]

A very good article on this subject.

A fairly good article except that it glosses over one important fact: After an error is triggered / trapped, you are in a subroutine entered via a hardware trap (including software traps that supply the trap code as though it were a hardware trap). But this subroutine isn't an ordinary subroutine - it is a trap-context subroutine. It has no arguments passed in and has the scope of the module where the trap was declared, not the module where the trap actually occurred.

As it happens, if your error handler falls through to an END SUB then you will return to the caller of the routine that declared the trap. That is because of the way Windows does trap handling. The RESUME statement (which is not legal outside of trap context) is the only way to get back into the context of the routine for which the handler was declared. There would be the temptation to issue a GOTO from the handler to a statement inside the scope of the routine with a trap, but in fact that can cause system failures. (We've had them reported on this forum.)

Many people read the ON ERROR documentation. Not enough people read the RESUME documentation. It is worth attention. Allen Browne uses ON ERROR and RESUME correctly in his example. (As of COURSE he uses it correctly...)

Microsoft Access tips: Error Handling in VBA

How to trap and log the errors reported while a Microsoft Access database is in use. (Access 95 and onwards.)

allenbrowne.com

 

[gemma-the-husky]

@The_Doc_Man

It occurred to me when I looked at the linked code that the error handler did a goto to an exit point for the procedure. I believe exiting the sub ends the scope of the error trap - but it would have been cleaner and more explicit to specifcally resume to that exit point. It's a good habit to minimise what you do in a error-handler (since subsequent errors are ignored until you resume), and generally resume rather goto.

 

[Isaac]

It's a good habit to minimise what you do in a error-handler (since subsequent errors are ignored until you resume)

Did you mean, errors that occur inside an error handler are automatically ignored until you resume? I think you must have meant something else because that is not the case, you will just get an unhandled error.

Or did you just mean that Resume clears the err object and until it's explicitly cleared or Resume then ... ?

 

[isladogs]

@The_Doc_Man

It occurred to me when I looked at the linked code that the error handler did a goto to an exit point for the procedure. I believe exiting the sub ends the scope of the error trap - but it would have been cleaner and more explicit to specifcally resume to that exit point. It's a good habit to minimise what you do in a error-handler (since subsequent errors are ignored until you resume), and generally resume rather goto.

I'm also confused by your comments.
If you are referring to Allen Browne's code in the link given by @The_Doc_Man, there are definitely no GoTo statements in his code.
If you are referring to other linked code, please could you be explicit & state what you are referring to. Thanks

FWIW, I have always used error handling like that in Allen's webpage. For example:

'generic error handling code

Private Sub ErrorHandlingCode()

On Error GoTo Err_Handler

 'code here
   
Exit_Handler:
    Exit Sub
   
Err_Handler:
    strProc = "" 'add proc name here
    MsgBox "Error " & Err.number & " in " & strProc & " procedure : " & vbCrLf & _
        Err.Description, vbCritical, "Program error"
    Resume Exit_Handler

End Sub

 

[The_Doc_Man]

Obviously, my intent wasn't clear. I was commenting on an omission that I felt left something unclear in the article referenced by @Nautical Gent in his post #47 of this thread.

@Pat Hartman - your suggested usage of Return (Next or a declared statement label) is absolutely correct. No issues there.

@isladogs - yes, true. Allen didn't use GoTo in his code - as well he should not have. There is a danger in using GOTO X in an error handler IF you make the mistake of using it as though it meant the same thing as RESUME X. That is because GOTO X doesn't clear the error context whereas RESUME X does.

@Isaac - Since Access is single-threaded, the only error that CAN occur at that point would be in the error routine itself. I will answer the question later about what happens when an error occurs in an error handler.

You can allow an exit handler to fall into an END SUB and it will be OK because Access tests for stacked context. However, you need to realize where you are when an error occurs and falls through to the END SUB. You have exited the subroutine when that happens. As for example, Pat's idea of using RESUME SUB_EXIT_POINT or whatever label floats your boat.

Suppose you have Sub A which declares a handler via On Error GoTo A_Handler.
In the course of coding Sub A, you call separately declared Sub B which does NOT declare a handler.
In the course of coding Sub B, you call separately declared Sub C which does NOT declare a handler.
Now it turns out that you have a condition that will trigger a run-time error in Sub C. Where will the trap occur?

The answer is A_Handler takes C's trap.

On Error statement (VBA)

Office VBA reference topic

docs.microsoft.com

(Read the "Remarks" section relating to active vs. inactive error handlers.)

OK, Isaac's question: Let's say that above, Sub C actually DOES have an error handler, call it C_Handler, but it is faulty AND gets called due to an error in the "normal" part of Sub C. If an error handler gets activated and there is an error in the code,...

In the three layers I named above, C cannot take the trap because its error handler is already activated. B cannot take the trap because no handler was declared. So I believe in the case of Isaac's question, A_Handler would take that trap.

Finally, again suppose that Sub C gets called, trips over itself such that the error handler C_Handler gets called; but C_Handler has an error, too, and eventually the error "bubbles up" such that A_Handler takes the error. A_Handler finishes with a RESUME NEXT. In this case, which line of code is executed next?

Answer: The line following the call (in the context of Sub A) to Sub B.

Sub C and its instantiation vanished when C_Handler failed and the error got passed up to B - which ALSO failed because the error had to be passed back up another layer - to A_Handler. From A_Handler (which is part of Sub A) you cannot return to any label in B or C via a RESUME SOME_LABEL - because they are no longer in scope. The only thing in scope at that point is Sub A.

Elements of run-time error handling

Office VBA reference topic

docs.microsoft.com

The next reference emphasizes the point that the "current" error handler doesn't necessarily have to be in the same routine in which the error occurred. It's an Excel source but the VBA advice is spot-on.

VBA Error Handling - A Complete Guide - Excel Macro Mastery

“Abort, Retry, Fail?” – MS-DOS error message circa 1986 This post provides a complete guide to VBA Error Handing. If you are looking for a quick summary then check out the quick guide table in the first section. If you are looking for a particular topic on VBA Error Handing then check out the...

excelmacromastery.com

My point, I guess, was that I didn't think the article brought up as much as I thought it should. You guys know that sometimes I get a wild hair somewhere. This post resulted from scratching where that wild hair itched.

 

[Isaac]

@The_Doc_Man
the only question I was asking was what Dave meant. I was just pointing out that you can have additional errors occur inside the error handling...that's all

 

[isladogs]

Thanks Doc but my reply was also directed at Dave - not at you.
Its not at all clear what Dave was referring to.

Also, although I've NEVER known it happen, its easy to determine what happens if errors occur inside an error handler.
Just add err.raise statements like this e.g. to a button click event ...

Private Sub cmdErrorTest_Click()

On Error GoTo Err_Handler

'code here
    MsgBox "Click OK to raise error 11 in the main code"
   Err.Raise 11

Exit_Handler:
    MsgBox "Click OK to raise error 94 in the Exit Handler section"
    Err.Raise 94
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in cmdErrorTest_Click procedure : " & vbCrLf & _
        Err.Description, vbCritical, "Program error"
    MsgBox "Click OK to raise error 7 in the Err Handler section"
    Err.Raise 7
    Resume Exit_Handler

End Sub

If anyone wants to try it out, see whether you can predict what will happen first.
Will all three errors get triggered in turn? If not, then what?

For info, don't worry...an infinite loop doesn't occur

 

[gemma-the-husky]

Did you mean, errors that occur inside an error handler are automatically ignored until you resume? I think you must have meant something else because that is not the case, you will just get an unhandled error.

Or did you just mean that Resume clears the err object and until it's explicitly cleared or Resume then ... ?

Yes - my bad.

especially for @Isaac @isladogs @The_Doc_Man

[I have edited this first para slightly now, and added an extra note below]
That's what I sort of meant. A second error that occurs within the error handler is ignored (ie - does not trigger the error handler again) until after you resume after the error, and this may allow programme errors to be missed. I also didn't mean necessarily within the error handler - I meant that until you issue the resume the error handler is still active, and a second error wouldn't be handled. Without testing I am not sure that you do always get an unhandled error. I thought that in the past, I had data errors that were not picked up because of this, but I could be wrong. See my edit below. Therefore you don't want any code within the error handler that might throw another error. Using goto rather resume is a bad habit, as it can be the cause of unhandled errors. I am pretty sure that ending the sub also exits the error handler, but I prefer to explicitly resume, and to keep the error handlers as short as possible.

[edit - I see what you mean now. I just tried your code and a second error within the error handler did produce an unhandled error. Maybe there are some errors that wouldn't get flagged as unhandled errors. As I say, I thought I had experienced issues caused by such circumstances, but I am always careful to resume after any error, and I am quite likely getting confused. Alternatively, maybe it was just code not working correctly because of accidentally using a Goto instead of a Resume, and then finding that a different on error trap did not work as expected]

 

[Isaac]

Hi folks,

These are the last few lines of my function.

You can see On Error Resume Next in them.

This doesn't work - if "permission denied" to delete what I am calling the 'log file', it raises a Debug error!
(You can see what I am doing - I have boolean variables, starting with bl, to indicate whether a given file still exists - and then in my error handler, I'm deleting them if they exist - a common practice which has worked well for me for a long time - but now I am realizing that the On Error Resume Next portion of it actually never worked apparently, because while inside the error handler, the On Error Resume Next is ignored.

Thoughts?

Exit Function
errhandler:
If blBatExists = True Then
    Kill strBatPath
End If
If blScriptExists = True Then
    Kill strScriptPath
End If
If blLogExists = True Then
    On Error Resume Next
    Kill strDownloadFolderPath & "\WinSCP.log"
End If
GetSourceFile_Method_One = "Error: " & Err.Description
End Function

The log file is the only thing I can't be sure if it will be delete-able, because, it's outside the control of my program.

 

[NauticalGent]

If I understand you correctly, all you would have to do is trap for that particular err.Number and simply do nothing and resume where you left off...?