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


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)
    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
        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
                    Exit For
                End If
            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
      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)
    End With
    If Not rs Is Nothing Then
    End If
    Set rs = Nothing

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

End Sub

Back to Access 2000 How To’s Series Home

Latest Articles