Treeviews in Access Tutorial – Part 1 Basic – Loading information into Treeview

The Treeview is a great free part of the Microsoft Common Control Library that lets you create your own Treeview and tie your own data and events into the Treeview. It can present a lot of information in a limited space, and allows a lot more versatility in presenting your information. Combine this with the ability to use small icons for each individual record, you get maximum structure on data presentation.

This is part 1 in a series on how to use the treeview in Access.

This first video demonstrates how to add a Treeview into your form, and how to load information into it. Its all very basic, explained and coded from scratch, with detailed explanations. Later I will upload more advanced guides on the same topic, but this is intended to be a very easy getting started guide.

 

Downloads:

Download “Treeview Demo Part 1 Sample (2003 format)” Treeview-Part-1-2003-format.zip – Downloaded 1585 times – 22 KB

Download “Treeview Tutorial Part 1 sample (2007 format)” Treeview-Part-1-2007-format.zip – Downloaded 12836 times – 31 KB

Author:

Posted in Treeview
32 comments on “Treeviews in Access Tutorial – Part 1 Basic – Loading information into Treeview
  1. Jessie says:

    Hi!

    I’m using your tutorial to create a tree in my database. I entered the loadTreeview code using my data, but when I tried to open the form, I get the following error:

    Run-time error ‘2465’: Application-defined or object-defined error

    When I click “Debug”, it highlights the following in your code:

    set tv=forms(frm_TreeView_Example”).treeReqs.Object

    It seems the “.treeReqs.Object” isn’t being recognized by my system. How do I change this?

    Thanks!

    • TheSmileyCoder says:

      Hi Jessie and welcome to my blog.
      I recently found out that not all combinations of Windows/Access will automatically add the reference to the treeview, when the treeview object is first inserted in a form (like it is done in my video).
      So start by checking your references, and make sure a reference is set to
      “Microsoft Windows Common Controls 6.0 (SP6)”
      If it does not occur in your list, try browsing for it, its usually installed in:
      “C:\Windows\System32\mscomctl.ocx”
      Hope that helps, and best of luck with your project.

      • Ben says:

        Hi

        I have the same problem as Jessie did but am still slightly lost.

        Unfortunately my programming knowledge is pretty basic and only goes as far as I have learnt for other adhoc stuff I have done, now that I am doing a treeview for the first time I am unsure how to make sure a referent is set to ‘Microsoft Windows Common Controls’. Is this something I would do in the code or in the menu options somewhere.

        Apologies for the stupid question, I have tried googling to find out but to no avail!

        Many thanks

  2. Sidnei says:

    😀 Hi!
    I have a MS Access database with a Treeview control in a form. The application runs fine in my client machines but I’m using a 64bits Office 2010 to mantain it. As the control is not supported in the 64bit version of Office, I do not even open that form. Is there some workaround to this problem should I have to really modify something in the Treeview control with my MS Office 2010 64bit edition?

    Thanks!

  3. Samer says:

    Hi
    I tried to modify the example to present arabic data in the children nodes but it comes as ?????
    Need your help and support please

    Many thanks

    • TheSmileyCoder says:

      Hi Samer

      In design view, try right clicking the treeview, select “TreeCtrl Object” and then “Properties”. In here there is a font tab, where you can see the fonts available. To be honest I don’t know if that allows or does not allow you to use arabic letters. I have never tried using arabic letters, and don’t even know how you guys manage to type them in a normal textbox.

      Best of luck with your project.

    • hello Samer,
      have you ever find a solution to your problem? coz i have the same problem but not “????”, it shows garbage instead.

      waleed

  4. perseusrufus@gmail.com says:

    Hi I appreciate you providing this tutorial. I have a table with data of my own in which I am trying to get this to work but for whatever reason it won’t run with the addChildren call within the first Do While Not loop

    addChildren tv, nodX, empData, empData!Name

    my first node works and creates the root name where mine is equal to 0 which i have here:

    Set nodX = tv.Nodes.Add(, , , empData!Name)

    I will copy and paste my code which is very similar to yours but when i run it I receive:

    Run-time error ’13’:
    Type mismatch

    when i click debug it highlights the addChildren call which I have provided above. Here is my code below that I have completed. Thanks again for your help.

    Option Compare Database

    ‘Within this module the LoadTreeview function is created so the code below runs when the Org_TreeView form is selected.
    Public Sub loadTreeview()
    ‘declaration of treeview as TreeView
    Dim tv As TreeView

    ‘sets the treeview and references where the treeview will be viewed by the user
    Set tv = Forms(“Org_Treeview”).TreeDesign.Object

    ‘clear all previous node information so only new info populates (for future changes to the tables)
    tv.Nodes.Clear

    ‘declare data in order to capture for the treeview
    Dim empData As DAO.Recordset

    ‘identifies that all data from the Employees table, ordered by OrgID to be used later by strFind
    Set empData = CurrentDb.OpenRecordset(“SELECT * FROM Employees ORDER BY OrgID”, dbOpenDynaset)

    ‘Declare variable which will find the data
    Dim strFind As String
    strFind = “OrgID =0”
    empData.FindFirst strFind

    ‘declare node
    Dim nodX As Node

    ‘Declare bookmark to store location of table
    Dim strBook As String
    ‘Create While loop in order to find data along with adding data to the Parent Node
    Do While Not empData.NoMatch
    Set nodX = tv.Nodes.Add(, , , empData!Name)

    ‘set bookmark during loop
    ‘strBook = empData.Bookmark

    ‘executes addChildren function which is declared and coded below “End Sub”
    addChildren tv, nodX, empData, empData!Name

    ‘restore location of bookmark after addchildren completes
    ’empData.Bookmark = strBook
    empData.FindNext strFind

    Loop

    End Sub

    ‘Create Function in order to load child nodes which are OrgIDs >0
    Private Sub addChildren(tv As TreeView, nodParent As Node, empData As DAO.Recordset, lngParentID As Long)
    ‘Declare String variable used for Find method along with loading data from Table
    Dim strFind As String
    strFind = “OrgID=” & lngParentID
    empData.FindFirst strFind

    ‘Delcare node again
    Dim nodX As Node

    ‘Declare bookmark to store location of table
    Dim strBook As String

    ‘Do While loop loop until it doesn’t have a match
    Do While Not empData.NoMatch
    ‘identifies location of node along with parent node
    Set nodX = tv.Nodes.Add(nodParent, tvwChild, , empData!Name)

    ‘set bookmark during loop
    ‘strBook = empData.Bookmark

    ‘recursively calls additional children
    addChildren tv, nodX, empData, empData!Name

    ‘restore location of bookmark after addchildren completes
    ’empData.Bookmark = strBook
    ‘iteration to find next orgID #
    empData.FindNext strFind
    Loop

    End Sub

    • TheSmileyCoder says:

      Hi Perseus.
      Looking at your code, I am guessing that empDate!Name is NOT a numeric field. The AddChildren routine as it is currently is expecting a Long as the last argument. The definition for the AddChildren reads:
      Private Sub addChildren(tv As TreeView, nodParent As Node, empData As DAO.Recordset, lngParentID As Long)

      As the lngParentID argument you are passing empDate!Name in this line:
      addChildren tv, nodX, empData, empData!Name

      I am a bit confused. Are you trying to load more than 1 organisation as well as their employees?

      • Jeremy Vail says:

        Hi. Thank you so much for responding to my question. I am trying to create a demo tree view with employees and their directs. For example: John Doe is the root and then Jim Beam is his direct, then he has 2 directs, and then those 2 have directs of their own.

  5. TheSmileyCoder says:

    Ok. I presume they are both in the same table. I get a bit confused since your code mentions OrgID which I am guessing is Organisation ID? Either way, the part passed to the AddChildren function should be the ID of the Direct. The current code is set to assume the primary key is numeric, for example a Autonumber column.

    If not the
    “lngParentID as Long”
    would need to be changed to for example
    “strParentID as string”
    as well as some modifications in the code to make it handle strings instead of longs.

    That said, I strongly urge you to use a autonumber primary key. It does not get affected by such issues as name changes (marriage/divorce) or 2 people having the same name.

  6. Norbert Meiss says:

    iI got the treeview working, but cannot change the font. The font tab in the properties simply doesn’t show. Is there a chance to change the font via VBA code?

    • TheSmileyCoder says:

      Hi Norbert
      What version of Office are you using?
      When changing the font it must be done through the ActiveX’s custom properties and not the active x container’s properties.

      If you still cannot change the font through the GUI, yes you can try to do it through VBA:
      TreeviewControl.Object.Font.Name="Calibri"
      for example.

      But if you still cannot change the font I will venture a guess that you are having a reference problem. But lets turn that stone when you have tried the above.

      • Norbert Meiss says:

        I use Office 2013 (32 bit) and this particular db was migrated from Office 2007. The treeview didn’t respond to events, but then I registered the OCX manually and the events worked again.

        However, the Font tab in the ocx properties did not show. Thanks for your code example – this finally works.

        • TheSmileyCoder says:

          Thank you for returning. I wonder what made the font tab not show. Maybe its a 2013 thing…

          But very pleased to hear that the code worked for you.

  7. Andrew says:

    Hey, your tutorial is awesome, keep up the good work! I have run into an error while attempting to adapt the code to handle multiple tables of data (each table holding a different level of the tree). I’m getting an “out of stack space” error on the recursive addChildren line within the addChildren sub. Here is my code:

    Private Sub addChildren(tv As TreeView, nodParent As Node, rsParent As DAO.Recordset, lngParentID As Long, strAttr As String)
    Dim strFind As String
    strFind = strAttr & lngParentID
    rsParent.FindFirst strFind
    Dim nodX As Node
    Dim strBook As String
    Do While Not rsParent.NoMatch
    Set nodX = tv.Nodes.Add(nodParent, tvwChild, , rsParent!projectDescription)
    Select Case rsParent!projectProposed

    Case True
    nodX.ForeColor = vbBlue
    Case False
    nodX.Bold = True

    End Select

    strBook = rsParent.Bookmark
    addChildren tv, nodX, rsParent, rsParent!themeID, “themeID=”
    rsParent.Bookmark = strBook
    rsParent.FindNext strFind
    Loop

    End Sub

    I’ve noticed when stepping through your code that it somehow jumps from outside of the loop directly to the rsParent.Bookmark = strBook line, however it does not do this in my code any longer, and it’s baffling. Help would be appreciated! I’m in Access 2010.

  8. Pavel says:

    Hi! Thank you so much for such great tutorial.
    Anders, could you please help me with my problem.
    When I add any control on the form containing TreeView I immediately get compiler’s error “Type Mismatch” on the string
    Set tv = Forms(“frm_TreeView_Example”).treeReqs.Object
    How it could be solved?
    Thank you
    Pavel

    • TheSmileyCoder says:

      Hi Pavel
      That sounds weird, and I haven’t run into something like that before. Are you using the sample download provided?
      I would suggest you start by checking the references to see if anything is missing.

      If that doesn’t help, try removing the treeview control from the form, and then adding it back in (And check reference)

  9. al capps says:

    I have my data in a single table row Level1ID, level1Text, Level2ID, level2Text, Level3ID,, level3Text level4ID, level4Text and I want how would you set up the structure to work. I see where you have a single ID and a level type. Is there a way to load the tree view using a this type of a structure.. not sure how to go more that one level deep.. Level1 and level2 are ok.. but the recursive call after that is what I am hung on.

  10. Caroline says:

    Just wanted to say thank you for the fabulous tutorials. They helped me a great deal to achieve what I needed to do.

    My rather complicated procedures are working great now, thanks to you.

    Much appreciated

  11. Jason says:

    Hello TheSmileyCoder,

    Very helpful your tutorials, so thank you.

    I am having a hard time finding any information on how to generate reports on treeviews. It seems recursively querying a treeview table is not something that is easily done in MS Access reports. Any ideas or links?

    Thanks,
    Jason

    • TheSmileyCoder says:

      It really depends on the structure of your data. If its a simple department/employees, then the report/subreport model fits nicely. Or really any tree where you know how deep it will run, i.e. how many levels, should be something you can cover by a report/subreport.

      If its single table hierarchical data of unknown depth, then either you need to use a different data table method such as a closure table, or a temp table. If you are using something like SQL server as your backend, then you could use a recursive query by CTE to get the data.

  12. Jason says:

    Thanks, that helps.
    I am still developing the DB and am not at the reporting stage yet, but I wanted to determine which design approach to take. You have given me good insight on that. Thank you,
    Jason

  13. Kieran Shirey says:

    Hi,

    Firstly, thanks for posting this valuable information.

    I have the same issue as Jessie:
    I’m using your tutorial to create a tree in my database. I entered the loadTreeview code using my data, but when I tried to open the form, I get the following error:

    Run-time error ‘2465’: Application-defined or object-defined error

    When I click “Debug”, it highlights the following in your code:

    set tv=forms(frm_TreeView_Example”).treeReqs.Object

    I tried your advice of: ‘So start by checking your references, and make sure a reference is set to
    “Microsoft Windows Common Controls 6.0 (SP6)”
    If it does not occur in your list, try browsing for it, its usually installed in:
    “C:\Windows\System32\mscomctl.ocx”’
    That reference is set but it has its location as sysWOW rather than System32. Do I need to place a copy of the mscomctl.ocx in there and register it or would you have another suggestion?
    Kind regards,
    Kieran

    • TheSmileyCoder says:

      I don’t know if its a copy paste error on your part, but the line
      set tv=forms(frm_TreeView_Example”).treeReqs.Object
      is missing a double quote, it should be:
      set tv=forms(“frm_TreeView_Example”).treeReqs.Object

      In this example, it expects to find a treeview control named treeReqs on the form “frm_Treeview_Example”. If you created the form yourself, you will need to rename the texts accordingly.

      • Kieran Shirey says:

        Thank you so much. Facepalm moment. You can look at it for hours sometimes and not see the smallest thing. I’m building an app at the moment to allow me to demonstrate predictive modelling tool so your tutorials are just invaluable and I will certainly make a donation. You may hear a little from me over the next few months but I’ll try not to bother you.

  14. Steve Hembry says:

    Thank you for the tutorial. I am completely new to VBA. What is the minimum amount of code required to show the top level node?

    I would like to get that functioning as a confidence boost.

    • TheSmileyCoder says:

      To just add a node (with no reference to a table) it might look like so:
      Dim tv as Treeview
      Set tv=me.treeviewControl.Object
      tv.nodes.add ,,"Test"

Leave a Reply

Your email address will not be published. Required fields are marked *

*