Treeview in MS Access: Part 1 – The basics, loading information into the treeview

Treeview in MS Access: Part 1 – The basics, loading information into the treeview


Hi there, and welcome to this demonstration on how to use the treeview in a Microsoft access database The Treeview is part of the Microsoft Common Control Library. Now before I start I am going to quickly show you what information I am going to be loading into the treeview Now, in this example I want to load in a Requirement Specification document For this example I have simply prepared a small Requirement Specification for a drink vending machine.
It has a few chapters – chapter 1 and chapter 2 and chapter 2.1 Coffee The Drink vending machine shall have these following requirements that must be able to fulfill. As you can see there’s sort of a structure to this document and I am going to recreate the same structure within my treeview You can see the treeview here as it’s going to look once I am done. I have even taken the liberty of formatting a few of the nodes to represent headers So now I am going to show you how this is all done First I will show you the table that is currently storing the requirements. You can see them here. It has a text part That is the text part you just saw in the excel document. It has a type specifying – whether it is a Document, a header, a requirement or a guide as you can see Number one, type one this is the document.
It has a header “Introduction” and a header “Technical Requirements” And later a subheader “Coffee” and a subheader “Tea” The parent column over here, is a link directly back to the same table “Introduction” and “Technical Requirements” are both related to parent one, which is the document Which has the primary key of one Furthermore there is a sort column specifying which order that different requirements shall arrive in or, shall still be shown in I will get back to the sort column a bit later, especially when I get around to creating new rows in my table, so back to the treeview I am going to start right over by selecting my treeview, deleting it, so I will be starting from scratch I take, and insert a treeview – It is an ActiveX control Scroll down to Microsoft and There it is, Microsoft Treeview Control 6.0 And you can see it here, I am just going to move it, resize it, finally selecting properties and renaming it to treeReqs Reqs short for requirements Lets have a look at the code required to load our nodes into the treeview. I am going to click the “view code” and this takes me into the module Another way could be to take my form, select its properties and look at the load event since this is the event I’m going to start using. So when the form loads I also want my treeview to load the nodes “Call LoadTreeview”
Now “LoadTreeview” is a procedure I haven’t written yet I am going to insert this in a separate module Public Sub LoadTreeview and this is where I start loading the treeview. First I need a reference to the treeview Dim tv as MsComCtlLib.Treeview I don’t actually need the Microsoft Common Control Library Part, you could just write Treeview but for some reason I always prefer to type it out Set tv=Frm_Treeview Set tv=Frm_Treeview_Example So this is my form, I just need to get a hold of it like this:
set tv=forms(“frm_Treeview_Example”) In this form I have a control called treeview_Reqs This is the the control that is storing the ActiveX object I need to get the object inside the control and thus I add this .Object to the end. I now have a reference to my treeview I am going to start off by clearing any previous nodes from my tree view So, So far so good, I now have a blank empty treeview I need to be able to load my record into it so first I need to get hold of my records
Dim RSReqs as DAO Recordset
Set rsReqs=CurrentDB.OpenRecordset Select everything from Tbl_Reqs order by ID_Parent and then dbl_Sort and specifying the type of recordset So I want to start by adding the documents, they should be the top most nodes I get to see So first I have to find my documents Mydocuments are the ones that has a type of 1 So rsReqs.Findfirst strFind so Do While rsReqs.NoMatch Of-course do while Not. So as long as I keep finding matches I want to continue working on my recordset So at the bottom of this I add a rsReqs.FindNext and use the same strFind criteria, so if I have more than one document this loop will continue to process the next document Dim string sorry, Dim NodX as Microsoft Common library dot Node So this is my note.
Set NodX=Treeview.Nodes (This is the collection of nodes to which I add this node) There’s a relative part and a relationship part, and I’ll get back to that in a bit. Skip directly to the key, I will actually skip this as well, The text I want to show that is from the rsReqs the field I want to display its called mem_Reqs, the mem indicating it’s a memo type field I will also get back to the image part later So this starts by adding my documents to the treeview.
Lets try loading the form and see what happens ups, small typo Just compile it, and lets try loading the form Ups, its in break mode As you can see my document was loading, the Drink Vending Machine, that was the document. Now I need to start adding All children requirements, children headers in order So AddChildren() Now, I haven’t written this yet so I also have to write that part Private Sub AddChildren() now I need to get the treeview so I can know which treeview to add children to I need to get the node, now I write nodParent because this is the parent node that I want to add the children to I need to get the recordset, so I have something to work with and finally I need the ID of the parent There we go. Ok, and of course I also need to add this up here So the NodX is what becomes the parent off the AddChildren procedure and the parent ID that I get from the requirements PK_Req, there we go So now I need to add the children Going to add a new strFind This time I want to add all the children that has the value of ID:Parent equal to the parent is just was working with Again, Do …. search for the first match .FindFirst strFind And then a loop, Do While Not rsReqs.NoMatch loop once again at the bottom, rsReqs.FindNext strFind Now since I started by opening my record set and then ordering by the ID parent and then that the dbl_sort this should be fairly fast because the requirements will come in the correct order and the sorting has already been taken care of Now I need to add nodes again Dim NodX as node Set NodX=tv.Nodes.Add(….
And this is where the relative and relationship part come in for the node X, I am about to add it has a relative, that is the parent node and the relationship is that this current …. the node I am about to add its a child so I use the tvwchild constant again I am going to skip to key but I will get back to this later. rsReqs!mem_Req and I need to recursively call the AddChildren so any more children can be added like so: This compiles just fine, finally there’s one more thing I need to do because we’re going back and forth in the same record set and I’m constantly using the rsReqs.FindNext I need the order to be preserved so what I’m going to do is create a string and store the current location of the record set in the string I can use the bookmark property of the recordset Now I have stored the bookmark into the string and I now return the recordset to the location it had before running the AddChildren procedure I’m going to do the exact same up here There we go Compile it and close the form Save it and reload it Lets see….yes The requirements were loaded, just like they were meant to So now, how do I get the +sign to show up here.
I can do that by switching into design view clicking my control and up here, you can see the properties of the ActiveX control. There is also a properties down here at the bottom But that’s the properties of the control which is the container of the ActiveX object, and the ActiveX object up here, thats the properties that I want to access The line style I will switch it to tvwRootLines Click off this, (HideSelection)
Set SingleSel to true,so that I will only be able select one node at a time and click apply As I look now, you can see that even my first node has a plus and minus sign, this is a matter of taste whether or not you want to have this so now I want all my headers to be bold.
I will go and look at that So after adding my node I want to do a select statement. I will do a select statement, Select Case rsReqs.ID_Type and select Case 1 ‘ That’s the document, now since we’ve already added the documents up here the document should never occur down here and just listing it’s so people don’t wonder where it went Case 2 ‘ that was the header so
NodX.Bold=True Case 3 ‘ That was the requirement Now I just going to have that as normal text Case 4’ That was the guide text. I will get back to what a guide is, and how it works in the requirement specification …. for now Just be satisfied that it is something that is different from the requirement and thus I want to show it by changing the fore color, thats the text color of my node.
I will just set it to vbBlue for now Finally up here, since all of these are documents I also want them to be bold.
NodX.Bold=True Now I could reload this form to get the treeview to load, but I could also just run the LoadTreeview procedure directly from here as I switch back to my form, you can now see it has been reloaded and all the header nodes have been bolded requirement nodes are back to normal and you can see the guide that I had up here, under “Introduction”. The guide is not a requirement it is just a helping text, and you can see the guide is blue just like I wanted it to be One final modification I will make in this demonstration is switching to design view Into the properties and change this indentation value, I am going to set it to 200 instead of the 500 it was You can now see a spacing between each node. The indent has been significantly decreased. I like this mode better, it has more space can also see that some of these text parts go quite far out you can solve this to some degree by switching back into the code view, and here simply say I only want the first lets say 30 characters of the requirement to show in the treeview in the later demonstration I will show you how you can use a treeview to navigate to the specified record and of course as we navigate to the record we can see the full text directly in the record Lets just load our Treview and see how it works You can now see this is cut off at 30 characters Perhaps for this treeview, 30 characters was a bit small. I will just change it to 50 instead Load my Treevie Now this seems to be quite fitting for the amount of space that is available Thank you for watching this There will be more videos coming

Only registered users can comment.

  1. I had the same problem with finding the correct reference. For other users here is what needs to be done:
    in VBA go to "Tools", "References", click "Browse" and in the C:WindowsSystem32 folder select the MSCOMCTL.OCX file.

  2. @Thomas Andersen
    You are quite correct. I suppose it could be a version issue. When I add the treeview control in Ac2010 the reference is automatically added for me, but perhaps it is not so in earlier versions of Access. Which version are you using?

  3. Odd. Using ac2010 on windows 7 myself, but I would have thought it would not be dependent on the windows version. Maybe something else I am just not seing.
    Anyway, best of luck with your project.

  4. Thanks.
    And by the way thanks a lot for making and uploading the video. It was just what I needed to get my treeview form up and running 🙂

  5. excellent introduction to using the TreeView control in Microsoft Access, thanks Anders ~ Crystal ~ have an awesome day ~

  6. Thank you Crystal. Glad you liked it. It means a lot coming from someone with your experience in creating online video tutorials.

  7. Don't forget to watch the rest of the tutorials. Just released part 6 on how to create new nodes/records from the treeview.

  8. Thanks for this Anders… looking forward to going through all the videos in this series. At the 13 minute mark you configure some of the TreeCtrl Properties… for me, all the drop-down options are empty for me – you wouldn't know what's causing this?

  9. Hi Matt
    I haven't heard of that happening before. I would start by checking there is a reference to the treeview controls library. Open your VBE and look at the reference for "Microsoft Windows Common Controls 6.0 (SP6)". On some systems the reference must be set manually. From the reference window browse to your windows directory and select the MSComCtl.OCX file. If that doesn't work, I need some more info on your Access and windows version (and 32 / 64 bit) to be of more help.
    TheSmileyCoder

  10. Dear ThesmileyCoder
    I have done all as you explained in your this session, but unfortunately i am unable to have the child node under the parent node instead all the parent and child nodes in a single column shapp, furthermore, the parent node which i added first is coming in the last and all other nodes are adding in the reverse order, furthermore no plus minus sign is being appeared. Can you put some extra light on it
    Regards

  11. I would ask you to please could you help me complimenting the code you need to double click to give me the node aperturar allow a form that indicated in the table, I have an example but I can not adapt to your example , I would beg if I can help , I need to pass an access cycle , TControl and NControl variables are 2 columns which I have adapted to the form, the first reference TControl if F is a form and the form name NControl you want to open . I can adapt these codes as your example that

  12. Help me with a code to match the tutorial one the double click option with 2 forms aperturar TControl and NControl adjoining columns, the first identify varibles F = Form; C = Check, R = Report, S = Exit or DoCmd.Quit and A = back or return NControl column and put the names of the forms I want aperturar, thanks in advance

  13. Hi Cesar. Sorry but I have no idea what your are talking about. I would suggest you post your question at a access site like UtterAccess which is more suited for this kind of discussion, as youtube posts are somewhat limited in both length and formatting.

  14. No matter leave, thank you, yet there has been no expert who can answer my question, maybe I can do it despite my limited experience, Greetings

  15. Youtube is good for sharing videos, its not good for a question/answer type of scenario. I suggest you visit UtterAccess and post a question. You will find hundreds of experts and several Access MVPs, and myself ready to help you understand and use Access. That forum is just better suited for posting technical questions, and supports attachments and links, which youtube does not.

  16. I'm getting a 'Type mismatch' error on 
    Set tv = Forms("[formname]").[nameOfControl].Object
    Do you know what's wrong?

  17. hola buenas tardes 

    quiero pedirte por favor ayudame!!  encontre tu video en youtube y me he guiado para hacer el mio, la primera parte va todo muy bien pero ahora al intentar crear un evento en uno de los nodos me sale error en esta parte del codigo  tv.Nodes.Clear porfa ayudame.

    te adjunto mi codigo para que lo puedas ver. gracias.

    Option Compare Database

    Public Sub loadtree()

        Dim tv As MSComctlLib.TreeView
        Set tv = Forms("BUSCA_GDT").Treefinal.Object
        
        tv.Nodes.Clear          'error 394 en tiempo de ejecucion  property is write-only
        
        Dim rsReqs As DAO.Recordset
        Set rsReqs = CurrentDb.OpenRecordset("SELECT * FROM Sistemas ORDER BY nivel", dbOpenDynaset)
        
        Dim strFind As String
        strFind = "nivel=1"
        rsReqs.FindFirst strFind
        Dim nodX As MSComctlLib.Node
        Dim strBook As String
        Do While Not rsReqs.NoMatch
            Set nodX = tv.Nodes.Add(, , , rsReqs!Nombre_Area)
            strBook = rsReqs.Bookmark
            addChildren tv, nodX, rsReqs
            rsReqs.Bookmark = strBook
            rsReqs.FindNext strFind
         
        Loop
    End Sub

    Private Sub addChildren(tv As TreeView, nodParent As Node, rsReqs As DAO.Recordset)
        Dim strFind As String
        strFind = "nivel=2"
        rsReqs.FindFirst strFind
        Dim nodX As MSComctlLib.Node
        Dim strBook As String
        Do While Not rsReqs.NoMatch
            Set nodX = tv.Nodes.Add(nodParent, tvwChild, , rsReqs!Nombre_Area)
            strBook = rsReqs.Bookmark
            rsReqs.Bookmark = strBook
            
            'Funcion para lo otro
            Dim consultaEquipos As DAO.Recordset
            Set consultaEquipos = CurrentDb.OpenRecordset("SELECT * FROM Tabla1 ORDER BY sistema", dbOpenDynaset)
            
            equipos tv, nodX, consultaEquipos, rsReqs!Id_area
            
            rsReqs.FindNext strFind
        Loop
    End Sub

    Private Sub equipos(tv As TreeView, nodParent As Node, consultaEquipos As DAO.Recordset, AREA As Long)

        Dim strFind As String
        strFind = "sistema=" & AREA
        consultaEquipos.FindFirst strFind
        Dim nodX As MSComctlLib.Node
        Dim strBook As String
        Do While Not consultaEquipos.NoMatch
            Set nodX = tv.Nodes.Add(nodParent, tvwChild, , consultaEquipos!Tag_equipo)
            strBook = consultaEquipos.Bookmark
            consultaEquipos.Bookmark = strBook
            
            consultaEquipos.FindNext strFind
        Loop
    End Sub

  18. This is a great tool.  Is it possible to apply a drag and drop feature? Can anyone provide any links to any working examples of this treeview in access database applications? Hopefully I'll have time to work on this.

  19. This seems to be WAY too complex just to populate a Treeview control. Why not just have a couple of parent and child items in a database and describe how to populate them?

  20. HI .. I have an acces database a mmaik form containes ather form want to cancel changes if save button isnot clicked..how can i do that..im asking if u can help me ..proff

  21. I'd like to thank you TheSmileyCoder very much for this nice video, which guided me on creating a TreeView object in my database!
    My only suggestion would be to include in its begging the instructions you gave in the comments section of your homepage in 2013 regarding the reference to the “Microsoft Windows Common Controls 6.0 (SP6)”.
    With warm regards from Brazil!

  22. That video series was awesome, I've been working with hierarchies in a very cumbersome, restrictive manner until I saw this! Appreciate the effort you put in 🙂

  23. Im really thanking you from the heart for demonstrating this code of treeview access database. I would like to draw your kind attention demo another access treeview code for opening access form accordingly on the node we double click and the form name should be called from the table as well. Anxiously waiting for your reply.

  24. I'd like to set PK_Req and, thus, ID_Parent as Text(String, like G1.1.1.2). What change do I have to make for that on strFind = "ID_Parent=" & lngParentID? looks like I'm having continuous problem on that.

  25. Hi, I'm trying to build the treeview using this tutorial but I'm getting an error 28 Out of stack and access closes. Could you please help with the code? my data has approx 1800 lines and 4 levels deep

  26. Hi. I followed your video to create a treeview in access 2019. when I run the code as per the 12:12 mark access locks up. I have tracked it down to it being stuck in an infinite loop becuase the bookmarks are not working. When I test to see if the recordset is bookmarkable Access state that it is not. I have searched but cannot find a reason as to why this is. Could you help? Thank you in advance.

Leave a Reply

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