Re: Is David Fenton right about error handling?


Anthony England wrote:
OK I know I am cheating, by now choosing to use ADO instead of DAO, but consider version 4. It is similar to the version 2 which you approve of. This function works fine as long as tblContact is there, but if I rename the table to tblBogus for instance, I get stuck in a never-ending loop caused by me trying to shut the recordset if it is not nothing (should have checked if it was actually open).
Now I don't know whether there is any way you can have a DAO recordset which 'is not nothing' but either not to be open or to be un-closable due to some other reason (** can anyone think of an example??? **). However, this is possible with ADO and I am concerned with the general principal of guaranteeing that exit code always exits and never-ending loops cannot occur.

If you are concerned about errors occurring in your error/exit handlers you could use the On Error Resume Next statement, but then follow each subsequent statement with a test for Err.Number<>0 to verify that there was no error. This also a useful technique for dealing with errors that you want to test for at specific points in your code rather than just in general.

So, using your last example as a starting point and showing this technique in both places that I have mentioned (note that this and the other examples are to demonstrate the error handling techniques under discussion only):

Public Function ContactExists4(lngConID As Long) As Boolean
    On Error GoTo Err_Handler
    Dim rst As ADODB.Recordset
    Dim strSQL As String

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

    On Error Resume Next 'want to know that the error occured here
    Set rst = New ADODB.Recordset
    If Err.Number<>0 Then
       MsgBox "Could NOT Set the Recordset"
       'Do code that you need to fix this specific error
       'and then continue or goto Exit as appropriate
    End If
    On Error GoTo Err_Handler  'go back to general error handling
    rst.Open strSQL, CurrentProject.Connection

    If Not rst.EOF Then
        ContactExists4 = True
    End If

Exit_Handler:
    On Error Resume Next
    If Not rst Is Nothing Then
        rst.Close
        If Err.Number<>0 Then
           MsgBox "Could NOT close the Recordset"
        End If

        Set rst = Nothing
        If Err.Number<>0 Then
           MsgBox "Could not Set rst = Nothing "
        End If
    End If
    On Error Resume 0
    Exit Function

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

End Function


--
Bri

.

Relevant Pages

  • RE: #Deleted# records in Form
    ... When the user clicks the button to delete a record, I give them a msgbox ... On Error GoTo DelCurrentRec_Error ... Set rst = Nothing ... Dave Hargis, Microsoft Access MVP ...
    (microsoft.public.access.forms)
  • Re: count records from table maching criteria
    ... Dim rst As Recordset ... Set rst = Forms!!.Form.RecordsetClone ... MsgBox rst.recordcount ... Dim dbs as Database, rst As Recordset ...
    (microsoft.public.access.formscoding)
  • Re: Null recordset problem
    ... If you want to not run the report at all in the case of a null recordset, ... Set rst = db.OpenRecordset ... On Error GoTo no_record ...
    (microsoft.public.access.reports)
  • Re: tweak of A2002 Enterprise Dev Handbook fails?
    ... Set rst = New ADODB.Recordset ... system stored procedure, leading it to look for it in the Master database ... Set prm = cmd.CreateParameter("WeekendNo", adInteger, ... Execute method creates a NEW recordset object with default properties and ...
    (microsoft.public.data.ado)
  • RE: Running Parameter Query from a form
    ... "fervet" wrote: ... Also, you can see what is in each recordset field with ?rst!Level1, etc. ... Set rst = qdf.OpenRecordset ... different textboxes that i want to populate with the results from the query. ...
    (microsoft.public.access.formscoding)
Loading