Is David Fenton right about error handling? (re-post?)

[Anthony England]

(sorry for the likely repost, but it is still not showing on my news server
and after that much typing, I don't want to lose it)
I am considering general error handling routines and have written a sample
function to look up an ID in a table. The function returns True if it can
find the ID and create a recordset based on that ID, otherwise it returns
false.

**I am not looking for comments on the usefulness of this function - it is
only to demonstrate error handling**

There are three versions of this code. David Fenton says under the earlier
thread "DAO peculiarity in A97?" that version 1 is bad since it has multiple
lines covered by On Error Resume Next and that a danger exists of this
'spilling over' into another block of code. Can anyone demonstrate this?
Do others have experience of this happening?
It seems he would prefer version 2. But I am wondering - if I cannot rely
on the error handling to be reset when I exit my function, can I guarantee
there is zero possibility of an error in the Exit_Handler part in version 2?
(e.g. the recordset wasn't nothing, but trying to close it causes an error).
If there is an error in the Exit_Handler part, we obviously get stuck in a
never-ending loop, so to some extent it would make sense to make sure that
this cannot happen. The code is also less verbose, particularly when there
are many objects to be cleared up. Perhaps the answer is version 3 which
tacks on a final 'On Error GoTo 0' but I have never seen anyone write a
function with that type of error handling.

**I am undecided and seeking the group's opinions**

Public Function ContactExists1(lngConID As Long) As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT tblContact.* FROM tblContact WHERE " & _
"ConID=" & CStr(lngConID)

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(strSQL, dbOpenForwardOnly, dbReadOnly)

If Not rst.EOF Then
ContactExists1 = True
End If

Exit_Handler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

Public Function ContactExists2(lngConID As Long) As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT tblContact.* FROM tblContact WHERE " & _
"ConID=" & CStr(lngConID)

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(strSQL, dbOpenForwardOnly, dbReadOnly)

If Not rst.EOF Then
ContactExists2 = True
End If

Exit_Handler:

If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

Public Function ContactExists3(lngConID As Long) As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT tblContact.* FROM tblContact WHERE " & _
"ConID=" & CStr(lngConID)

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(strSQL, dbOpenForwardOnly, dbReadOnly)

If Not rst.EOF Then
ContactExists3 = True
End If

Exit_Handler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
On Error GoTo 0
Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

[David W. Fenton]

"Anthony England" <ae******@oops.co.uk> wrote in
news:dp**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com:

(sorry for the likely repost, but it is still not showing on my
news server and after that much typing, I don't want to lose it)
I am considering general error handling routines and have written
a sample function to look up an ID in a table. The function
returns True if it can find the ID and create a recordset based on
that ID, otherwise it returns false.

**I am not looking for comments on the usefulness of this function
- it is only to demonstrate error handling**

There are three versions of this code. David Fenton says under
the earlier thread "DAO peculiarity in A97?" that version 1 is bad
since it has multiple lines covered by On Error Resume Next and
that a danger exists of this 'spilling over' into another block of
code. Can anyone demonstrate this?
Even *I* can't demonstrate it.

However, I had an app that had problems that could only be explained
by the use of On Error Resume Next. When those were removed (or
allowed to apply to only one line at a time), the problems with data
errors being lost disappeared. This was several years ago.

Now, philosophically, I have a definite problem with relying on it.
It's lazy programming, in my opinion.

Using it shows that you *know* an error is going to happen. To me,
that shows that, instead of saying "ignore the error" you should
trap for it and respond accordingly.

The main reason for this is that you can't anticipate every possible
error, and there could be errors that you *don't* want to ignore
(which is what was happening in the app I cited above).

This is, of course, separate from the issue of scope, which I can't
really prove. All I know is that it appeared to me that On Error
Resume Next was not going out of scope when subroutines ended.
Do others have experience of this happening?
It seems he would prefer version 2. But I am wondering - if I
cannot rely on the error handling to be reset when I exit my
function, can I guarantee there is zero possibility of an error in
the Exit_Handler part in version 2? (e.g. the recordset wasn't
nothing, but trying to close it causes an error). If there is an
error in the Exit_Handler part, we obviously get stuck in a
never-ending loop, so to some extent it would make sense to make
sure that this cannot happen. The code is also less verbose,
particularly when there are many objects to be cleared up.
Perhaps the answer is version 3 which tacks on a final 'On Error
GoTo 0' but I have never seen anyone write a function with that
type of error handling.

I don't know what errors could happen in closing a recordset. If you
are avoiding doing it my way because of a worry about an unforeseen
error, then it seems to me to be contradictory of your seeming
preference for a method that ignores *all* errors, anticipated or
not. In other words, you seem to prefer a method that ignores *all*
unforeseen errors to a method that may break if *one* unforeseen
error occurs.

That seems extremely unwise to me.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Randy Harris]

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...

"Anthony England" <ae******@oops.co.uk> wrote in
news:dp**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com:
However, I had an app that had problems that could only be explained
by the use of On Error Resume Next. When those were removed (or
allowed to apply to only one line at a time), the problems with data
errors being lost disappeared. This was several years ago.

Now, philosophically, I have a definite problem with relying on it.
It's lazy programming, in my opinion.

Do others have experience of this happening?
It seems he would prefer version 2. But I am wondering - if I
cannot rely on the error handling to be reset when I exit my
function, can I guarantee there is zero possibility of an error in
the Exit_Handler part in version 2? (e.g. the recordset wasn't
nothing, but trying to close it causes an error). If there is an
error in the Exit_Handler part, we obviously get stuck in a
never-ending loop, so to some extent it would make sense to make
sure that this cannot happen. The code is also less verbose,
particularly when there are many objects to be cleared up.
Perhaps the answer is version 3 which tacks on a final 'On Error
GoTo 0' but I have never seen anyone write a function with that
type of error handling.

I don't know what errors could happen in closing a recordset. If you
are avoiding doing it my way because of a worry about an unforeseen
error, then it seems to me to be contradictory of your seeming
preference for a method that ignores *all* errors, anticipated or
not. In other words, you seem to prefer a method that ignores *all*
unforeseen errors to a method that may break if *one* unforeseen
error occurs.

That seems extremely unwise to me.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

I had a problem similar to the one David describes with On Error Resume
Next. It was particularly bad because I let it get out to a customer that
way (shame on me). It caused some highly intermittent problems that were
extremely difficult to pin down. It was several years ago and I no longer
have the code.

I also agree that its use is "lazy programming" but I still use it on
occasion. Properly trapping errors is always better, but I'm a lazy
programmer. The solution that I've come up with is to never, ever leave a
section of code with On Error Resume Next "unterminated". I consider On
Error Goto 0 (or Goto something else) a proper termination. I used to put
Err.Clear ahead of the Goto, but that didn't seem to make any difference.
Also, keep blocks of code with Resume Next as small as possible and be very
certain that code that doesn't get run and doesn't generate an error can't
cause problems elsewhere. This "solution" has worked well for me.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

[Steve]

Another perspective: I sometimes trap for particular errors that I want
to do special processing with, then use Resume Next after that
processing so the user never sees any error I don't want them to. As
David said, some errors shouldn't be ignored, but at the same time I'd
rather keep frivolous errors from popping up than show every error.
Note that it would be difficult to trap EVERY possible error that is
not terribly important, or rather it would take a lot of code.

[Anthony England]

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...

"Anthony England" <ae******@oops.co.uk> wrote in
news:dp**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com:

(sorry for the likely repost, but it is still not showing on my
news server and after that much typing, I don't want to lose it)
I am considering general error handling routines and have written
a sample function to look up an ID in a table. The function
returns True if it can find the ID and create a recordset based on
that ID, otherwise it returns false.

**I am not looking for comments on the usefulness of this function
- it is only to demonstrate error handling**

There are three versions of this code. David Fenton says under
the earlier thread "DAO peculiarity in A97?" that version 1 is bad
since it has multiple lines covered by On Error Resume Next and
that a danger exists of this 'spilling over' into another block of
code. Can anyone demonstrate this?

Even *I* can't demonstrate it.

However, I had an app that had problems that could only be explained
by the use of On Error Resume Next. When those were removed (or
allowed to apply to only one line at a time), the problems with data
errors being lost disappeared. This was several years ago.

Now, philosophically, I have a definite problem with relying on it.
It's lazy programming, in my opinion.

Using it shows that you *know* an error is going to happen. To me,
that shows that, instead of saying "ignore the error" you should
trap for it and respond accordingly.

The main reason for this is that you can't anticipate every possible
error, and there could be errors that you *don't* want to ignore
(which is what was happening in the app I cited above).

This is, of course, separate from the issue of scope, which I can't
really prove. All I know is that it appeared to me that On Error
Resume Next was not going out of scope when subroutines ended.

Do others have experience of this happening?
It seems he would prefer version 2. But I am wondering - if I
cannot rely on the error handling to be reset when I exit my
function, can I guarantee there is zero possibility of an error in
the Exit_Handler part in version 2? (e.g. the recordset wasn't
nothing, but trying to close it causes an error). If there is an
error in the Exit_Handler part, we obviously get stuck in a
never-ending loop, so to some extent it would make sense to make
sure that this cannot happen. The code is also less verbose,
particularly when there are many objects to be cleared up.
Perhaps the answer is version 3 which tacks on a final 'On Error
GoTo 0' but I have never seen anyone write a function with that
type of error handling.

I don't know what errors could happen in closing a recordset. If you
are avoiding doing it my way because of a worry about an unforeseen
error, then it seems to me to be contradictory of your seeming
preference for a method that ignores *all* errors, anticipated or
not. In other words, you seem to prefer a method that ignores *all*
unforeseen errors to a method that may break if *one* unforeseen
error occurs.

That seems extremely unwise to me.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Thank you for your response. I am not advocating one approach or the other,
merely asking for input and experiences from others.
You may have missed the central point of my question - and it is not about
cutting corners to be lazy. I am talking specifically about the 'clean up
and exit' part of the function where any error would get us into a
never-ending loop. What you seem to be saying is:
"You cannot guarantee that On Error Resume Next will be 'switched off' when
you leave the function, but you can guarantee that the clean up code will
never ever result in an error being produced (resulting in the ghastly
loop)"

I understand that you cannot give an example of this happening, but am
prepared to believe something like this might happen on some occasions, yet
at the same time like the general idea that the 'clean up and exit' part of
the function always exits.

Would anyone like to comment on version 3, or stick their necks out and
write the best exit-function bit?

[Anthony England]

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:XD*******************@newssvr21.news.prodigy. com...

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...

"Anthony England" <ae******@oops.co.uk> wrote in
news:dp**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com:
However, I had an app that had problems that could only be explained
by the use of On Error Resume Next. When those were removed (or
allowed to apply to only one line at a time), the problems with data
errors being lost disappeared. This was several years ago.

Now, philosophically, I have a definite problem with relying on it.
It's lazy programming, in my opinion.

> Do others have experience of this happening?
> It seems he would prefer version 2. But I am wondering - if I
> cannot rely on the error handling to be reset when I exit my
> function, can I guarantee there is zero possibility of an error in
> the Exit_Handler part in version 2? (e.g. the recordset wasn't
> nothing, but trying to close it causes an error). If there is an
> error in the Exit_Handler part, we obviously get stuck in a
> never-ending loop, so to some extent it would make sense to make
> sure that this cannot happen. The code is also less verbose,
> particularly when there are many objects to be cleared up.
> Perhaps the answer is version 3 which tacks on a final 'On Error
> GoTo 0' but I have never seen anyone write a function with that
> type of error handling.

I don't know what errors could happen in closing a recordset. If you
are avoiding doing it my way because of a worry about an unforeseen
error, then it seems to me to be contradictory of your seeming
preference for a method that ignores *all* errors, anticipated or
not. In other words, you seem to prefer a method that ignores *all*
unforeseen errors to a method that may break if *one* unforeseen
error occurs.

That seems extremely unwise to me.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

I had a problem similar to the one David describes with On Error Resume
Next. It was particularly bad because I let it get out to a customer that
way (shame on me). It caused some highly intermittent problems that were
extremely difficult to pin down. It was several years ago and I no longer
have the code.

I also agree that its use is "lazy programming" but I still use it on
occasion. Properly trapping errors is always better, but I'm a lazy
programmer. The solution that I've come up with is to never, ever leave a
section of code with On Error Resume Next "unterminated". I consider On
Error Goto 0 (or Goto something else) a proper termination. I used to put
Err.Clear ahead of the Goto, but that didn't seem to make any difference.
Also, keep blocks of code with Resume Next as small as possible and be
very
certain that code that doesn't get run and doesn't generate an error can't
cause problems elsewhere. This "solution" has worked well for me.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

Hi Randy. Thanks for the response. Your mention of Err.Clear prompted me
to re-read the help file which clearly states that the method is called
after the Exit Function. But I guess if you couldn't rely on one, then you
couldn't rely on the other.

' ==================================================
' ====== Acc XP Help File ===============================
Clear Method

Clears all property settings of the Err object.

Syntax

object.Clear

The object is always the Err object.

Remarks

Use Clear to explicitly clear the Err object after an error has been
handled, for example, when you use deferred error handling with On Error
Resume Next. The Clear method is called automatically whenever any of the
following statements is executed:

Any type of Resume statement
Exit Sub, Exit Function, Exit Property
Any On Error statement
Note The On Error Resume Next construct may be preferable to On Error GoTo
when handling errors generated during access to other objects. Checking Err
after each interaction with an object removes ambiguity about which object
was accessed by the code. You can be sure which object placed the error code
in Err.Number, as well as which object originally generated the error (the
object specified in Err.Source).

[Randy Harris]

"Anthony England" <ae******@oops.co.uk> wrote in message
news:dp**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...

Hi Randy. Thanks for the response. Your mention of Err.Clear prompted me
to re-read the help file which clearly states that the method is called
after the Exit Function. But I guess if you couldn't rely on one, then you couldn't rely on the other.

' ==================================================
' ====== Acc XP Help File ===============================
Clear Method

Clears all property settings of the Err object.

Syntax

object.Clear

The object is always the Err object.

Remarks

Use Clear to explicitly clear the Err object after an error has been
handled, for example, when you use deferred error handling with On Error
Resume Next. The Clear method is called automatically whenever any of the
following statements is executed:

Any type of Resume statement
Exit Sub, Exit Function, Exit Property
Any On Error statement
Note The On Error Resume Next construct may be preferable to On Error GoTo when handling errors generated during access to other objects. Checking Err after each interaction with an object removes ambiguity about which object
was accessed by the code. You can be sure which object placed the error code in Err.Number, as well as which object originally generated the error (the
object specified in Err.Source).

Sorry, I didn't make that very clear. I didn't mean to suggest that
Err.Clear was unreliable. The nightmare I mentioned, that I ran into a while
back, was caused by assuming that On Error Resume Next would cleanly go out
of scope. Tracking that problem down cost me a lot of unproductive time and
a lot of embarrassment with a customer. (I couldn't explain the bizarre
problems the app was having). I've never run into a similar situation using
On Error Goto 0.

IMO - It is not uncommon to hide errors from the user, but, a well written
application should never let the user see an unhandled error. I wish I
could say that were true of all my apps.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

[David W. Fenton]

"Anthony England" <ae******@oops.co.uk> wrote in
news:dp**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com:

What you seem to be saying is:
"You cannot guarantee that On Error Resume Next will be 'switched
off' when you leave the function, but you can guarantee that the
clean up code will never ever result in an error being produced
(resulting in the ghastly loop)"
No, I'm not saying that.

I'm saying that you're describing a choice between knowingly
ignoring all errors, including unanticipated ones, and an
alternative that handles the anticipated errors, but could perhaps
get into a loop *if* an error happens in the exit routine. You are
correct, I think, that the only likely line for errors to happen is
the rst.Close line, but I can't conceive of exactly what could cause
an error to happen on that line.

So, it's comparing a nearly inconceivable error that might get you
into a loop to an approach that will knowingly ignore any unforeseen
error.

I definitely prefer taking my chances with the unforeseen error,
since if it happens once, it can then be trapped for.
I understand that you cannot give an example of this happening,
but am prepared to believe something like this might happen on
some occasions, yet at the same time like the general idea that
the 'clean up and exit' part of the function always exits.

I don't know that it does, and didn't say that it did.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Anthony England]

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:IB****************@newssvr11.news.prodigy.com ...

"Anthony England" <ae******@oops.co.uk> wrote in message
news:dp**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...

Hi Randy. Thanks for the response. Your mention of Err.Clear prompted
me
to re-read the help file which clearly states that the method is called
after the Exit Function. But I guess if you couldn't rely on one, then

you

couldn't rely on the other.

' ==================================================
' ====== Acc XP Help File ===============================
Clear Method

Clears all property settings of the Err object.

Syntax

object.Clear

The object is always the Err object.

Remarks

Use Clear to explicitly clear the Err object after an error has been
handled, for example, when you use deferred error handling with On Error
Resume Next. The Clear method is called automatically whenever any of the
following statements is executed:

Any type of Resume statement
Exit Sub, Exit Function, Exit Property
Any On Error statement
Note The On Error Resume Next construct may be preferable to On Error

GoTo

when handling errors generated during access to other objects. Checking

Err

after each interaction with an object removes ambiguity about which
object
was accessed by the code. You can be sure which object placed the error

code

in Err.Number, as well as which object originally generated the error
(the
object specified in Err.Source).

Sorry, I didn't make that very clear. I didn't mean to suggest that
Err.Clear was unreliable. The nightmare I mentioned, that I ran into a
while
back, was caused by assuming that On Error Resume Next would cleanly go
out
of scope. Tracking that problem down cost me a lot of unproductive time
and
a lot of embarrassment with a customer. (I couldn't explain the bizarre
problems the app was having). I've never run into a similar situation
using
On Error Goto 0.

IMO - It is not uncommon to hide errors from the user, but, a well written
application should never let the user see an unhandled error. I wish I
could say that were true of all my apps.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

Hmmm... difficult to say whether we are talking about genuine weirdness
going on, or the programmer not remembering that there some subtleties with
error handling. For example, the sub CauseAnError has no error handling and
would normally generate a runtime error, but when I click the button to call
the routine, I do not notice the error.

Private Sub CauseAnError()
' Cause an error with absolutely
' no error handling at all
Eval "1/0"
End Sub

Private Sub cmdTest_Click()
On Error Resume Next
MsgBox "The next sub will cause an error"
Call CauseAnError
MsgBox "Did you notice it?"
End Sub

I hope that none of you would expect an error to be triggered or maintain
that this is some weird buggy feature of 'On Error Resume Next' not going
out of scope when it should.
I am not accusing anyone of mis-understanding this and accept things might
not work as they should - it's just a shame that no-one can show an example
of code which doesn't work as it should with regard to error handling.

[Bri]

Anthony England wrote:

Hmmm... difficult to say whether we are talking about genuine weirdness
going on, or the programmer not remembering that there some subtleties with
error handling. For example, the sub CauseAnError has no error handling and
would normally generate a runtime error, but when I click the button to call
the routine, I do not notice the error.

Private Sub CauseAnError()
' Cause an error with absolutely
' no error handling at all
Eval "1/0"
End Sub

Private Sub cmdTest_Click()
On Error Resume Next
MsgBox "The next sub will cause an error"
Call CauseAnError
MsgBox "Did you notice it?"
End Sub

I hope that none of you would expect an error to be triggered or maintain
that this is some weird buggy feature of 'On Error Resume Next' not going
out of scope when it should.
I am not accusing anyone of mis-understanding this and accept things might
not work as they should - it's just a shame that no-one can show an example
of code which doesn't work as it should with regard to error handling.

A routine that does not have an error handler passes the error up the
chain to the calling routine, if there is one, to handle the error. So
in your case, CauseAnError errors and pops back to cmdTest_Click to let
it handle it, which it does by ignoring it since you used On Error
Resume Next

--
Bri

[Anthony England]

"Bri" <no*@here.com> wrote in message
news:g1Vuf.133542$2k.101999@pd7tw1no...

Anthony England wrote:

Hmmm... difficult to say whether we are talking about genuine weirdness
going on, or the programmer not remembering that there some subtleties
with error handling. For example, the sub CauseAnError has no error
handling and would normally generate a runtime error, but when I click
the button to call the routine, I do not notice the error.

Private Sub CauseAnError()
' Cause an error with absolutely
' no error handling at all
Eval "1/0"
End Sub

Private Sub cmdTest_Click()
On Error Resume Next
MsgBox "The next sub will cause an error"
Call CauseAnError
MsgBox "Did you notice it?"
End Sub

I hope that none of you would expect an error to be triggered or maintain
that this is some weird buggy feature of 'On Error Resume Next' not going
out of scope when it should.
I am not accusing anyone of mis-understanding this and accept things
might not work as they should - it's just a shame that no-one can show an
example of code which doesn't work as it should with regard to error
handling.

A routine that does not have an error handler passes the error up the
chain to the calling routine, if there is one, to handle the error. So in
your case, CauseAnError errors and pops back to cmdTest_Click to let it
handle it, which it does by ignoring it since you used On Error Resume
Next

--
Bri

Indeed. That was my point - this is normal documented behaviour and not
some buggy random characteristic of vba error handling. A less-able
programmer might not understand what is happening and blame it on 'On Error
Resume Next' not working properly and 'spilling over'.
(... of course I'm sure this doesn't apply to any of the distinguished
contributers in this discussion!)