My Treeview Project | Episode Five: Using The Treeview To Select Records to View/Edit

In episode 5 we'll make the treeview really do domething. We'll use it to select records from the products and categories tables that we want to see or edit. The work you'll have to do in this episode is a little more than in the last two, but we'll go through it step-by-step. We're building on the work you've done in previous episodes. If you started in the Northwind database in episode 1 you'll have all the tables you need. If you're starting with the database you downloaded from the episode 4 page you'll have to add the Suppliers table from the Northwind database.

When we're done, we'll be able to click on a category in our treeview and see the information about that category on our form beside the treeview, or click on a product to see the product details. We'll use subforms to get the product and category information onto our form. So the first thing we need to do is create the subforms. For my database I let the wizard do the work.

• With the Categories table selected in the Access database window, do Insert/Form and select the Form Wizard option. (Or in Access 2007 select Create/More Forms/Form Wizard).
• Select all the fields in the table.
• And select columnar layout.
• Save your new form as frmCategories.
• Do the same thing with the products table to create frmProducts.

These will be pretty generic forms but they'll work for our purpose.

Now we have to add the subforms to the form with the treeview. So open that form in design mode (mine is called frmMyNorthwindTreeview.) Find the subform/subreport tool on the toolbox and use it to add a subform. Specify that you want an existing form, and select the frmCategories we created above. When it prompts you for a name for your subform call it sfrmCategories. (It's a good idea to have the subform control's name slightly different than the name of the form used as a subform- I always name my subform controls starting with sfrm.) Add the subform anywhere you like- I added mine right beside the treeview.

Do the same thing to add the form frmProducts to your form as a subform called sfrmProducts. Place it right on top of the categiries subform. In our code we'll adjust which one is actually visible based on the type of node that's clicked.

Subforms can automatically show matching data, but they need a field or control on the main form that tells them which record to show. In this case you'll need to add text boxes for this purpose. We'll write code that will find the appropriate ID numbers and put them in the etxt boxes so the subforms know what data to display. Add a text box called txtProductID and one called txtCategoryID. Both are unbound, meaning they're not connected to fields in any table. Eventually you'll make these invisible, so Access can use them put users don't see them, but for now leave them visible so you know what's going on.

Now you have to tell the subforms to use those text boxes to know which data to show. Go into the data properties of the subform control we named sfrmCategories. Set the Link Child Fields property to CategoryID (that's the name of the field on the subform used for the link) and set the Link Master Fields property to txtCategoryID (that's the control on the main form used for the link.)

Do the same thing for the subform sfrmProducts. The Link Child Fields property should be ProductID and the Link Master Fields property should be txtProductID.

Now all we have to do is get Access to put the right id numbers in the two text boxes based on what we click in the treeview. We set up for this way back in episode two when we wrote the code that added the category and product nodes. The Key we used for a category node is Cat= and then the id number. Likewise product nodes have a Key of Prod=. So our new code just has to figure out whether we're on a product or category node, get the id from the key, and put the id in the text box. The subform will go to the right record automatically when the text box changes. Here's the new code you have to add to the forms' module:

Private Sub xProductTreeview_Click()
    Dim nodSelected As MSComctlLib.Node ' a variable for the currently selected node
    
    Set nodSelected = Me.xProductTreeview.SelectedItem ' get the currently selected node
    
    If nodSelected.Key Like "Prod=*" Then ' are we on a product node
        Me.txtProductID = Mid(nodSelected.Key, 6)
        Me.sfrmProducts.Visible = True
        Me.txtCategoryID = Null
        Me.sfrmCategories.Visible = False
    ElseIf nodSelected.Key Like "Cat=*" Then ' are we on a category node
        Me.txtCategoryID = Mid(nodSelected.Key, 5)
        Me.sfrmCategories.Visible = True
        Me.txtProductID = Null
        Me.sfrmProducts.Visible = False
    Else ' somehow this is neither a category or product node
        Me.txtProductID = Null
        Me.sfrmProducts.Visible = False
        Me.txtCategoryID = Null
        Me.sfrmCategories.Visible = False
    End If
    
End Sub

Nothing really complex here, but let's go through it.

• Take a look at the Sub's name. xProductTreeview_Click. This is the On-Click event for the treeview control called xProductTreeview. Whenever the user clicks the treeview this subroutine will execute.
• The line starting with nodSelected gets the currently selected node and stores it in a variable- we'll use that a couple of times so might as well get it once and keep it.
• Next we use If...ElseIf...EndIf to check the beginning of the selected node's key so we know if it's a category node or a product node (or something else, just in case.).
• Once we know what it is we use Mid() to get the ID number part of the key and put that in the approrpiate text box. For good form we clear the other text box so that only one subform is showing data.
• And we make one text box visible and the other one invisible- remember that their over top of each other- whichever one doesn't apply should not be shown.

That's all there is to it! Change your text boxes to Visible=No (under their format properties) and you'll have a nice looking form. Here's how mine came out:

To download the completed database from episode 5 click here.

In Episode Six we'll start on a new treeview for customers and orders and we'll add images to the nodes!