Tuesday, March 17, 2009

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.

17 comments:

Unknown said...

Thanks for completing this episode Stephen. I'm happy to see the imagelist control working properly.

If you decide to do more, perhaps tweaking as you mentioned to load faster would be useful.
Thanks again.

jack

Anonymous said...

Thanks for all those tutorials.
Need some help regarding the tag property.
I have been tryin gto use the code in ep 5 to retrive the tag

Set nodSelected = Me.tvwCategories.SelectedItem


If nodSelected.Key Like "SubCat1=*" Then
If Me.tvwCategories.Tag = "SubCat2CC" Then
me.frmdefault.visible = true

else
...

However, its no picking up the tag.
Please, is this the right way of retrieving the tags?

many thanks
Ben

Stephen said...

Ben

try nodSelected.tag

The tags are properties of the nodes not the tree itself.

Anonymous said...

Thanks for your reply regarding the tag (and for all those great tutorials) Stephen...works perfectly

Ben

Anonymous said...

Great article once again! Is it possible to link the nodes to PDF files so that if I click on a node the PDF file for that specific product will pop up? If so, could you suggest the best way to do that? Thanks

Anonymous said...

Thanks for the tutorial. It would be great if you decide to do an episode 7 on speeding up large treeviews by populating children nodes on demand. Thanks again!

Anonymous said...

Hi - Some really great stuff going on here?

As the tree grows with more nodes containing more info

Wouldn't it be useful to have a search facility to search the tree for specfic data?

Might that not be a useful future episode?

Sun Sopheary said...

Thank you so much for your effort to make the treeview tutorial. It is very great to know on how to use treeview, but I was wondering if you could post the code on how to delete children of any node that we wish. For example: I have node named Language and there are about 20 children nodes under the parent nodes Language. I wish to delete all the children node of Language. Thank you.

tewald said...

This is incredible! Exactly what I've been looking for. Thank you so much. Question: Can Treeview handle more than one field (or attribute) per line? I'm thinking of a manufacturing situation, where for example a car would have 4 wheels, each wheel a main body and maybe 5 studs and 5 nuts, etc. So I'd need to show a quantity. Altogether, a part number, description, quantity and maybe weight. I'm not asking you how to do this - that would be taking advantage of you, and you would want to be paid, as would be appropriate. I'm just asking if it's possible with the Treeview control. Thanks again so much.

Anonymous said...

I always wanted to create a Treeview in Access. Thanks for the code. I want to create a Colored Ball type status indicator based on a recordset value. Such as a numerical value of 1 = a Red Ball graphic and 2 = blue and so on.
Would you use the image MS Image Library or is there another method you'd recommend?

Anonymous said...

Excellent tutorial. Well commented code and easy to follow explanations. In my case it doesn't quite depict a flexible enough solution. I'm trying to work out a way to call each child-populating function recursively, so you can have as many levels as you want, based on a linked list (parent/child table). I think the help you've provided here is a good step in the right direction.

Anonymous said...

Dear Stephen Hill,

like the others that posted before me, I just want to say Thanks for those awesome tutorials. Minus your time and effort, I would still be looking for a solution to my "Treeview" dilemma. Again, Thank you!

Edward

Anonymous said...

It will also great for me if you decide to do an episode 7 .

In the treeview
1)
How delet a key
2)
How deleted a Child key
3)
How to hide a key and
4)
How to hide a Child key
5)
How to get focus on a key
6)
How to get focus on a Child key
7)
How to change name on a Child key
8)
How to change name on a key
How to get on the top of the tree when you open the forms, instead in the top of the bottom, when there are more key in the tree that you can show in the form.

I want episode 7 the is telling about edit in the treeview when you changes in the post as name like to change the treeviewæs key

and so on.

I like you blog and the going over the treeview.

Thanks to you

Stephen said...

"Anonymous" - for "colored ball status indicators" I would use a character that looks like a ball and set the font color according to your status.

Stephen said...

tewald- the treeview only has one text field, but you can combine multiple data elements into one string as I do in the example in this post

Juancho said...

hello, if i may ask:
i have a fishCategory field and a fishType multivalue field in myFish table, how do i get a treeview to display:

+fishCategory
-----+fishType
---------fishName

i tried the code in episode two but could only get to work

+fishCategory
-----fishName

i can't get to work

+fishType
---fishName

plz help newbie

Stephen said...

Jauncho-

Reading from a multi-value field requires that you assign the value to a recordset and loop through the recordset to get all the values.

Here's a version of the product nodes subroutine from episode 2 that adds nodes for product keywords, which are stored in a multi-value field:

Private Sub CreateProductNodes()
Dim nodProduct As MSComctlLib.Node
Dim rst As DAO.Recordset ' recordset for product data
Dim rstProductKeywords As DAO.Recordset ' recordset to read multivalue keyword field

' open the recordset for products
Set rst = CurrentDb.TableDefs!Products.OpenRecordset

' loop through the rows in the recordset
rst.MoveFirst
Do Until rst.EOF
Set nodProduct = Me.xProductTreeview.Nodes.Add(relationship:=tvwChild, _
relative:="Cat=" & CStr(rst!CategoryID), _
Text:=rst!ProductName, Key:="Prod=" & CStr(rst!ProductID))

' add sub nodes for keywords
If Not IsNull(rst!Keyword) Then
Set rstProductKeywords = rst!Keyword.Value ' get the mutliple values for keyword and assign them to a recordset for processing
Do Until rstProductKeywords.EOF
Me.xProductTreeview.Nodes.Add relationship:=tvwChild, _
relative:=nodProduct, Text:=rstProductKeywords!Value.Value
rstProductKeywords.MoveNext
Loop

rstProductKeywords.Close
End If

rst.MoveNext
Loop

rst.Close

Set rstProductKeywords = Nothing
Set nodProduct = Nothing
Set rst = Nothing

End Sub