Visual Basic For Applications: A sub to load table records into a tree

Platform:Visual Basic For Applications
Task:A sub to load table records into a tree
Discussion:
I wanted a quick way to load a table's records into a treeview control
Example:
Private Sub LoadDBTableRecordsAsNodes(ParentNodeKey As String, TableName As String, PrimaryKeyColumn As String, OrderByClause, NodeTextColumn As String, MasterTableForeignKeyColumn As String, MasterTableForeignKeyColumnValue As Variant, ShowAllRecords As Boolean)
    'On Error GoTo Error:
    'dim variables
    Dim nodeCurrent As Node
    Dim sql As String
    Dim whereclause As String
    
    'determine whether to filter the records or show them all
    If ShowAllRecords = False Then
        'filter the records
        'test the master table's id to see if it is numeric or string
        If IsNumeric(MasterTableForeignKeyColumnValue) = True Then
            'it's numeric
            whereclause = " WHERE " & MasterTableForeignKeyColumn & " = " & MasterTableForeignKeyColumnValue
        Else
            'it 's a string
            whereclause = " WHERE " & MasterTableForeignKeyColumn & " = '" & Trim(MasterTableForeignKeyColumnValue) & "'"
        End If
    End If
    
    ' make a recordset of the requested records
    Set rs = New ADODB.Recordset
    sql = "SELECT * FROM " & TableName & whereclause & " ORDER BY " & OrderByClause
    MsgBox sql
    rs.Open sql, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    
    'loop through the records and add them as nodes
    If rs.RecordCount > 0 Then
        Do While Not (rs.EOF)
            Set nodCurrent = treeNav.Nodes.Add(ParentNodeKey, tvwChild, PrimaryKeyColumn & " = " & rs(PrimaryKeyColumn), rs(NodeTextColumn))
            rs.MoveNext
        Loop
    End If
    rs.Close
    Exit Sub
Error:
    MsgBox Err.Description
End Sub
Back to Index