(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.OpenRecords et(strSQL, dbOpenForwardOn ly, 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.OpenRecords et(strSQL, dbOpenForwardOn ly, 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.OpenRecords et(strSQL, dbOpenForwardOn ly, 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
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.OpenRecords et(strSQL, dbOpenForwardOn ly, 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.OpenRecords et(strSQL, dbOpenForwardOn ly, 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.OpenRecords et(strSQL, dbOpenForwardOn ly, 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
Comment