Excel VBA - Loop through recordsets ADODB with For Loop Error - Help

Hi all,

Basically, to reduce overhead and repetitive code, I need to loop through SQL query definitions 10 times. I have ten unique recordset.source strings. With each query, fields are pulled to userform... BUT I cannot seem to get the for loop to allow this. I get error "Operation is not allowed when the object is closed"... .. .. .. .. . . . Its not closed. Im opening it every time.

Please see my code below:

Sub getLines(linenum As Integer, qnum As String)

Dim CompanyConn As ADODB.Connection
Set CompanyConn = New ADODB.Connection
Dim CompanyData As New ADODB.Recordset
Set CompanyData = New ADODB.Recordset
Dim companyfields As ADODB.field
Dim usf As MSForms.UserForm
Set usf = Edit_Quote
Dim connectionstring As String, xsource As String, i As Integer
connectionstring = Sheets("TABLES").Range("C1").value
CompanyConn.connectionstring = connectionstring

On Error GoTo ErrorHandler

        For i = 1 To 2 'i need to do this ten times, 2 is for debugging
        Dim j As String
        j = CStr(i)
        
        Select Case i
            Case 1: xsource = 'insert first query string here
            Case 2: xsource = 'insert second query string here
        End Select
        CompanyConn.Open
        CompanyData.Open xsource, CompanyConn, adOpenForwardOnly, adLockReadOnly
        
        With CompanyData
            usf.Controls("tbPn" & j) = .Fields("partnumber")
            usf.Controls("tbRev" & j) = .Fields("rev")
            usf.Controls("tbDes" & j) = .Fields("description")
            usf.Controls("tbQty" & j) = .Fields("quantity")
            usf.Controls("tbPrice" & j) = .Fields("price")
        End With
        
        CompanyData.Close
        CompanyConn.Close
        Next

    GoTo CloseConnection
   
ErrorHandler:
    Call DataErrorHandler("ReadFunctions", "getLines")
   
CloseConnection:
    CompanyData.Close
    CompanyConn.Close
End Sub

Thank you for the help!