Sunday, February 24, 2008

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.

9 comments:

Ricardo said...

This is a great tutorial. I just want to mention that in the piece of code,

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

I believe the line that reads
Relative:="Prod=" &
should read like
Relative:="Cat=" &

ter-o-retik said...

I think that using Queryes instead of Tables is worth mentioning.
I meen code like
"Set rst = CurrentDb.QueryDefs!QueryX.OpenRecordset"
By using queryes you can store you tree structure in one table (fields like CategoryName; CategoryID; Level; ParentID).
Query example:
SELECT Table1.CategoryName, Table1.Code, Table1.ParentCode
FROM Table1
WHERE (((Table1.Level)="2"));

Stephen said...

Ricardo- you're right, sir, and I;ve fixed the post. Thanks for the catch!

Stephen said...

ter-o-retik: you're right- I;ve seen some neat examples where a query is constructed that pulls the parent and child data together, allowing one pass through the query's recordset to create all the nodes

Darrell said...

When I posted the code into the form module, in the line: Me.xProductTreeview.Nodes.Add Text:=rst!CategoryName, _
Key:="Cat=" & CStr(rst!CategoryID)


rst!CategoryID would not compile.

jack said...

Darrell,
Make sure you name the treeview control xProductTreeview.

Jack

cvmircea said...

Great tutorial.I try to use treeview but i receive an error: "Run time error 35601 element not found".I don't know what is wrong.can you help me?I have 2 tables: Clienti with Columns: ClientId,Nume
and Comenzi table with columns: ComandaId,ClientId. and code:
Private Sub CreateCategoryNodes()
Dim rst As DAO.Recordset ' recordset for category data

' open the recordset for categories
Set rst = CurrentDb.TableDefs!Clienti.OpenRecordset

' loop through the rows in the recordset
rst.MoveFirst
Do Until rst.EOF
Me.xProductTreeview.Nodes.Add Text:=rst!nume, _
Key:="Cat=" & CStr(rst!ClientId)
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!Comenzi.OpenRecordset

' loop through the rows in the recordset
rst.MoveFirst
Do Until rst.EOF
Me.xProductTreeview.Nodes.Add Relationship:=tvwChild, _
Relative:="Cat=" & CStr(rst!ClientId), _
Text:=rst!ComandaID, Key:="Prod=" & CStr(rst!ComandaID)
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
CreateCategoryNodes
CreateProductNodes
End Sub
The error is when it add the child node

Sanjaya said...

Hi Guys,

Im very much a novice in here. I have managed to use this code and develop something similar to what we have here and I thank you very much for that. I need to go one more step forward to add another level to the tree using a thrid table which is related to the second table using a one-many relationship. Can someone help me with this , great favour

addy said...

Great tutorial - I've been working on a TreeView and some information here has just helped me overcome an issue I was having. Thanks!