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.
Download the finished database
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.
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 SubHopefully 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 SubNew 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:
- 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.
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.
- We do Set nodNewCustomerNode= to assign this newly created node to a variable so we can refer to it later.
With nodNewCustomerNode .Expanded = True ' set a Tag we can use to identify the customer the node is for .Tag = "C" & CStr(strThisCustomerID) End WithWe 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:=strThisCustomerIDThis 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 SubThe 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 SubThis 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.