Access TreeView-ListView Basics
March 21, 2008
Ok, this truly is a 'basics' article. I've never been fond of the TreeView & ListView because the library file that exposes these controls, MSComCtl.ocx, always seemed to behave a little quirky. It was as if I could never be sure which version of the controls any given user would have on their computer and while the controls worked fine for me, users constantly experienced issues. So, I gave up on them ... but maybe too soon.
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:
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:
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.
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.