My Treeview Project | Episode 6: Images for the Nodes

This (at long last) is Episode 6 of My Treeview Project.

• In Episode 1 we started with the "Hello World" treeview
• In Episode 2 we built a treeview that showed some simple data from the Northwind Traders sample data
• In Episode 3 we learned how to change the appearance of the treeview
• In Episode 4 we added Expand All and Collapse All buttons that interact with the treeview
• And in Epsiode 5 we finally got the treeview to do something useful- we used it to select records to view and edit.

Here's this episode's treeview:

Download the finished database

In Episode 6 you'll see two things. The main focus of the episode is the use of images for the nodes in the treeview. As well, I'll demonstrate a different approach to the code that reads records from the tables and creates the nodes.

A couple of caveats before I start. First- I never claimed to be an artist. The 10 small images I created for use in this episode are just good enough that you can tell them apart when they're used in the treeview. If you want icon-quality images don't look here. Second- I've kept the code simple at the expense of performance. On my PC the treeview takes a few seconds to load 3,000 records using the Northwind sample orders data. If this was a real application I'd tweak it a little to make it more efficient. For a tutorial though, it should more than suffice.

Getting Started

This episode's treeview uses the Northwind sample data again. It shows each customer, their orders, and the products on each order. Images beside each node of the tree help distinguish customer lines from order lines and product lines. They also show what category each product is from.

For this episode we're leaving the Categories and Product treeview behind us and starting from scratch. The completed database is available for download here, or follow along and we'll create it together. You'll need the following tables from the Northwind Traders sample database: Categories, Customers, Order Details, Orders, and Products. If you don't have a copy you can download it here.

You'll need one query in addition to the tables. Call it qryOrderLines and create it with the following SQL:

SELECT 
  [Order Details].OrderID, 
  Products.ProductID, 
  [Order Details].UnitPrice, 
  Quantity, Discount, 
  ProductName, 
  CategoryName
FROM 
  Categories INNER JOIN 
  ( [Order Details] INNER JOIN 
    Products ON [Order Details].ProductID = Products.ProductID) 
  ON Categories.CategoryID = Products.CategoryID
;

Now let's get started on the Treeview! Create a blank form. Before adding a treeview control, add an imagelist control. An imagelist is a kind of a "helper" control that holds images ready to be used in the treeview. It comes in the same ActiveX component as the treeview and is added in much the same way. Select Insert/Activex control, and find the ImageList control. (The exact steps will vary slightly in Access 2007.) Place the imagelist control anywhere you like on the form- the users won't see it. Call the imagelist xMyTreeviewImages. The name will be important later because we'll have to connect the treeview and imagelist together.

To be of any value we'll have to load images into the imagelist control. If you right-click the imagelist and select ImageListCtrl Object and then Properties, on the Images tab you can upload images to the imagelist so that you can then use them in your treeview. We'll load our images from code, though (more on that in a few minutes). I think that's a good idea because it's more repeatable if you ever have to rebuild the imagelist.

Now add a treeview control to your form, the same way you did in previous episodes. Call it xMyTreeview.

Now on to the VBA code. First let's look at the code to load images into the treeview. In the download file linked above are ten bitmap files. You'll need these in the same folder as your database for this code to work. Enter the following code into the form's module.

