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:
Author:TheSmileyCoder
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!
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.
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
😀 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!
Hi Sidnei
I don’t have any good solution for that. If you want to use treeviews in 64 bit office, I believe you are limited to using a 3rd party dll or trying out this vba based approach by a bunch of Access mvps.
http://www.jkp-ads.com/articles/treeview.asp
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
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
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
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?
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.
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.
Sweet! Thank you so much.
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?
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.
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.
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.
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.
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
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)
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.
Hi Al Capps
It sounds to me like your data structure is not normalized. Before writing any code in a project, you must make sure that your data model is correct and normalized. You can start by taking a look here:
http://bytes.com/topic/access/insights/585228-database-normalization-table-structures
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
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
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.
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
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
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.
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.
No worries. We have all been there a couple of times.
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.
To just add a node (with no reference to a table) it might look like so:
Dim tv as TreeviewSet tv=me.treeviewControl.Object
tv.nodes.add ,,"Test"
Hi,
I tried to build up treeview. During the data loading there is an error. “Compile error: A module is not a valid type”
“Private Sub addchildren(xyz…)” is highlited.
This is the call: addChildren tv, nodX, rsReqs, rsReqs![5WhyID]
And this is the sub: Private Sub addChildren(tv As Treeview, nodParent As Node, rsReqs As DAO.Recordset, lngParentID As Long)
Could you help me what’s wrong?
I don’t think I’ve ever come across that error before. Have you by chance named your module the same as the function?
Hi,
Thank’s for all you did, it’s very helpful
I have a question abot Treeviews
I want to remove duplicate nodes, how can I check if a child node exists before adding it to the treeview
Thnk’s in advance
Kind regards
El-Houssine
The best way would normally be to ensure that the query feeding your Treeview doesn’t contain duplicates, but I know there are cases where that might not be feasible.
The key of a treeview node must be unique (and not start with a number), and usually I use the ID of the record along with a prefix to ensure its not a number.
I don’t know of a way to check for a duplicate key, without triggering an error, so I find it easiest to write a small function, which will handle the error.
Public Function node_exist(ByRef tv As TreeView, ByVal key As String) As Boolean
‘This function will return True if node exists, and false otherwise
On Error Resume Next
node_exist = tv.Nodes(key).Index <> 0
End Function
Hi Smiley,
I just viewed several of your videos and tutorials on treeview. I just upgraded my office to 2013 and treeview works fine on my computer with the latest ed of Windows 10.
My question; however, has to do with structure.
Your table has more than 2 fields, my database has several tables with only 3 fields (tables ID and information, and an ID for a linked table backwards). The structure was made this way because several records duplicate in the respective fields so I made separate tables to limit redundancy (Main, Levels 1 through 5).
My treeview works until I get to the children. On your video you have PK_Req and ID_Type. My main table is tbl_Main with only 2 fields, MainCategoryID which I associate with your PK_Req and MainCategory which corresponds to your mem_Req.
In my subsequent tables (Level1 through Level5) are similar (inclusive) fields such as “MainCategoryID, Level1CategoryID, Level1Category” … “Level1CategoryID, Level2CategoryID, Level2Category”
My database has an error when it gets to “addChildren tv, nodX, rsReqs, rsCategory!MainCategoryID” which corresponds to your “addChildren tv, nodX, rsReqs, rsReqs!PK_Req” line of “Private Sub addChildren…”
The error is an Run-time error ’28’: Out of stack space and I’m presumming I need to associate this line to the next table which would have “my” Level1 information but I don’t know how to change the line. When I change MainCategoryID to Level1CategoryID it has an error since Level1 table hasn’t been called or identified within the module.
I don’t know if you still monitor this blog, but if you do, thank you for your time and attention to this message
Out of stack space sounds like you have some recursion gone off the deep end, and it continues to add more nodes until the system is out of memory.
Hi SmileyCoder,
I have resolved that issue and my treeview is now working 6 linked databases which have the following structure:
Main:
MainCategoryID
Main Category
Level1:
MainCategoryID (Linked to Main)
Level1CategoryID
Level1Category
Level2:
Level1CategoryID (Linked to Level1)
Level2CategoryID
Level2Category
Level3 through Level5 similar structures.
The tree continues to work as I have placed some variables in to do comparison checks until it gets to about level3 data then it does a jump on the variables and loses count.
there is a similar if then check on each level and it checks to see if there is a match in id # comparisons such:
Do While Not rsL1Category.NoMatch
‘validation
L1_MCatID = rsL1Category!MainCategoryID
L1CatID = rsL1Category!Level1CategoryID
If L1_MCatID MCatID Then
If L1_MCatID > MCatID Then
GoTo Exit_L1DoWhile
ElseIf L1_MCatID < MCatID Then
GoTo IncreaseL1ID
End If
End If
'end validation
….
….
….
Loop
Exit_L1DoWhile
End Sub
After Main (the true first level), which doesn't need a check, each level (Level1 or L1 through Level 5 or L5) has this check to make sure it is still comparing the same row.
the tree works until it reaches level 3 and then completes without the rest of the data.
As a side note, this is a creation of the levels of eBay categories which was created from an excel *.csv sheet.
Any help would be appreciated.
And thank you for responding to my original post, I sincerely appreciate your time.
Ken
correction, it is working 6 linked tables, one database
In theory its possible to do it your way, but I wouldn’t recommend it. Imagine if you decide some level 3 category is now a level 2 category? You would need to move all sorts of records around between tables. It really should be in just a single table.
Its really hard to give more specific advice without having the database in front of me. Sorry.
I understand what you are saying. I was able to fix it to see all 6 tables, and if you would like, it would be a pleasure to send you the database to get your input.
The correct solution is to load the data into a single table, and work at it from there.
I am afraid I don’t have the time availble at the moment to offer more support on this.