Treeview in MS Access: Part 6 – Creating New Nodes and Records

Introduction

This blog post is about how to use the treeview itself in combination with right click events as the platform for creating new records. This is a great feature since we can use the treeview and the mouseclick event to specify the location of the new record/node.

 

The video

As usual I have created a youtube video which shows all steps required. Hope you will like it.

 

 Downloads

You can download the sample DB here:

2007 format:

Download “Treeview Tutorial Part 6 (2007 format)” Treeview-Demo-Part-6-Add-New-Node.zip – Downloaded 4799 times – 94 KB

I hope you enjoyed this part 6 of my tutorial series, and hope to see you again for part 6: Creating New nodes/records

28 comments on “Treeview in MS Access: Part 6 – Creating New Nodes and Records
  1. Matt says:

    This is a great start for developing Treeview controls. Have you developed any further additions to this? I’ve tried it out and its easy for a user to incorrectly use this tool so I was wondering if you have any other simple treeviews like this example but with more robust prevention of simple user errors. For example there are a lot of instances where using the tool rendered the error invalid use of null.

    • TheSmileyCoder says:

      Hi Matt
      I have not published any further tutorials on the treeview at this time. But I have created several applications using the treeview, and to a much more advanced degree than what the tutorials show.

      The most common reason for getting a invalid use of null, is if you are doing edits in VBA while the form is live (Open in normal mode). Some changes reset the vba environment, and with that the treeview. So to do proper testing, I would encourage you to always close and re-open the forms between any VBA edits. (Data edits are of course ok)

  2. Matt says:

    Thanks for the response. I had given up on the treeview years ago because I never found a useful example such as yours. Now I’m taken serious look at using a treeview. Not sure if you are able to but it would be great to see any advanced examples of the treeview where you use the same methods as your example. Thanks again.

  3. Matt says:

    Drag N Drop
    One of the reasons a lot of us are on here looking at your tutorial is also to find an example such as this that uses drag and drop functionality. That would open up a lot of possibilities for this treeview control. Thanks

    • TheSmileyCoder says:

      I’ve been thinking about making a drag-n-drop video for a while, but I’ve been swamped lately doing a major home renovation.

  4. sachin says:

    can we build or made MLM & TREE structure concept in excel please give a some suggistion awaiting for your reply

    • TheSmileyCoder says:

      If you create a user form in Excel, you can have a treeview control in that. Without a more specific question, I can’t really give a more specific answer.

  5. cjeukens2013 says:

    When i try any of the insert options, I get a run-time error 3426 “the action was cancelled by an associated object”.
    In the debugger the line “frmReq.Recordset.AddNew” is highlighted.

    Where do I go wrong?

    • TheSmileyCoder says:

      If you download my demo file, and open it while it is still in the zip file, it will open as read-only. Thus it cannot create new records.

      If you move the demo out of the zip file, it should work “normally”.

  6. Arno says:

    Hi SmileyCoder,
    Im trying to use your “Treeview in MS Access: Part 6 – Creating New Nodes and Records” but I am getting a runtime error 35602 (Key is not unique in collection) when I try and add an entry.
    I currently run Windows 7 64bit and Office 2013 32bit.
    Do you have any ideas on how to rectify this issue.
    I appreciate you help.

    • TheSmileyCoder says:

      Hi Arno
      Thank you for your interest. In which manner are you trying to add a new record? Directly in the table, or by using the right click menu?

  7. Süleyman Erarslan says:

    Hi Smiley Coder,

    Thanks for your contribution for my almost first attempt of code development with access 2013. I followed your treeview videos and establish an application.I am using insert command and adding a new node to the treeview. Everything goes well.When I added new node (It is empty with juct prefix as I needed) my cursor getting on new added node as designed in the code. Control Subform record is returning as I what I added(since my cursor on the new node) when I started to navigate to other nodes,all the nodes are being reflected as correct record data into control subform but new added. It is reflecting the data at the 0 position (first recorded data).
    I tried many ways as non-familiar code developer but I couldn’t succeeded.
    Could you please advice your solution (if any)
    Thanks in advance
    Regards

    P.S:If possible,I would like to send you my sample app.

    • TheSmileyCoder says:

      Could you send me a couple of screenshots of the issue? My email address is SmileyCoderTools[removathis]@[andthistoo]Thesmileycoder.com

  8. Salman says:

    i have seen the demo you have made and i liked it but the problem when i use it in accesses 2015 and id like know how to handle the null problem when adding new main header node like in the example “Drink Vending Machine (DVM)”, so if i add new node on top or below (not inside) it gets an error saying “runtime error ’94’: invalid use of null, i have tried fixing it then i made it worse (in addnew function , it highlights “lngParentID = DLookup(“ID_Parent”, “tbl_Reqs”, “PK_Req=” & lngID)” for some reason

  9. Marco says:

    Hi, SmileyCoder

    My knowledge of Database and SQL is really very very poor. I think it would be enough though to adapt your sample database with treeview to my needs. The problem is that, in my case, one row should have many parents. I think it means that i would need to substitute the ID-Parent column for a table that relates rows of the tbl_Reqs table. I think it means that the same row of the table should appear in many different nodes of the Tree. Does it make sense? Is it possible to do that? What changes should i do to your Database Treeview in order to solve my problem?

    Thank you very much in advance!
    Regards

    • TheSmileyCoder says:

      Hi Marco
      You are correct, you would require a junction table (which is used for many to many relations in Access). The main tricky part in such a situation is to take care to avoid an endless loop. I.e. if A is a child of B and B is a child of A. The nodes would continue to be added until you run out of memory.
      As to the actual implementation, I can’t really say anything generic. Its really a matter of what fits for the application, which I simply don’t know enough about.

  10. Peter Keegan says:

    Hi, SmileyCoder
    Thank you for providing the Treeview Demo files and lessons, they have helped me understand Treeview. In my project I am using the Common Controls 6.0 (SP6) – “MSComctlLib.TreeCtrl.2” treeview version. I do not understand how in your “Treeview-Demo-Part-6-Add-New-Node.zip” you are using the “text” argument for the next node “relative” instead of the index or key. It works in your project but it is not the documented method and it does not work in my project. I need to use the “key” argument in the “relative” argument.
    Am I missing something?
    Thanks
    Peter

    • TheSmileyCoder says:

      Hi Peter
      I assume you are referring to this line of the AddChildren method
      Set nodX = tv.Nodes.Add(nodParent, tvwChild, genReqNodeID(rsReqs!PK_Req), Left(rsReqs!mem_Req, 50))

      Now I can see why you might be confused. If you hover over nodParent while in debug mode, it will show the TEXT of the node. If you try to debug it by writing
      Debug.Print nodParent
      it will print the TEXT of the node. That is because it is an object, and it has a default property, namely the Text property.

      Now I will admit its a big of “black box” to me exactly how/when VBA decides to use the default property, instead of the object. I expect that whenever you are in a situation where the method expects a Variant/string/long, such as when doing debug.print, it will use the default method. But the ADD method of the treeview is flexible, in that it can both accept an object (the object has to be a node) or a string (Which should be a key of an existing node).

      So my code is actually passing an OBJECT, and not that objects text property.

      I hope that was a good enough explanation to clarify it for you.

  11. Ozzy Dincarslan says:

    Fantastic set of videos on the use of treeview control. I’m creating one for the first time and it has been immensely useful so far. Thank you so much for all your work on this. I’m not a very experienced programmer so it took a while to understand everything but you explain things so well, it’s brilliant. For my database, I have to automate parts of the data entry task which I haven’t quite figured out yet. My table is set up so that the person doing the data entry will select the value of the parent node via a dropdown so that we know which node the record will be added to as a child, then the entries will need to be sorted alphabetically automatically within each parent. Is there an easy way to do this? Or am I biting off more than I can chew as a first attempt? 😀

    • TheSmileyCoder says:

      It depends. Do the parent and the child records come from the same table?

      • Ozzy Dincarslan says:

        Thank you for response 🙂 They will be joined via a query and records will be based on that query.

        • TheSmileyCoder says:

          In either case it can be done quite easy, although explaining it without the proper context (I don’t know what your data/business is) is actually quite difficult.
          But imagine we have a a table of Departments and a table of Employees. Imagine we have already added all departments to the treeview, and now want to add employees as children nodes. Now if we open the employees table as “Select * from Employee order by Department, FirstName”, and then add them to the treeview, they will be ordered in the treeview, because the recordset was ordered.
          Does that make sense?

          • Ozzy Dincarslan says:

            Yes, absolutely makes sense. My setup is a bit more complex with many-to-many relationships but I’ve been playing with it and my form is working great so far! Thank you so much 🙂

  12. Edward Marcinczyk says:

    Great videos! Is it possible to insert more at the Documents level?

  13. Cedric MALBRANC says:

    Hi,
    your videos are really usefull and so clear.
    Technical question : Is it possible to contract a node and all the node under that specific one using a function in rightclick commandbar?

    all my test have failed, Each time I open all level of a node. when I contract level 1 with righ click function it’s working. But when I’m expanding level 1 later. all other level are already open.

    tkx for your help.

  14. cedric malbranc says:

    I found it. yes!!!.

    thx for make us understand more clearly method, propery, obect and collection.

    sample if someone need it :

    Public Function cmdcontractBranch(intindex As Long)
    Dim i As Integer
    Dim tv As MSComctlLib.TreeView
    Set tv = gettv
    Dim nodx As Node
    Set nodx = tv.Nodes(“” & intindex & “”)

    If nodx.Next Is Nothing Then
    nodx.Expanded = False
    Else
    For i = nodx.Index To nodx.Next.Index – 1
    tv.Nodes.Item(i).Expanded = False
    ‘ Debug.Print Me.xTree.Nodes.Item(i).Text
    Next i
    End If
    ‘cleanup
    Set nodx = Nothing
    Set tv = Nothing
    End Function

    intindex = getID(nodx). same way you used to insert a node with rightclick commandbar.

  15. Tim says:

    To add nodes, you have: Insert Above, Below and Inside, can you give me the code to remove a selected node?

Leave a Reply

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

*