'===================================================================================================
' Sub SetUpImageList
'
' Load the images for the treeview to the imagelist control
' Imagelist controls are not visible in form view- their only purpose is to hold images for
' other controls
' Images can be accessed by index number or by key
' Images can be loaded in design mode (right click on the image list control and select
' imagelistctrl object/properties
' Images can also be loaded in code
'
Private Sub SetUpImageList()
    Dim strFolder As String
    
    ' retreive the database's folder as the folder for the images
    strFolder = CurrentProject.Path & "\"
  
    With Me.xMyTreeviewImages
        With .ListImages
            .Clear
            ' load images to image list, specifying a key that will be used to connect the image
            ' to the nodes later on
            ' each image has to be converted to picture data using LoadPicture()
            
            ' Customer and Order nodes will use a picture appropriate to the type of node
            .Add Key:="Customer", Picture:=LoadPicture(strFolder & "Customer.bmp")
            .Add Key:="Order", Picture:=LoadPicture(strFolder & "Order.bmp")
            
            ' Order line nodes will use an image that relates to the product's category, keyed
            ' using the category's name
            .Add Key:="Beverages", Picture:=LoadPicture(strFolder & "Beverages.bmp")
            .Add Key:="Condiments", Picture:=LoadPicture(strFolder & "Condiments.bmp")
            .Add Key:="Confections", Picture:=LoadPicture(strFolder & "Confections.bmp")
            .Add Key:="Dairy Products", Picture:=LoadPicture(strFolder & "Dairy Products.bmp")
            .Add Key:="Grains/Cereals", Picture:=LoadPicture(strFolder & "Grains.bmp")
            .Add Key:="Meat/Poultry", Picture:=LoadPicture(strFolder & "Meat.bmp")
            .Add Key:="Produce", Picture:=LoadPicture(strFolder & "Produce.bmp")
            .Add Key:="Seafood", Picture:=LoadPicture(strFolder & "Seafood.bmp")
        End With
    End With
End Sub

Hopefully this code is fairly straightforward. LoadPicture() is a VBA function that loads a picture to an ActiveX control. The code loads each required picture, giving it a Key, which we'll use later to refer to the specific pictures we want on each node. Images can be retreived by index or key- I think keys are more useful. The keys we're using for the category images match the category names exactly- that will make finding images for product nodes easy.

As usual we have code to set-up the treeview. Here's how that goes in this episode:

'===================================================================================================
' Sub SetupTreeview
'
' Inititalize the treeview options- these can also be set in design mode- right click on the
' treeview, select Treectrl object/properties
'
Private Sub SetupTreeview()
    With Me.xMyTreeview
        .Nodes.Clear
        .Style = tvwTreelinesPlusMinusPictureText
        .LineStyle = tvwRootLines
        .Indentation = 240
        .Appearance = ccFlat
        .HideSelection = False
        .BorderStyle = ccFixedSingle
        .HotTracking = True
        .FullRowSelect = False
        .Checkboxes = False
        .SingleSel = False
        .Sorted = False
        .Scroll = True
        .LabelEdit = tvwManual
        .Font.Name = "Verdana"
        .Font.Size = 9
        .ImageList = Me.xMyTreeviewImages.Object
    End With
End Sub

New is the line: .ImageList=Me.xMyTreeviewImages.Object. This line associates the imagelist with the treeview so that whenever the treeview needs an image it knows where to go.

I said above that the way we'd build the nodes would be different. Previously we added all the category nodes, and then all the product nodes. That approach works well in many cases. For this treeview, though, we'll add one customer, add one order for that customer, and add all the products for that order. Then we'll do the next order and the next, and when we run out of orders we'll do the next customer.

Adding Customer Nodes

Here's the code to add the customer nodes.

'===================================================================================================
' Sub CreateCustomerNode
'
' Add a node to the treeview for each customer, as well as adding each customer's orders
'
Private Sub CreateCustomerNodes()
    Dim rst As DAO.Recordset ' recordset for customer data
    Dim strThisCustomerID As String ' customerID for this customer
    Dim strCustomerCompanyName As String ' Company Name for this customer
    Dim nodNewCustomerNode As MSComctlLib.Node ' new customer node added to the tree
    
    ' open the recordset for customers
    Set rst = CurrentDb.TableDefs!Customers.OpenRecordset
   
    ' loop through the rows in the recordset
    If Not (rst.BOF And rst.EOF) Then ' make sure there are records to process
        rst.MoveFirst
        Do Until rst.EOF
            strThisCustomerID = rst!CustomerID
            strCustomerCompanyName = rst!CompanyName
            
            ' display a progress message in the status bar
            SysCmd acSysCmdSetStatus, "Adding node for customer " & strCustomerCompanyName
            
            ' add the node, specifying text and the image to use (by key)
            ' the node will not have a key as there's no need to access it by key
            Set nodNewCustomerNode = Me.xMyTreeview.Nodes.Add(Text:=strCustomerCompanyName, _
                    Image:="Customer")
            
            With nodNewCustomerNode
                .Expanded = True
                ' set a Tag we can use to identify the customer the node is for
                .Tag = "C" & CStr(strThisCustomerID)
            End With
            
            ' add order nodes for this customer
            AddOrderNodes nodCustomer:=nodNewCustomerNode, strCustomerID:=strThisCustomerID
            
            rst.MoveNext
        Loop
    End If
    rst.Close
    
    ' clear the status message
    SysCmd acSysCmdClearStatus
