Recently I've been working with the TreeView & ListView controls on some utilities we use in-house, and the problems I remember from my Access 97 days have not manifest themselves. I'm sure I'll get email from developers who never encountered problems with these controls in Access 97 applications, but alas, that was my experience. But all that's water under the bridge, and I am finally, after all these years, embracing this family of controls.

However, as a newcomer to the TreeView & ListView control, I have no depth of wisdom to impart. There are resources a plenty on the web that describe advanced techniques. This article will focus on getting started. Click here to download the sample code.

Reference The Control

The first step is critical. You need to inform your Access application that you plan to use these controls, and point it to the Microsoft Library wherein they are contained. On all the Access installs where I develop, that ends up being in the Windows\System32 directory:

 C:\Windows\System32\MSComCtl.ocx 

To add the reference, you need to open any VBA code module (the keystroke Ctl+G will get you there), and select References from the Tools menu. With most libraries, you simply need to scan the list for the one you want. You're looking for the Microsoft Windows Common Controls library, but if your install is like mine, you won't find it in the list. You're going to have to browse to it, thus the need for the path given above. The process is illustrated in the screen shot below. Find the file, click Open and then click OK on the References dialog, and we're ready to code.

Build the Form

The first step, and not a trivial one, is to get the control(s) on your form. There are two ways to do this. You can click on the "More Controls" button on the ToolBox toolbar, which will pop up a scrollable list of available ActiveX controls. Scan the list for Microsoft TreeView Control or Microsoft ListView Control and select it.

Alternatively, you can choose ActiveX Control from the Insert menu and work with a smaller, more manageable pick list which gives you feedback about the control you are about to plunk on your form. Either way, you end up with a special object that has, as we shall see, special properties.

This new control (TreeView shown in the screen shot) exposes a special properties dialog box, in addition to the standard properties that Access exposes for all controls, such as Height & Width, Top & Left, etc. To expose these special properties, right-click the control and choose TreeCtl Object >> Properties from the menu. This opens the special properties dialog shown in the screen shot below.

Setting these properties changes the way the control appears and behaves. The ListView control exposes additional tabs to control things like the widths for column headers, sorting and the like. I had a hard time finding help, as it did not seem to be installed. A search of the newsgroups revealed the files I needed, and where to get them, but even then, it didn't seem to run on my machine. Here's a summary of what I found. The version 5 files are included in the download, but here's a site where you can download the latest files, if you can get them to work on your computer:

    http://latium.dpi.ufv.br/downloads/C++IntroductoryEdition/VCB600ENU1/COMMON/HELP/?C=N;O=D
      Ver 6 ... CMCTL198.CHI
      Ver 6 ... CMCTL198.CHM
      Ver 5 ... comctl1.hlp
      Ver 5 ... comctl1.cnt

There's so much more that could be written here ... and so much more has. Remember, this article is just to get you started. There are many examples on the web, and MSDN even has some helpful articles. For now, having placed the controls on a form, let's look at one example of using these controls.

The Code

Below is the entire code listing for the demo application. The only trick to it is the adding of what are called 'Nodes'. Node keys must be unique, and from my experience, must be perceived by the control as being non-numeric. Accordingly, I've attached the bar character (|) to the ID numbers to ensure they appear to be text values.

The other thing to note in the code is how string values are used for comparison to determine when it's time to create a new node. As we begin looping through the recordset, the previous value for strPName (publisher name) is compared with the current recordset value. If it's different, a new node is created and the variables are updated. The same process is followed for the Store Node and the Author Node. After that, it's just looping.

You won't find any special events for the control, but they do exist. Accordingly, you simply have to code for them, as with this event procedure:

 Private Sub tvwMyTreeView_NodeClick(ByVal Node As Object) 

The same is true for the ItemCheck event of the ListView control. However, the ListView control differs from the TreeView in that there are no nodes ... just items, which are added through the ListItems.Add method. The best way to learn about how this works is to download the demo app, and step through the code. That's pretty much what I had to do my first time.

