In Episode 7 of My Treeview Project we're going to look at another approach to reading through our data and adding nodes to the treeview.

Different ways to get data for the treeview nodes

In Episode 1 we didn't use a table at all; the nodes to be added were defined in the code. That approach may be suitable for treeviews that are used for navigation where the nodes don't depend on your data.

In Episode 2 we first used the Northwind product and category data to build our treeview. We had one subroutine to add all the categories and another to add all the products. That approach was clean and simple and will perform quite well in most cases.

In Epsiode 6 we took a different approach. That treeview shows customers, their orders, and the products on those orders. We added our customers, and just after adding a customer we added that customer's orders, and just after we had added the order we added the products for that order. That approach handles is capable of handling more complex data scenarios- it's necessary for hierarchical data where the number of levels is not known, such as employees who report to other employees.

In this episode I'll show you another approach, which many people find simpler. We'll use the same data as we used in epsiode 2 through 5: categories and products from Northwind. For this approach we'll build one query that brings in all the categores and all the products and we'll loop through all the records in that query, adding both category and product nodes in the same loop.

A starting point for this epsiode's code

As before, we're using data from Microsoft's Northwind Traders sample database. If you don't have a copy you can download it here. Or even better, start with the database we created in Episode 4. If you've been following along you may have it already, or download it here.

If you want to build the Episode 4 database go back to Episode 2 and start with a new form there, and then add to it in Epsiode 3 and Episdoe 4.

The new query- all category and product data

First we need a query that will bring the category and product data together into one list. Create a query called qryCategoryProductTreeviewData. Here's the SQL- paste this into the query's SQL view.:

SELECT
     "C" & Categories.CategoryID AS CategoryNodeKey, 
     Categories.CategoryName, 
     "P" & [ProductID] AS ProductNodeKey, 
     Products.ProductName
FROM 
     Categories 
       INNER JOIN Products 
       ON Categories.CategoryID = Products.CategoryID
ORDER BY 
     Categories.CategoryName, 
     Products.ProductName;

It's a pretty straightforward query, but we'll talk about a few points worth noting.

"C" & Categories.CategoryID and "P" & [ProductID] are creating text values we can use as keys for our nodes. Remember that we typically create a key for each node, that each key has to be unique, and that the key has to be text, not a number. By making the first letter of each category node key a C and the first letter of each product node key a P we will be able to determine in code whether it was a category or product node that was clicked or selected. In Episode 5 there's an example of code that uses the key to do something with the node that has been selected.

We've sorted the query results on CategoryName and Product Name. It's important when using this type of query to add nodes that the records for a category stay together- sorting on the category name will do that for us. The sort or product name will add the product nodes in alphabetical order (we could also tell the treeview to sort the nodes- in this case we'll just add them in the order we want to see them.)

This Epsiode's code

Here's the core of this week's code changes. Delete the subs called CreateCategoryNodes and CreateProductNodes and replace them with this new sub called AddAllNodes.

Private Sub AddAllNodes()
    Dim rst As DAO.Recordset ' recordset for category and product data
    
    Dim strCategoryNodeKey ' key for this category node
    Dim strOldCategoryKey As String ' for detecting change in category
    
    ' open the recordset
    Set rst = CurrentDb.QueryDefs!qryCategoryProductTreeviewData.OpenRecordset
   
    ' loop through the rows in the recordset
    rst.MoveFirst
    Do Until rst.EOF
        
        strCategoryNodeKey = rst!CategoryNodeKey
        
        If strCategoryNodeKey <> strOldCategoryKey Then ' check for change in category
            ' change in category- add category node
            Me.xProductTreeview.Nodes.Add Text:=rst!CategoryName, Key:=strCategoryNodeKey
            strOldCategoryKey = strCategoryNodeKey ' remember this as the current key for detecting changes
        End If
            
        ' now add product node
        Me.xProductTreeview.Nodes.Add Relationship:=tvwChild, Relative:=strCategoryNodeKey, _
                Text:=rst!ProductName, Key:=rst!ProductNodeKey
        
        rst.MoveNext ' next record in qeury
    Loop
End Sub

Change the sub FormOpen to this new version

Private Sub Form_Open(Cancel As Integer)
    SetupTreeview
    AddAllNodes
End Sub

This new sub, AddAllNodes, opens a recordset based on the query we created above,
Set rst = CurrentDb.QueryDefs!qryCategoryProductTreeviewData.OpenRecordset
and reads through all the rows in the recordset
rst.MoveFirst
Do Until rst.EOF
    ....
    rst.MoveNext
Loop Within the loop we're going to use "control break logic". That's an old computing term for a classic program design where we keep track of a certain value in our data, and when that value changes we do something. In this case we keep track of the product category, and everytime that value changes we add a new category node to our terrview. The variable strOldCategoryKey is used to remember the category key of the last category we processed. The test If strCategoryNodeKey <> strOldCategoryKey checks to see if we're still on the same category. Not so hard really (and I've probably over-explained the logic already.)

Now to the treeview-specific details. Adding the category nodes is pretty similar to the other add nodes code we've seen before:
Me.xProductTreeview.Nodes.Add Text:=rst!CategoryName, Key:=strCategoryNodeKey
A node is added with text and key coming from the query. The line that adds the product node is only a little more complex:
Me.xProductTreeview.Nodes.Add Relationship:=tvwChild, Relative:=strCategoryNodeKey, _
    Text:=rst!ProductName, Key:=rst!ProductNodeKey
The node is again added with the text and key from the query. The node is aaded as a child node (Relationship:=tvwChild) and the key of the current category node is supplied to identify the parent node this node should be added under (Relative:=strCategoryNodeKey). Note that you can use either the key of the parent, as we did here, or a variable that referes to the parent node itself, as we did in episode 6.

That's all it takes! You should get a working treeview. For a simple treeview this one query approach may be attractive. My point in illustrating these diverse approaches is to show that your approach to reading your data should depend on your application and your data, as well as your preference. Once you know how to use the treeview properties and methods you'll be able to work treeviews into your application in a way that makes sense.

You can download the completed episode 7 database here.