Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Oct 17, 2002

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

By David Nishimoto


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

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM