My Treeview Project | Episode Two: Northwind Categories and Products

In Episode 1: The Hello World! Treeview we built the simplest of treeviews, just to introduce some of the concepts. In this episode we'll build a treeview to display products in categories.

We'll use data from Microsoft's Northwind Traders sample database. If you don't have a copy you can download it here .

Start by creating a blank form in the Northwind Traders database and add a Treeview control, the same way we did in episode 1. Call it xProductTreeview and make it a little taller this time- I made mine about 2-1/2" wide and 3-1/2" tall. Switch to the code view and paste the following into the form's code module:

Private Sub CreateCategoryNodes()
  Dim rst As DAO.Recordset ' recordset for category data
  
  ' open the recordset for categories
  Set rst = CurrentDb.TableDefs!Categories.OpenRecordset
   
  ' loop through the rows in the recordset
  rst.MoveFirst
  Do Until rst.EOF
    Me.xProductTreeview.Nodes.Add Text:=rst!CategoryName, _
      Key:="Cat=" & CStr(rst!CategoryID)
    rst.MoveNext
  Loop
  rst.Close
  Set rst=Nothing
End Sub

Private Sub CreateProductNodes()
  Dim rst As DAO.Recordset ' recordset for product data
  
  ' open the recordset for products
  Set rst = CurrentDb.TableDefs!Products.OpenRecordset
  
  ' loop through the rows in the recordset
  rst.MoveFirst
  Do Until rst.EOF
    Me.xProductTreeview.Nodes.Add Relationship:=tvwChild, _
        Relative:="Cat=" & CStr(rst!CategoryID), _
        Text:=rst!ProductName, Key:="Prod=" & CStr(rst!ProductID)
    rst.MoveNext
  Loop
  rst.Close
  Set rst=Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
  CreateCategoryNodes
  CreateProductNodes
End Sub

Let's look at the code. The Form_Open event code simply calls two subroutines: one to add the category nodes and one to add the products.

Each of the two subroutine uses a really simple loop to read through all the records in the table. CreateCategoryNodes reads the Categories table and CreateProductNodes reads the products table. The loop is probably unrealistically simple, but this isn't a "how to read a table in VBA" tutorial.

In CreateCategoryNodes each Category node is added using the code:

Me.xProductTreeview.Nodes.Add Text:=rst!CategoryName, _
  Key:="Cat=" & CStr(rst!CategoryID)

We looked at adding nodes in episode 1. What's different this time? We want to be able to refer to these category nodes later when we add products under each one, so we're giving each one a key. We want to be able to tell the category nodes apart from the product nodes, so we build a key by making a string that starts with Cat= followed by the category ID number.

After all the category nodes have been added the subroutine CreateProductNodes adds products by reading through the Products table and for each one doing:

Me.xProductTreeview.Nodes.Add Relationship:=tvwChild, _
  Relative:="Cat=" & CStr(rst!CategoryID), _
  Text:=rst!ProductName, Key:="Prod=" & CStr(rst!ProductID)

Like the second node we added in episode 1, we use Relationship:=tvwChild to specify that this node should be added as a child of another node. We specify the node it's a child of with Relative:="Cat=" & CStr(rst!CategoryID), recreating the key of the appropriate category node. The category ID is a field in the product table so we know what category each product belongs to. We won't need it yet, but we'll give the product nodes a key in a similar way to the category nodes above, with Key:="Prod=" & CStr(rst!ProductID).

Save the form and open it. You should see the treeview with the categories listed. Double click on a category and you'll see the products. There you have it- a working treeview showing data from a database!

In episode three, we'll fine tune the look of the treeview and specify that the categories should be expanded when the form is first loaded. Check out all the posts in MyTreeviewProject.