End Sub

We open a recordset for customers, and add a node for each customer. Hopefully fairly familiar. Here's the line that adds the nodes:

Set nodNewCustomerNode = Me.xMyTreeview.Nodes.Add(Text:=strCustomerCompanyName, _
    Image:="Customer")

Some things to note:

1. We don't give the nodes a key this time. Nodes only need a key if you need to find them later. Our previous approach needed keys because each time we added a product we used the key for the corresponding category node to find a node to attach the product to. This approach doesn't need that. We also know we will have products showing up over and over in the tree (because different customers order the same product) so we'd have a problem keeping our keys unique.

2. The Image parameter isused to tell the tree to find the image we added with the key Customer and use that image for this node.

3. We do Set nodNewCustomerNode= to assign this newly created node to a variable so we can refer to it later.

Treeview nodes have a tag property- a free form text property that you can use to store anything you need to store about the node. It doesn't have to be unique, like the key does, but you can't use it to find nodes like you can the key. You can't specify a tag when you create the node, but a couple of line's further down we have this block:

With nodNewCustomerNode
    .Expanded = True
    ' set a Tag we can use to identify the customer the node is for
    .Tag = "C" & CStr(strThisCustomerID)
End With

We use the variable we set to represent the newly created node to expand the node and to set its tag property, storing the customer id. In this module we won't go back and use the info we stored in the tag, but if we wanted to do something when a user clicked on a customer node we'd retrive the id from the tag.

Adding Order Nodes

Now take a look at the line

AddOrderNodes nodCustomer:=nodNewCustomerNode, strCustomerID:=strThisCustomerID

This calls our next procedure to add Orders for this customer. We pass that procedure a variable that points to the node we just created, so that the new nodes can be attached to it. And we pass it the customer id so that it knows which orders to create nodes for. Here's the code for that procuedure:

'===================================================================================================
' Sub AddOrderNodes
'
' Add the order nodes for one customer, as well as the corresponding order line nodes
'
' Parameters:
'   nodCustomer :: the customer node that the order lines are to be added under
'   strCustomerID :: the id of the customer whose orders should be added
'
Private Sub AddOrderNodes(ByVal nodCustomer As MSComctlLib.Node, ByVal strCustomerID As String)
    Dim strSQL As String ' SQL to be used to find order records
    Dim rst As DAO.Recordset ' recordset for order data
    Dim lngOrderID As Long ' order id for the order being added
    Dim strOrderText As String ' text to be used for the order node
    Dim nodNewOrderNode As MSComctlLib.Node ' new order node added to the tree
    
    ' open the recordset for products
    strSQL = "SELECT * FROM Orders WHERE CustomerID='" & strCustomerID & "'"
    Set rst = CurrentDb.OpenRecordset(strSQL)
    
    ' loop through the rows in the recordset
    If Not (rst.BOF And rst.EOF) Then
        rst.MoveFirst
        Do Until rst.EOF
            lngOrderID = rst!OrderID
            strOrderText = Format(rst!OrderDate, "yyyy-mm-dd")
            
            SysCmd acSysCmdSetStatus, "Adding node for order " & CStr(lngOrderID)
            
            ' add the node, specifying text, parent node to add the node under,
            ' and the image to use (by key)
            ' the node will not have a key as there's no need to access it by key
            Set nodNewOrderNode = Me.xMyTreeview.Nodes.Add(Relationship:=tvwChild, _
                    Relative:=nodCustomer, Text:=strOrderText, Image:="Order")
            
            With nodNewOrderNode
                .Expanded = True
                .Tag = "O" & CStr(lngOrderID)
            End With
            
            ' add the order line nodes for this order
            AddOrderLineNodes nodOrder:=nodNewOrderNode, lngOrderID:=rst!OrderID
            rst.MoveNext
        Loop
    End If
    rst.Close