Private Sub LoadMyTreeView()
    On Error GoTo Err_Handler
    Dim strSQL As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    
    Dim nod As Node
    Dim iNode As Integer
    Dim strPName As String
    Dim strPKey As String
    Dim strSName As String
    Dim strSKey As String
    Dim strAName As String
    Dim strAKey As String
    
    
    ' Clear the treeview nodes
    tvwMyTreeView.Nodes.Clear
    Set dbs = CurrentDb
    strSQL = "SELECT pub_name, stor_name, au_name, pub_id, stor_id, au_id " & _
             "FROM qryPublisherStoreAuthor ORDER BY pub_name, stor_name;"
    Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
    
    If Not rst.BOF Then rst.MoveFirst
    Do Until rst.EOF
         ' Add Publisher node
        If strPName <> rst!pub_name Then
            strPKey = rst!pub_id & "|"
            strPName = rst!pub_name
            Set nod = tvwMyTreeView.Nodes.Add(, , strPKey, strPName, "publisher_open", "publisher_closed")
            
            strSName = ""
        End If
        
        ' Add Store node
        If strSName <> rst!stor_name Then
            strSKey = strPKey & rst!stor_id & "|"
            strSName = rst!stor_name
            Set nod = tvwMyTreeView.Nodes.Add(strPKey, tvwChild, strSKey, strSName, "store", "store")
            nod.Tag = rst!stor_id
            
            strAName = ""
        End If
        
        ' Add Author node
        If strAName <> rst!au_name Then
            strAKey = strSKey & rst!au_id & "|"
            strAName = rst!au_name
            Set nod = tvwMyTreeView.Nodes.Add(strSKey, tvwChild, strAKey, strAName, "author", "author")
            
            nod.Tag = rst!au_id
        End If
        
        rst.MoveNext
    Loop
    
    ' Initialize ListView to empty recordset.
    LoadMyListView ("")
    
Exit_Here:
    Set dbs = Nothing
    Set rst = Nothing
    Exit Sub
Err_Handler:
    MsgBox Err.Description, vbCritical, "ERROR"
    Resume Next
End Sub
Private Sub tvwMyTreeView_NodeClick(ByVal Node As Object)
    On Error GoTo Err_Handler
        
    Dim lngStor_id As Long
    Dim strAu_Id As String
    
    ' Grab the stor_id from the node's tag property
    ' If null, set to zero.
    strAu_Id = Nz(Node.Tag, 0)
    
    ' Given we have a non-zero ID, load the listview control
    If strAu_Id <> "" Then
        Call LoadMyListView(strAu_Id)
    End If
    
Exit_Here:
    Exit Sub
Err_Handler:
    MsgBox Err.Description, vbCritical, "ERROR"
    Resume Next
End Sub
Private Sub LoadMyListView(ByVal au_id As String)
    On Error GoTo Err_Handler
    Dim strSQL As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim lstItem As ListItem
    Dim strItem As String
    Dim strKey As String
    Dim intCount As Integer
    
    
    ' Remove all existing items
    While Me!lvwMyListView.ListItems.Count > 0
        Me!lvwMyListView.ListItems.Remove (1)
    Wend
    
    'open recordset of filtered Title/Authors
    Set dbs = CurrentDb
    strSQL = "SELECT title, title_id FROM qryTitleAuthor WHERE [au_id]='" & au_id & "'"
    Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
    
    'add each entry in the rs to the listview
    If Not rst.BOF Then rst.MoveFirst
    Do Until rst.EOF
        strItem = rst!title
        strKey = "ID=" & rst!title_id
        
        Set lstItem = Me!lvwMyListView.ListItems.Add(, strKey, strItem)
        
        intCount = intCount + 1
        rst.MoveNext
    Loop
    
    If intCount > 0 Then Me!lvwMyListView.ListItems(1).Selected = True
    
Exit_Here:
    Set dbs = Nothing
    Set rst = Nothing
    Exit Sub
Err_Handler:
    MsgBox Err.Description, vbCritical, "ERROR"
    Resume Next
End Sub
Private Sub lvwMyListView_ItemCheck(ByVal Item As Object)
    On Error GoTo Err_Handler
    Dim strSQL As String
    Dim iCurr As Integer
    Dim strKey As String
    Dim lngKeyID As Long
    For iCurr = 1 To Me!lvwMyListView.ListItems.Count
        If Me!lvwMyListView.ListItems(iCurr) = Item Then
            strKey = Me!lvwMyListView.ListItems(iCurr).Key
            MsgBox strKey, vbInformation, "Note"
        End If
    Next
Exit_Here:
    Exit Sub
Err_Handler:
    MsgBox Err.Description, vbCritical, "ERROR"
    Resume Next
End Sub

Barely scratched the Surface

There's a lot to these controls, way more than what's been described here. Below are a couple of MSDN references, and there are more to be had. The newsgroups were a great source for additional code samples and for suggestions on how to overcome errors in describing unique Node Keys, which seems to be the most common pitfall of the TreeView control.

Fill a TreeView Recursively
TreeView Drag and Drop

No, I'll never be a Common Control guru, but such developers exist. I work with one such expert, and he has shown me an app that programmatically changes icons, column headers, sort orders and much, much more. I'm in awe of what can be accomplished by the TreeView and ListView controls. It may be just the tool you need to finish your application, but be advised, the learning curve is anything but trivial.