Tuesday, November 17, 2009

My Treeview Project Episode 7 | Another Way To Read Through Your Data

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.:

     "C" & Categories.CategoryID AS CategoryNodeKey, 
     "P" & [ProductID] AS ProductNodeKey, 
       INNER JOIN Products 
       ON Categories.CategoryID = Products.CategoryID
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
    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
End Sub

Change the sub FormOpen to this new version

Private Sub Form_Open(Cancel As Integer)
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
Do Until rst.EOF
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.


Anonymous said...

Wow, what a great help this tutorial has been.
I have one issue I can't seem to resolve. After making a selection from the tree, I would like to disable it until certain data is entered on my form, then enable the tree again. If I use the .Enable = true/false, the tree display gets disoriented. If I click on it, it then displays correctly. Any suggestions?

JD said...

Wow from me as well. I have used your posts extensively to create treeview based forms in my application. but... some of the treeview labels became too long for the fixed treeview width. I was looking for a solution and was advised to use VBs "Split Container" which most of the applications today use to fold/unfold left-right panes.

This might be related to this subject as well : " Walkthrough: Creating a Multipane User Interface with Windows Forms" , http://msdn.microsoft.com/en-us/library/aa984325(VS.71).aspx .

Can this be used with ACCESS 2007 VBA ?, I do not have enough experience to translate this into a working solution (With ACCESS 2007 that is). Would I dare and ask if this could be the basis for your next Treeview Episode ? .

I would appreciate for some simple example of a two panes split container having Treeview on the left and Access Form on the right.

Thanks and all the best.


tewald said...

These are great - I can't thank you enough for helping me to understand the TreeView. I notice that the data has only 2 levels, though. Could you help us understand how to do more than 2? Adding more manually like in your first lesson is intuitive; how about something more automatic like this episode? For example, a bill of material that goes 3 or 4 levels deep, or even more. I'd like to understand the mechanics of that. Thanks, again.