Sunday, September 14, 2008

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!

20 comments:

SNAKEBOB said...

Perfectly wonderful information.
Before I found this blog, using Treeview control seems like something under scope of high-level programmer.
Now I feel it get much familliar.

I am looking forward next story.

Paul said...

From zero humble steps on my own in one night ... many thanks

Anonymous said...

tree have become fun..thanks to u..but there is a issue ..if i am having a date validatin(eg.compare validator) then tree just expands once before validation and dies..can u help..

Anonymous said...

Nice job.

Do you have an example of a recursive routine for products (like a mult-level bill of material) -- products used to make higher level products?

Please email me at dave.meyer@live.com.

Thanks!

Soulcrafter said...

Well done description of a complicated and underdocumented topic

However, I note that Click() isn't triggered if the user is navigating via keyboard and I couldn't find a useful alternate Event; any recommendations as to modifications to accommodate arrowing from one treeview record to another and expanding child nodes via plus sign

Anonymous said...

Many thanks for this info. I wrote a lot of treeview stuff for Access some years ago, but since then I've been doing my "proper job" (big, big, big databases) and I'd forgotten the details. I needed a refresher. Thanks! Andrew in London

Fredrik said...

having problem withe the click funktion !

Private Sub TreeView1_Click()
Dim nodSelected As MSComctlLib.Node ' a variable for the currently selected node

Set nodSelected = Me.TreeView1.SelectedItem ' get the currently selected node

If nodSelected.Key Like "Sys=*" Then ' are we on a product node
MsgBox "fredrik"
ElseIf nodSelected.Key Like "Usys=*" Then ' are we on a category node
MsgBox "Undersystem"


Else ' somehow this is neither a category or product node
MsgBox "inget"

End If
End Sub
geting an error when i click on the node
refrens not conected or ?
anyone a clue?

Unknown said...

In the Open Event of the Form, suggest both subforms have the Visible property set to False. This will prevent both subforms appearing/overlapped etc before the user clicks on the Treeview control.

jack

Anonymous said...

Hi

Up until I found this site, finding anything, anywhere about treeview controls was a nightmare. This site has literally changed how users interface with my database overnight. Fantastic alround. Hope you manager to write Episode 6, in an earlier comment you mentioned doing it about Pop up forms when you double click a node. Look forward to it! and Thank you.

Stephen said...

Thanks for the great feedback all! I'm glad you've found treeviews as fun to develop with as I have, and I'm glad I could help you navigate the mystery!
Do let me know what you'd want to see in future "episodes". I'm going to make time to put at least a couple more up.

Stephen said...

Episode 6 will be a treeview with images for the nodes. The database is done and I just need to write the article!

Anonymous said...

Just wana say THANK YOU for this!! my uncle asked me to create a database for him using access, and i only have experience in Java (although i do have a small amount of exp in VB [using excel tho, not access])

So thanks!!
Paul

Anonymous said...

HI. I think your treeview articles are really impressive. One problem through, when I open my database my treeview automically pops up just fine, when other users go into the database, they are greeted with a message that says "compile error can't find project or library" and highlights private sub SetupTreeview (). But we are all working out of the same db. How come I can get the treeview but others users get that message? Could anyone explain the problem and offer a solution. Thanks

Stephen said...

Anonymous: there must be a code library that your code references that the other users don't have access to. For instance it might be on your c: drive. From the VBA window on their PC go to Tools/References and look for a library that says "MISSING".

Anonymous said...

Thanks Stephen.

Tried what you said and it worked. Thanks very much.

Anonymous said...

Hi Stephen.

You articles are brillant!! You mentioned a couple months back that Episode 6 will be a treeview with images for the nodes. The database is done and you just needed to write the article! - have you managed to post it yet? Also, is it possible to link the nodes to PDF files so that if I click on a node the PDF file for that specific product will pop up? If so, could you suggest the best way to do that? Thanks

Anonymous said...

Apologies, just seen you have already uploaded Episode 6. Blind as a bat sometimes!! Thanks

Dale said...

One of the best references on the Treeview control I have ever found.

Am currently having a problem with one of mine.

My main form contains a treeview which is filled with activities. It also contains a subform with the activity details. On the subform there are several buttons that pops-up other forms.

When one of these forms is opened (and it only happens with this one form), and then closed, the treeview inexplicably expands all of the nodes, but it only does it some of the time.

I put a breakpoint on the cmd_Close_Click event, and tracked it through to the point where the treeview expands, and there is NO code that references the treeview in any way.

I've used treeviews many times in the past (A2003 and earlier) but this is my first attempt to use it with an accdb file.

Any ideas?

Diego F. Pereira-Perdomo said...

Hello,
Glad to be here!
I have a question.
I'm trying to fill a listbox from a treeview control.

I tried doing something like this:

CodCIETree = The name of my treeview control
CIEID3digID = The name of the field bound to the listbox

Private Sub CodCIETree_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Long, ByVal y As Long)

If Button = RIGHT_BUTTON Then
Me.CIEID3digID.SelectedItem.Key

End If

End Sub

... but it didn't work

Can you give me a hand?

Thanks in advance,

Diego

salpante said...

Hi,
beautiful article, it made me able to use treeviews in my app with a very professional and good-looking effect, thanks!

R