VB - Database Driven TreeView

[JCScoobyRS]

This code will build a TreeView from a Database with an infinite number of levels and will retain the EXACT TreeView Structure as when built. This code would need to be modified for your database BUT the method in which it builds the TreeView can be used in any situation. Here you go:

'TreeView Functions/Subs
Public Sub Create_Tree()
    Dim TreeRec As ADODB.Recordset
    Dim TreeStr As String
    Dim TreeNode As Node
    Dim TreeKey As String
    Dim TreeText As String
    
    Set TreeCon = CreateObject("ADODB.Connection")
    TreeCon.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
                             "Data Source=QSDB;" & _
                             "User ID=QSDB_USER;" & _
                             "Password=5star5;"
    
    'Open Connection
    TreeCon.Open
    
    tvPS.Nodes.Clear
    tvPS.Nodes.Add , , gbtxtPartName & "_PART", gbtxtPartName 'gbtxtPartName is a variable that houses the ROOT Nodes Text
    
    Set TreeRec = New ADODB.Recordset
    TreeStr = "SELECT RT_IMKEY,RT_WCKEY Item,RT_SEQNUM SeqNum,RT_OP_NUM OpNum,'_PRO' IconType FROM RT WHERE RT_IMKEY = '" & gbtxtPartName & "'" & _
               "UNION " & _
               "SELECT PS_IMKEY,PS_CPKEY Item,PS_SEQNUM SeqNum,PS_OP_NUM OpNum,'_MAT' IconType FROM PS WHERE PS_IMKEY = '" & gbtxtPartName & "' Order By OpNum"
    TreeRec.Open TreeStr, TreeCon, adOpenStatic, adLockOptimistic, adCmdText
    TreeRec.MoveFirst
    Do While Not TreeRec.EOF 'Adds current level of nodes
        Set TreeNode = tvPS.Nodes.Add((gbtxtPartName & "_PART"), tvwChild, gbtxtPartName & "_" & TreeRec!Item & "_" & TreeRec!OpNum & TreeRec!IconType, TreeRec!Item) 'Sets new node as current node for next Sub
        TreeKey = TreeNode.Key
        TreeText = TreeNode.Text
        TreeRec.MoveNext
    Loop
    Call Walk_Tree(TreeNode) 
End Sub
Private Sub Walk_Tree(ByVal TreeNode As Node)
    Dim i As Integer
    Set TreeNode = TreeNode.Parent
    Set TreeNode = TreeNode.Child
    
    Do 'Walks TreeView
        Call Query_Node(TreeNode)
        Set TreeNode = TreeNode.Next
    Loop Until TreeNode Is Nothing
End Sub
Private Sub Query_Node(ByVal TreeNode As Node)
    Dim TreeNode1 As Node
    Dim NodeRec As ADODB.Recordset
    Dim NodeStr As String
    
    Set NodeRec = New ADODB.Recordset
    NodeStr = "SELECT RT_IMKEY,RT_WCKEY Item,RT_SEQNUM SeqNum,RT_OP_NUM OpNum,'_PRO' IconType FROM RT WHERE RT_IMKEY = '" & TreeNode.Text & "'" & _
               "UNION " & _
               "SELECT PS_IMKEY,PS_CPKEY Item,PS_SEQNUM SeqNum,PS_OP_NUM OpNum,'_MAT' IconType FROM PS WHERE PS_IMKEY = '" & TreeNode.Text & "' Order By OpNum"
    NodeRec.Open NodeStr, TreeCon, adOpenStatic, adLockOptimistic, adCmdText
    
    If NodeRec.RecordCount = 0 Then 'Checks against database for Children
        NodeRec.Close 
        Exit Sub ' If no Children, exit sub, continue walking tree
    Else
        NodeRec.MoveFirst
        Do While Not NodeRec.EOF 'Adds nodes for current level
            Set TreeNode1 = tvPS.Nodes.Add(TreeNode.Key, tvwChild, TreeNode.Text & "_" & NodeRec!Item & "_" & NodeRec!OpNum & NodeRec!IconType, NodeRec!Item)
            NodeRec.MoveNext
        Loop
    End If
    Set TreeNode = TreeNode1
    Call Walk_Tree(TreeNode) 'Calls Recursively
End Sub

The database Structure for this example is as follows:|

PS Table
------------
PS_SEQNUM = unique number
PS_OP_NUM = number
PS_IMKEY = Parent Node
PS_CPKEY = Child Node

(Example)
PS_IMKEY = Jeremy
PS_CPKEY = Josh

(Tree)
Jeremy
|_Josh

RT Table
-----------
RT_SEQNUM = unique number
RT_OP_NUM = number
RT_IMKEY = Parent Node
RT_WCKEY = Child Node

(Example)
PS_IMKEY = Jeremy
PS_CPKEY = Josh
RT_IMKEY = Jeremy
RT_WCKEY = Kick

(Tree)
Jeremy
|_Josh
|_Kick

Hope this all makes sense, Jeremy