Public Function Ranks()
On Error GoTo ErrorHandler:

    Const intMaxCols As Integer = 5                 'number of spreadsheet columns required

    Dim intBrokerMax As Integer, intCategoryMax As Integer
    Dim strSQL As String
    Dim intLoop As Integer
    Dim cn As New ADODB.Connection
    Dim rstCat As ADODB.Recordset                   'category list
    Dim rstBroker As ADODB.Recordset                'broker list

    'cn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;Data Source=H:\Development\Trading\Trading_AAR2005\764\764.mdb"
    cn.ConnectionString = CurrentProject.Connection
    cn.Open
    
    'get broker list
    strSQL = "SELECT fl_brokerCode, fs_brokerNameinUID FROM qryBrokerList"
    Set rstBroker = cn.Execute(strSQL)

    intBrokerMax = rstBroker.RecordCount                'total num of brokers to loop through

    Do Until rstBroker.EOF
        Debug.Print rstBroker!fl_brokercode
        Debug.Print rstBroker!fs_brokerNameinUID
    Loop

    cn.Close

Exit Function
ErrorHandler:
    MsgBox "Error in Ranks creation function " & Err.Number & " " & Err.Description
    Debug.Print "Error in Ranks creation function " & Err.Number & " " & Err.Description
End Function

Public Function Ranks()
On Error GoTo ErrorHandler:

    Const intMaxCols As Integer = 5                 'number of spreadsheet columns required

    Dim intBrokerMax As Integer, intCategoryMax As Integer
    Dim strSQL As String
    Dim intLoop As Integer
    Dim cn As ADODB.Connection
    Dim rstCat As ADODB.Recordset                   'category list
    Dim rstBroker As ADODB.Recordset                'broker list

    Set cn = CurrentProject.Connection
    
    'get broker list
    strSQL = "SELECT fl_brokerCode, fs_brokerNameinUID FROM qryBrokerList"
    Set rstBroker = cn.Execute(strSQL)
    rstBroker.MoveLast
    intBrokerMax = rstBroker.RecordCount                'total num of brokers to loop through
    rstBroker.MoveFirst

    Do Until rstBroker.EOF
        Debug.Print rstBroker!fl_brokercode
        Debug.Print rstBroker!fs_brokerNameinUID
    Loop

    cn.Close

Exit Function
ErrorHandler:
    MsgBox "Error in Ranks creation function " & Err.Number & " " & Err.Description
    Debug.Print "Error in Ranks creation function " & Err.Number & " " & Err.Description
End Function

    Set rstBroker = cn.Execute(strSQL)

    intBrokerMax = 0

    Do Until rstBroker.EOF
        Debug.Print rstBroker!fl_brokercode
        Debug.Print rstBroker!fs_brokerNameinUID
        intBrokerMax = intBrokerMax + 1
    Loop
  
    ' now intBrokerMax has the count of the rows in the RS.

Public Function Ranks()
On Error GoTo ErrorHandler:

    Const MaxCols As Integer = 5                 'number of spreadsheet columns required

    Dim BrokerMax As Integer, CategoryMax As Integer
    Dim strSQL As String
    Dim cn As ADODB.Connection
    Dim rstCat As New ADODB.Recordset                   'category list
    Dim rstBroker As New ADODB.Recordset                'broker list

    Set cn = CurrentProject.Connection
    
    'get broker list
    strSQL = "SELECT fl_brokerCode, fs_brokerNameinUID FROM qryBrokerList"
    With rstBroker
        .Open strSQL, cn, adOpenStatic, adLockReadOnly
    End With
    
    BrokerMax = 0

    Do Until rstBroker.EOF
        Debug.Print rstBroker!fl_brokercode
        Debug.Print rstBroker!fs_brokerNameinUID

        'write to array here

        BrokerMax = BrokerMax + 1
        rstBroker.MoveNext
    Loop

    Debug.Print BrokerMax

    cn.Close

Exit Function
ErrorHandler:
    MsgBox "Error in Ranks creation function " & Err.Number & " " & Err.Description
    Debug.Print "Error in Ranks creation function " & Err.Number & " " & Err.Description
End Function

quote:

---

Originally posted by jhermiz

You may want to clean your resources in your Exit Function Handler:

Set cn = Nothing
Set rstBroker = Nothing

---

quote:

---

Originally posted by jsmith8858

>>Jeff not sure why it is a pet peeve if it is not causing any types of problems but only ensures that the memory is released.

That is the pet peeve -- people think the bold part is true and it is not. If you have well-structured code it is not necessary and has no effect. Just remember that setting object variables to Nothing does NOT explicitly destroy that object -- it simply decrements the reference count on that object by 1. (which is the same thing that happens when a variable goes out of scope)

- Jeff

---