Treeview in MS Access Part 3 – Refresh treeview on record update

Introduction

This blog post is about how to handle record updates so that the treeview is always showing accurate up-to date information. I have also included a few helper functions which will make coding with the treeview a bit easier.

The video

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

 

 

More explanation

This time I will try to also include at least part of the video content in the blog post as well. But for full value,  I strongly recommend watching the treeview, and/or downloading the Access database sample.

Updating the treeview

So we want to make sure that the information displayed in the treeview is accurate. This means that as a record is updated using our standard Access form, we want to also refresh the treeview. My usual approach is to simply reload the entire treeview. This works just fine if your treeview contains around 500-maybe 1000 records. If you use more then that amount of records in the treeview you might want to consider only partially populating the treeview, or having a way of selecting a subset of information before loading the treeview, or perhaps only refreshing the treeview instead of reloading it. 500-1000 records is what can be expected to load without a noticeable delay. More then that and the system might start to get a “sluggish” feel.

As written above, when the form gets updated we want to update the treeview. So in this case we use the forms Afterupdate event to trigger the code. The video has more details and followup on logic, this post will simply contain the finished code

Now this correctly causes the treeview to reload entirely, but now it can’t remember which node was the active one. So we need to tell the treeview to select the node which the form is currently standing on.

The node is now properly reselected after the treeview is updated. 🙂

 

Helper Functions

I want to have a easy way of referencing my treeview regardless of where I am in my code project. So I create a function which will return a treeview reference, namely THE reference to my treeview. Its quite simple as can be seen below:

The good part about using a helper function like this, is that as I type GetTv anywhere in my project, I now get to use Intellisense, so I can see all the properties, and methods of the treeview. Also should I wish to change to Latebound at a later point, I only have to change the single reference to make the GetTV return a object instead of a treeview. Note that this approach is slightly less effective then creating a local treeview variable. If you use it inside a loop while adding 1000 nodes for example I would recommend setting a local treeview variable and then setting that variable using the GetTV function. For example:

The last helper function is to make it easier writing the syntax for the node key. As you can see in Part 3 of my video series, as well as earlier parts, I use the syntax <ID>PrimaryKey</ID> as key for the node. So my helper function to convert the Primary Key to the Node key is:

If we take a look at the forms beforeUpdate event we can now rewrite it to look like so:

 Downloads

You can download the sample DB here:

2003 format:

Download “Treeview Tutorial Part 3 (2003 format)” Treeview-Demo-Part-3-Updating-the-treeview2003-format.zip – Downloaded 881 times – 44 KB

2007 format:

Download “Treeview Tutorial Part 3 (2007 format)” Treeview-Demo-Part-3-Updating-the-treeview2007-format.zip – Downloaded 1827 times – 43 KB

I hope you enjoyed this part 3 of my tutorial series, and hope to see you again for part 4. Using icons with nodes in the treeview.

 

 

 

 

Author:

Posted in Treeview
15 comments on “Treeview in MS Access Part 3 – Refresh treeview on record update
  1. Jesper says:

    Thank you, thank you, thank you. Your instructions make it look so simple. I love how you walk through every step of the process, including the buildup, instead of just showing a finished form.

    Looking forward to your next blog post!
    Jesper

    • TheSmileyCoder says:

      Hi Jesper
      Glad you liked it. Thanks for the feedback, that is the best motivator for me to keep posting.

  2. Leo says:

    I have office 2013. I can’t get my or your tree to update the subform as I click or move on the tree.

  3. Leo says:

    honestly I don’t know. It came on the Surface Pro. I can then assume that since the treeview is not working that I have the 64 bit version. I do have the common control ocx installed properly.

    • TheSmileyCoder says:

      I can’t say much about office 2013 as I have not yet tried it. Are you getting any error messages?
      MS released a patch mid 2012 that also broke references to the treeview library. For more info see
      http://www.fmsinc.com/microsoftaccess/controls/mscomctl/index.htm
      That could also be your issue as well/opposed to the 64 bit thing. MS still recommends to install Office 32 bit, so I doubt that if your computer came pre-installed that it would come with office 64 bit.

  4. Jack Farhi says:

    I’ve gotta tell you – these 3 part videos are spectacular – spent 1/2 a day working on this and it works great… any chance of getting the files for parts 3-6? Also, have a question – is it possible to populate the subform with all of the child reqs listed under the Parent? I’m using the subform as a datasheet and would love to populate the child reqs from your example….

    Thanks,
    /Jack

  5. mohamed says:

    it is fantastic really but i got error
    “runtime error 35601”
    “element not found”
    and when i press debug it highlight this
    “tv.Nodes(“” & Me!PK_Req & “”).Selected = True”
    WHAT IS WRONG?

    • mohamed says:

      dear smiley coder
      very thankfull for your fast replay
      but sorry I did not understand
      I am not that strong in codeing
      I have office 2010 and 2013
      and on both the sample is not working even on part 6 so I try to go step one by one
      try part 1,2,and on part three begin not to work and have a lot of error message so I go to video download sample 2 try to go step by step and when I write down the code
      tv.Nodes(“” & Me!PK_Req & “”).Selected = True
      that you wrote it give me the error I wrote in my post so PLEASE tell me how to fix
      and by the way part 4,5,6 DOES NOT work at all on both 2010 and 2013

  6. TheSmileyCoder says:

    If you want to use a KEY to reference nodes, it must begin with text, e.g. “Req” & me!PK_Req
    When you remove the text component, the nodes collection is now looking for the node with INDEX=pk_rEQ

    And that node might not even exist. The index is simply the order in which nodes were added, i.e. the first node added to the treeview got index=1.

    So in short, you need to keep the text part to force key comparision.
    Hope that helps

  7. mohamed says:

    i do not know why when i write (id) it become “” after i post

  8. TheSmileyCoder says:

    Did sample files 1 and 2 work? When you say sample file 3 did NOT work, does that mean it didn’t work at all after downloading it?

    All the sample files were made in Access 2010.

    If the blog editor is being “helpful” in formatting your text and removing some parts of it, you can try to email me instead. My email is in all my sample files.

  9. mohamed says:

    Thank you for you patient
    first of all all sample are working
    i mean by NOT working that there are -for me at least- many errors that happened which make the sample opened but DO NOTHING but error messages also i tried it on 2 PCs but same result
    i want to send a screen shoot for all the errors that happened and try to find your mail but failed i searched for it in all the tree view samples but did not find .
    could you please send to to my mail ?
    ALSO PLEASE do not give up on me as i said before i am not that strong in coding

Ask a question, leave some feedback, post a reply!