End Sub

The lines

strSQL = "SELECT * FROM Orders WHERE CustomerID='" & strCustomerID & "'"
Set rst = CurrentDb.OpenRecordset(strSQL)

find order data matching this customer (based on the customer id passed from the previous procedure) and from there the code should be a lot like code we've done before. Read through the recordset, create nodes. The text of the node is calculated using the order date. It gets a tag just like the customer node did. And it displays an image appropriate for an order. The node it should be attached to (the Relative parameter) is determined using the variable nodNewOrderNode which was also passed from the previous procedure. And just like orders were added for customers, order lines will be added to the order by calling another procedure, AddOrderLines.

Adding Order Line Nodes

And here's the last big procedure- it adds order line nodes attached to the order node.

'===================================================================================================
' Sub AddOrderLineNodes
'
' Add the order nodes for one customer, as well as the corresponding order line nodes
'
' Parameters:
'   nodOrder :: the order node that the order lines are to be added under
'   lngOrderID :: the id of the order whose detail lines should be added
'
Private Sub AddOrderLineNodes(ByVal nodOrder As MSComctlLib.Node, ByVal lngOrderID As Long)
    Dim strSQL As String ' SQL to be used to find order lines
    Dim rst As DAO.Recordset ' recordset for order line data
    Dim lngProductID As Long '  product ID for the detail line being added
    Dim strOrderLineText As String ' text for the order line to be added
    Dim strImageKey As String ' key for the image to be used for the node
    Dim nodNewOrderLineNode As MSComctlLib.Node ' order line added to the tree
    
    ' open the recordset for products
    strSQL = "SELECT * FROM qryOrderLines WHERE OrderID=" & CStr(lngOrderID)
    Set rst = CurrentDb.OpenRecordset(strSQL)
    
    ' loop through the rows in the recordset
    If Not (rst.BOF And rst.EOF) Then
        rst.MoveFirst
        Do Until rst.EOF
            lngProductID = rst!ProductID
            strOrderLineText = CStr(rst!Quantity) & " x " & rst!ProductName
            
            ' determine key (in imagelist control) for image to be used for this node
            ' image key is the category name from the query
            strImageKey = rst!CategoryName
            
            Set nodNewOrderLineNode = Me.xMyTreeview.Nodes.Add(Relationship:=tvwChild, _
                    Relative:=nodOrder, Text:=strOrderLineText, Image:=strImageKey)
            
            With nodNewOrderLineNode
                .Tag = "P" & CStr(lngProductID)
            End With
            
            rst.MoveNext
        Loop
    End If
    rst.Close
End Sub

This code is a lot like the previous procedure. The first difference is that the key of the image to be used is determined from the product's category name. (Remember when we added the images to the list view we used category names as the image keys.) And since there are no nodes to be added to these order line nodes we don't have another procedure to call.

Wrapping It Up

The only other code we need are form open and close events. The open calls the procedures to set up the imagelist and set up and load the treeview. The close just does some clean-up.

Private Sub Form_Close()
    SysCmd acSysCmdClearStatus
    DoCmd.Hourglass False
End Sub

Private Sub Form_Open(Cancel As Integer)
    DoCmd.Hourglass True
    SysCmd acSysCmdSetStatus, "Setting up treeview"
    
    SetUpImageList ' load images to image list
    SetupTreeview ' set treeview properties
    CreateCustomerNodes ' add customer nodes, and with them order and order line nodes
    
    SysCmd acSysCmdClearStatus
    DoCmd.Hourglass False
End Sub

That's it! You should have a treeview with customers, orders, and order lines, with an image for each node.