Access 2000 How To’s: Adding a Tree View Control and Filter

Overview

This Access 2000 How To’s article explains how to load data into a treeview control. Nodes in a treeview have a parent key and a child key. In this
example, the first level of nodes contains process ids and titles. In the second level, Activities represent dependant child nodes. One or more activies are associated as children to the a process.

When the user clicks on a node, its key is parsed and the contents determine the type of node: process or activity. Depending on the type of node, a filter criteria is applied to a join between the process and activity table.

Building the Control

Option Compare Database
Option Explicit

Private Sub FindTreeCtrl_NodeClick(ByVal Node As Object)
    'AT
    Dim iOffset
    Dim iLength
    Dim sId
    Dim i
    Dim ch
    
    iOffset = InStr(Node.Key, "AT")
    If (iOffset > 0) Then
        iLength = Len(Node.Key)
        sId = Mid(Node.Key, iOffset + 2, iLength - iOffset + 1)
        'Filter for a task
        Filter = "Id=" & sId
        FilterOn = True
    Else
        iOffset = InStr(Node.Key, "P")
        
        'Filter for a process
        If iOffset > 0 Then
            iLength = Len(Node.Key)
        
            For i = iOffset To iLength
                ch = Mid(Node.Key, iOffset + i, 1)
                If IsNumeric(ch) = True Then
                    sId = sId & ch
                Else
                    Exit For
                End If
            Next
         
            Filter = "ProcessId=" & sId
            FilterOn = True
        End If
        
    End If
        
End Sub

Private Sub Form_Load()
    Dim objNode As Node
    Dim dbs As Object
    Dim rs As Object
    Dim rs2 As Object
    Dim sProcessId As String
    Dim sProcessName As String
    Dim sActivityId As String
    Dim sActivityTitle As String
    
    Set dbs = CurrentDb
    
    Set rs = dbs.Openrecordset("select * from processes")
    
    With FindTreeCtrl
      .Nodes.Clear
      
      Set objNode = .Nodes.Add(, , "root", "Activities")
      objNode.Expanded = True
      
      Set objNode = .Nodes.Add("root", tvwChild, "TT", "Processes")
      objNode.Expanded = True
      
      Do While Not rs.EOF
            sProcessId = "P" & rs("processid")
            sProcessName = "" & rs("processName")
      
            Set objNode = .Nodes.Add("TT", tvwChild, sProcessId, sProcessName)
            
            Set rs2 = dbs.Openrecordset("select * from activities where processid=" & rs("processid"))
            Do While Not rs2.EOF
                sActivityId = sProcessId & "AT" & rs2("id")
                sActivityTitle = "" & rs2("activitytitle")
                Call .Nodes.Add(sProcessId, tvwChild, sActivityId, sActivityTitle)
                rs2.MoveNext
            Loop
        rs.MoveNext
      Loop
    End With
    
    If Not rs Is Nothing Then
        rs.Close
    End If
    Set rs = Nothing

    If Not rs2 Is Nothing Then
        rs2.Close
    End If
    Set rs2 = Nothing

End Sub

Back to Access 2000 How To’s Series Home