Thread: VBA Code Sequence Question

In the following Code, would the "Exit Here:" code run even if there is no error? Because sometimes I get an error when I have my close in both places.

Private Sub Template()
    Dim rsXXXXX As New ADODB.Recordset
    
On Error GoTo HandleError
    
    rsXXXXX.Open "Select * From tblName Where FieldName ='" & CompareString & "'", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    If Not rsXXXXX.EOF Then
        With rsXXXXX
            
        End With
    End If
    rsXXXXX.Close
    Set rsXXXXX.ActiveConnection = Nothing
    
ExitHere:
    rsXXXXX.Close
    Set rsXXXXX.ActiveConnection = Nothing
    Exit Sub
    
HandleError:
    MsgBox Err.Description
    Resume ExitHere
    
End Sub

Thanks in advance,

Dan

Sure. You need an Exit Sub before Exit Here.

And this is a preferable declaration:

Private Sub Template()
    Dim rsXXXXX     As ADODB.Recordset

    Set rsXXXXX = New ADODB.Recordset

Thank you this answers my question perfectly!!!
I can use the preferable, side note question. Why? i.e. why have 2 statements to do one?

Thanks in advance,

Dan

Another question on topic:

Since my code seems to be buggy at best. (i.e. I am learning)

I want to ensure I close connections even if there is an error. I learned that if the .close and =nothing are after the resume exithere, then I end up in an endless loop.

So should I use:

Private Sub Template()
    Dim rsXXXXX As New ADODB.Recordset
    
On Error GoTo HandleError
    
    rsXXXXX.Open "Select * From tblName Where FieldName ='" & CompareString & "'", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    If Not rsXXXXX.EOF Then
        With rsXXXXX
            
        End With
    End If
    rsXXXXX.Close
    Set rsXXXXX.ActiveConnection = Nothing
    
ExitHere:
    Exit Sub
    
HandleError:
    MsgBox Err.Description
    rsXXXXX.Close
    Set rsXXXXX.ActiveConnection = Nothing
    Resume ExitHere
    
End Sub

Thanks in advance,

Dan

Why? i.e. why have 2 statements to do one?

Read this code and decide what the two msgboxes should say. Then run it.

Sub x()
    Dim col As New Collection
    
    MsgBox col Is Nothing
    Set col = Nothing
    MsgBox col Is Nothing
End Sub