Getting Help

Access Community

First off, I want to say that I believe Access has one of the most helpful communities ever. I have often wondered why that is, and my own take on this is that most Access developers do not start out as full time developers. They start out as users, who become power users, who become actual developers who then become professional developers. They started out as users, and fell in love with the empowerment that Access can provide both for a developer, but also for the end-user and the business.

This means that a lot of those professional developers out there, they still remember what it was like the first time they tried to get the syntax right for using dates in a Dlookup function. They remember that feeling when searching the net and finding that priceless bit of help 100% free of charge, and now they are in a position to pay-it back.

Search for it!

If you get stuck on a access problem, don’t despair. Odds are that someone else has had the same problem before, so just start googling! I have found that if I include the keywords “Access” and “VBA” the odds of getting a useable link in the top 3 results are very good.

 Great online forums

There are plenty of great online forums with million (yes) pages of information on access. If you have searched for it and not been able to find the result you need, or the problem is too complex, well then you can try asking for help. Remember, asking for help is not a sign of weakness, its a sign of somebody who wants to learn, who will not settle for what he knows, but strives to become better, to improve the quality and functionality of his (or her) work.

Before you start posting questions though, try taking a look at this link:

http://whathaveyoutried.com/

Following some of the advice there, before posting your own question might either solve your question, or at the very least lead to an improved question, which again leads to an improved answer. Asking questions in a forum is not the same as asking questions face-to-face. Its important to show those site/access experts that you are serious about your question. If you spent 2 minutes jotting down a question with little details, and possibly plenty of spelling errors, odds are that the expert will skip over your question and move on to a more dedicated/detailed question.

These are some of the forums I personally have enjoyed using, and you can find many questions asked by me (yes I ask questions too), as well as even more answered by me.

http://www.Bytes.Com/Topic/Access 

This site also has a great insights section where experts have submitted some really useful and detailed articles. This is where I truly started my online identity, and this site has some great and truly dedicated experts.

http://www.UtterAccess.com

This site has over 2.000.000 posts to date, and several Access MVP’s troll around in here. Not to forget either, yours truly visit this site quite often as well.

 

Wait, there is more!…

Resource sites

Several of the Access MVP’s also run their own site, with extra bits of goodies for free. Here are a few of the links I have used in my time. They are listed in no particular order, but are all worth a visit.

Crystal Long

Crystal runs a site with a alot of intro material on Access, explained very nicely and thoroughly in video tutorials.

http://www.accessmvp.com/strive4peace/

 

Mark Davis aka CyberCow

Mark is a very active (17.000 posts) contributor at UtterAccess and also has his own site. He is also the person who has supplied me with music for my treeview part 4 video (and hopefully more to come). He has alot of demo tools freely available. Give it a look.

http://www.endprod.com/

 

 

Sites of interest:

http://bytecomb.com/what-is-microsoft-access/

http://www.fmsinc.com/MicrosoftAccess/developer/index.html

http://allenbrowne.com/tips.html

 

 

Author:

51 comments on “Getting Help
  1. David Lopez says:

    Great videos, On video 2 you copied and pasted a pre created set of code, and made a comment about reviewing the code to see what it does, but the video cuts off a portion of the code at the end, is there any way to get that from you, also will you be making a video about how to access multiple forms based off the treeview selection

    Thank you

    • TheSmileyCoder says:

      Hi David.

      I had not noticed that any of the code was cut off. I always intended to include the treeview sample databases but have just never gotten around to actually doing it. I have just released part 2 on my blog, with download links to the sample files. I hope you find them helpfull.

  2. Gary says:

    Loved your video on Treeview and how it had multi tiers to it, however I was trying to figure out how I can do this in 2 or more tables.

    I am currently in the US coastguard and I am trying to make my self a Parts database. I only need to list the Items by name but also show part information thorough the tiers in a subform. IE

    Tree
    -Chapter 22 Communications
    –VHF
    —Upper VHF Antenna
    —-Screw
    —-Washer
    —Lower VHF Antenna
    –GPS

    Subform
    part number etc

    I believe this can be done using 2 tables but any help would be greatly appreciated. Please feel free to e-mail me.

    Thanks
    Gary

    • TheSmileyCoder says:

      Hi Gary, and thank you for your feedback. I love all feedback and it really helps to keep me motivated.

      Yes, it is quite possible to show different types of information in a treeview, and I use that extensively in my projects.

      I am a bit confused about which parts of your example comes from the first table (lets call it tbl_Parts) and which comes from the secondary table (lets call it tbl_PartInfo).

      I will drop you a mail as well.

  3. Steve says:

    Sir,
    I have appreciated your videos on implementing the Treeview control. Can you point me in a direction for help in Access on how to implement a Treeview control in a Navigation pane such as you see within Outlook?

    Thanks in Advance, Steve

    • TheSmileyCoder says:

      Hi Steve
      I am trying to better understand your question.
      In what ways would it differ from what I have shown in video 2?

  4. Steve says:

    Sir,
    It is more a navigation control question than a treeview one. Is there a way in the Access navigation control for the buttons to spread open when one is pressed and a subform with a treeview to be shown in the control; between the buttons?

    Thanks, Steve

    • TheSmileyCoder says:

      To be honest I have not used the Navigation Control yet. All my projects originated in 2003 databases so I have yet to have used the navigation control. I would be carefull though, I have seen issues with a treeview control when used inside a tab control, that the treeview would on occasion visually appear at the wrong coordinates.

  5. Andy says:

    Hi,
    I saw your treeview videos, very nice 🙂
    But the download examples don’t work (neither part2 nor part3), if I switch between the trees, nothing happened in the subform. I use the sample 2007 and Access2013, may this be the problem?

    • TheSmileyCoder says:

      A while ago I loaded a treeview into Access 2013 and it seemed to work fine. At least the nodes were all properly loaded, and I was able to click nodes to expand and contract.

      Yesterday I found a machine with Access 2013 and downloaded my own sample to it, and while the treeview will load and display the nodes as it is meant too, I could not get the treeview to react to nodeClick events, nor were I able to see the events within the coding interface. I will need to research this a bit further.

      Ps. The treeview will NOT work in any 64bit version of Office *(Windows can be 64bit, as long as office is 32bit)*. There is nothing that can be done about this, besides looking for another treeview control to use.

    • TheSmileyCoder says:

      I have now done some more testing, and while the treeview will load, and display treeview nodes, I can’t seem to get it to react to node clicks, and I cannot seem to get to the properties within the object.

      • Andy says:

        Thx for testing. I use 32bit version. It’s a pity that Acc2013 seems to have problems with this nice function…
        Regards,
        Andy

        • TheSmileyCoder says:

          Thank you for your comment.
          If I get the time, I might try to look into whether there is something that can be done to make it work in Access 2013, but at the moment I have more pressing matters to attend.

  6. Richard says:

    Hi Anders,
    I am a rather enthousiastic, but unexperienced VBA-user. I make simple databases for my clients for a long time using as much as possible the standard wizzards. My clients are large and small customers in the production branche (oil & gas, powersupply, (fine) chemical, etc.). Treeview we use e.q for assettrees.

    I enjoyed your lessons with treeview and learned a lot from you. However the use of the icons (expand/collapse) in lesson 5 doesn’t work in my newer version (???) of Access 2010. Rightclick gave an 438-error and revers to the mod_treeview:

    Public Function getImageList() As MSComctlLib.ImageList
    Set getTV = Forms(“frm_TreeView_Example”).IlForTreeview.Object
    End Function

    In your explanation you implement Microsoft Office 14.0 Object Library, but in mij version there was only 15.0. I don’t think that this is the problem, but…
    Maybe you can help me out.
    PS If neccessary I can send you the zipped database if that may help.
    Greetings, Richard

    • TheSmileyCoder says:

      Hi Richard
      I can’t spot from your description of the problem what might be the issue. You are welcome to send me a mail with your database (zipped) and I will take a look at it.

  7. Richard says:

    Hi Andres Ebro,

    Here I am again and this time with a part of my database (Database Asset Tree vs 48). I really appreciate that you are willing to have a look after it.
    But first let me say it again: the way you explain how to make a Treeview in 6 lessons is really great! I can hear at your voice, you’re very enthusiastic. So do I (although I am already 61 years of age ––and still feeling very young, because of my children and grandchildren ! ).

    OK, to the point now. I send you my database just to look after it and give some advices (and maybe you can help me out with some code).
    When you look for how to set up a Treeview in Access 2010 on internet, you will find a lot of different methods and that’s the first thing which frustrates me a little (more ways lead to Rome). I prefer the way you explain it.
    A nice example which I found was “TreeViewWithDragAndDrop”. But what a code!!! It will last a long time before I get at that level..

    When you use a Treeview for Asset Management, it is necessary that is possible that you can add, delete, move, copy, paste, cut records (assets), etc.. When you right click on a node in my database you will see what I mean.
    Also the drop & drag function should be in it to make things going easier.

    Further it is necessary that there’s a certain hierarchy in the different types: Customer -> Location -> System -> Equipment -> Component -> (Spare)part
    You start with the customer.
    One customer may have more than one production location all over the world.
    On a production location the production installations consist of more separate systems.
    A system is build up from several equipments, which sometimes exists form several components.
    Spare parts are related to components.
    And also e.g. it shouldn’t be possible to link a component to a system, etc.
    Pfffff.

    Of course in the end I have to spend some time to get the error handling working fine.

    PS Wouldn’t it be a great idea to go on with lesson 7, 8, 9 etc. in which you explain how to manage this?

    I realize that there is a lot to do before it’s proper working. I have to do it (probably just like you) in the evening hours, but in the end it will be great!
    I hope you have some time to help me out and get me in the right direction for solutions.

    Greetings, Richard

    • TheSmileyCoder says:

      Thank you for the sample you sent. Certainly an interesting project. I have sent you an updated database with some drag-n-drop examples. Hope you like it.

      • Richard says:

        Thanks a lot for your effort!. The drop & drag functions works fine. The attached comment in the VBA also explains a lot. I only need a small adjustment so that’s possible to set “systems” under “systems” (some locations have systems and subsystems). I also tried to get the “delete”-function for node + children get working by a lot of different manners according to the MS-Developers-site, but unfortunately I didn’t succeed until now. Maybe you have some suggestions. I feel free to send you may database again. Hope you have some time to look after it.
        Thanking you in advance, Richard

  8. Marcel Frauchiger says:

    Hi there,
    I have a question about the treeview. Each child does have a status and a corresponding Icon. Now the parent node should reflect the Status of the child with the biggest severity level.
    Do you have any idea how to implement it? Any help is appreciated, thanks a lot.
    Kind regards
    Marcel

    • TheSmileyCoder says:

      Hi Marcel
      2 options spring to mind. One is that you calculate the maximum severity in the query that you use for the parent node, and this might be the preferred way. It could easily be that you need that same query/status elsewhere.

      The other option is to check the parent image against the severity of the child node when you are adding child nodes.
      Imagine if we have severities 1 to 8, and the corresponding images are named “Severity1” to “Severity8”.

      Now as we add each child node, we check the severity of the parent (Aircode):
      Set nodChild=Treeview.Nodes.Add(nodParent,tvwChild,Recordset!KeyField,Recordset!TextField)
      'Get severity of parent
      dim lParentSeverity as Long
      lParentSeverity=Right(nodParent.Image,1)
      If lParentSeverity

      I hope you can use either of the two approaches.

      • Marcel says:

        Hi Andres Ebro,
        Thanks for your help. I will extend the database queries so that I have already the correct status when creating the parent node.
        Cheers Marcel

  9. Marcel says:

    Hi Andres Ebro,

    Me again. Further question: Do you have coded functions available for copy and paste a parent node with all childs as well ass for drag and drop a node (parent with childs andf a single node)? I hope you don’t mind my requests.

    Cheers Marcel

    • TheSmileyCoder says:

      No, I don’t actually have functions for copying nodes, I’ve never really had the need. It’s also something that would be highly situational. For example if your are copying all the children nodes, do you want new independent records (i.e. nearly duplicates of the originals) or should the child item simple be made to refer to multiple parents in a many to many relationship? Such kind of things would need to be known in advance before writing the code.

  10. kentyler567 says:

    Actually, I have written,cut copy and paste functions for your basic implementation of that treeview… i’d be glad to share.

    But the reason i posted is that on some computers the treeview causes a “vb project is corrupt” warning and the access application refuses to open.
    The same application runs fine on other computers… if i remove the treeview from the application it does not give that message …. have you had any experience with this ?

    • TheSmileyCoder says:

      I’m sure there are people who would like to read what you have to share on the subject.

      I’ve never seen such a message myself. Have you tried decompiling? Are the computers in question using the same version of Access?

  11. Bo says:

    Hi
    Tried changing your requirement hierarchy into a general classification – in this case I used the APQC classification of processes into Category > ProcessGroup > Process > Activity > Task > Subtask so I expanded the type table and renamed the types.
    Works like a dream – in my case eg a process (level 3) can be subdivided into more processes – still level 3 in terms of type but sometimes a child of another level 3 – no problem.
    Got an error 13 along the way – not sure why – might be something with the sort sequence
    Fumbling around I missed the pop-up of Delete (cascading delete)
    Saw your post on Access 2013 issues – I had no problem running this on 2013.

    • TheSmileyCoder says:

      Thank you for your comment. An error 13 is a type mismatch. It could be caused by trying to provide a string where a integer is expected. Without knowing the line of code on which it errors, its hard to assist more.

  12. Bo says:

    Fumbling even more – I miss a move (or move up move down) capability – which naturally can be handled by the parent (red) data element – not sure if I then mess up the sort red data field

    • TheSmileyCoder says:

      I’ve started the rough outline of making a video on Drag-n-drop. Drag-n-drop is interesting because you might have special restrictions. It could be that you want to allow the use of drag-n-drop to reorder children nodes, but might not want to allow the children node to be dragged to a new parent. So right now I am trying to narrow down how to show a powerfull example of drag-n-drop, without overcomplicating the matter.

  13. Bo says:

    Yeah, see your point. I think basically it comes down to your hierarchy – is it objects of the same type (for APQC and BIAN it is really functions containing functions containing functions or processes invoking processes invoking processes) or are we talking about a hierarchy of object of completely different types like Document and requirement

  14. Marcel says:

    Hi Andres Ebro,
    I have a sub form as a continuous form in a parent form. I click through the records and then display the corresponding Information in the parent form. Now, based on a flag I don’t want to go to the record but remain on the highlighted one. I inserted the code in the form_current event of the sub form. If the flag is set then use the DoCmd.GoToRecord to go back to the previously selected record. But with that the form_current event is fired a second time. How can I solve this bug?

    Any help is very much appreciated. Thanks and cheers
    Marcel

    • TheSmileyCoder says:

      Hi Marcel
      If you post your current code it will be much easier to understand exactly what is going on. If the current event is firing twice it is likely a navigation problem, but I can’t say for sure without the code used.

  15. Cristian Prifti says:

    Hi Anders,

    Thank you very much for tree view videos! They were much of a help! 🙂

    I have two questions:
    1) Is there a maximum length applied to it ? Like the ’22 height for a form?
    2) Is there a way / code in which if the user clicks on the last level of a node, it will jump for example on the next level 2 node (clicks on Header 1 and it jumps to Guide 2)

    • TheSmileyCoder says:

      Hi Christian
      @ 1) I will admit that I have never tried to use a treeview that size, but obviously it cannot be larger than the form. But it doesn’t need to. The treeview has built in scrollbars, so the content of the treeview can be about as large as you want it to be. That said, if you start putting more than roughly a thousand records into the treeview it will start slowing down a bit. It’s not slow yet, but its going down from “instantaneous” to “slight delay”. Of course the problem expands if you continue to add nodes to it. That said, I’ve had treeviews with around 10.000 nodes in them, when needed.

      @ 2) Yes, I would say that anything you can reasonably request you can usually get done. In this case you would need to test for a couple of things. First whether or not the node clicked is a header. Then if that header has any children (You can use If Node.Child then) to test for that. You can then select the node using: Node.Child.EnsureVisible and Node.Child.Selected=True) to select the node, and even simulate a click of the node by passing in the node to whatever click event you usually use.

      Hope that helps, and best of luck with your project.

  16. Cristian Prifti says:

    Thank you Anders!
    I do not think it will be a too succesfull project, but let’s see.
    According to the first preview, I need to add one more step, the desired output is to have two treeviews, first one should contain the header with the first children level and then based on what child the user select to populate the second treeview with the remaining levels of children, which is a litle bit out of my league. 🙂

    • TheSmileyCoder says:

      Your welcome Christian.
      As for the project, just try to divide it into smaller manageable bits. Focus on the first treeview and its contents, and once that is working move on to the next thing. It consists of selecting/clicking a node. Finding out what content should be loaded into the 2nd treeview. Get that content into a recordset, and then load it into the 2nd treeview. Important thing is to make smaller manageable bits.

  17. Jeff says:

    Today I stumbled across your website and in particular your progress bar for copying files. Very nicely done. I would like to have that type of feature in a project I am working on however I am allowing users to select multiple files at once. Can your code be modified to work that way cycling through and showing the progress for each file?

  18. Mark says:

    Hi Anders, I’m trying to get Treeview Demo Part 2_Format2007 to work.The nodes and branches all open and close ok. The problem i have is that the form doesn’t update when different elements are selected. I have a reference to Common Controls 6 and I’ve tried unregestering then registering MSCOMCTL.OCX in my system32 folder but it still doesn’t work. I also noticed that treeReqs contains many events including Nodeclick. However, after trying to run the form I open the vba to see that instead of many events there are only 5 events. Any help would be greatly appreciated. Thanks.

  19. Richard says:

    Hi Anders,
    I have to thank you again that you helped me out last year with the development of my database with Treeview. For a new (large) customer I developed a completely new dbase for registrating the criticality on analyzed assets. It’s a site (location) with about 10 different plants in it and together there are about 70.000 assets to analyze!
    The most important tables I use are the asset tree, the failure modes, the failure causes and of coarse the company goals (risk matrix).
    When I “activate” all assets the loading of the Treeview last a very long time. Activating assets I do that by filling in the word “equipment” in de Treeview table (really simple, but this cost me some time). E.g. when I activate the assets of 2 plants (about 14.000 assets) it last 15 seconds to load the Treeview and of coarse every time you want to modify data it takes 15 seconds. To prevent that I work with queries, which goes very fast, but… is there a way to do it more efficient?

    • TheSmileyCoder says:

      Hi Richard
      Yes, the treeview is not terribly efficient. While I do have a single app that can load about 10.000 entries into the treeview, it does take quite a while (I would say 5-7 seconds). What I did in that case was to add an optional filter to restrict it to 1 root node, and since we had roughly 15 root nodes, the average number of entries per root node was less than a thousand. I would say that as long as you stay under a thousand nodes the load time of the treeview is less than 1 second and acceptable. (If the load is more than 1 second, with a 1000 nodes, its more likely to be the way you access the data, rather the treeview control, mind you)

      So what to do? If you can limit the amount of nodes that needs to be shown, similar to what I did, that is 1 way to approach it. That might however not always be feasible. In that case, I would do some sort of delayed loading. I.e. don’t load the child nodes until the parent node is expanded. This can be done simple, e.g. start by adding a “fake child” node to each parent (This ensures you get the + sign to let the user know there are more nodes.) Then in the nodeExpand event you can check if node.Child=”Fake Child” then LoadChildren. The downside here is if the parent node doesn’t have any children you still get the + sign, but again, even that can be remedied by checking the data before adding the fake child. Its really a matter of how much effort and time you want to put into it.

      Hope that helps, otherwise feel free to send me a mail, although I am quite swamped at the moment (Moment being the last couple of years *sigh*)

      • Richard says:

        Hi Anders,
        Thanks a lot for your quick reply in spite of your “home work”, which you showed me by mail! It’s a very good suggestion you gave and I will figure it out how to manage that in VBA.
        Greetings, Richard

  20. Peter Argast says:

    Hello Anders, First thanks so much for the great effort in your Treeview tutorials! I am working in Tutorial #2 and have a XML problem I think. I have not done much XML work and I do not see my problem in your blogs, hope you can help…

    You direct on providing a Key when we load the node object i.e. Set nodX = tv.Nodes.Add(nodPrimary, tvwChild, “” & 1 & “”, rstSecondary![Step Description]) – Here you see I’ve simply used an actual integer in my troubleshooting but this would revert to a recordset member when I get this figured out. When I run this I am getting: “Key is not Unique in Collection”

    Well, my primary keys are unique of course, but I suspect I have a XML problem with the “” etc references. I’m not sure if is your field attribute of an Access XML attribute. I use a PID / FID (Primary Identification Key / Foreign Identification Key) convention and have interchanged these name in my troubleshooting but still get the same results.

    Sorry I’m a XML newby, hope you can help.

    • Peter Argast says:

      Hi Again Anders, I got this figured out myself. I see now the XML mark ups you used served to force the Key value to be ascii based which is mandatory for the key value. It is VB6 comparability driven and can be any non numeric mark up but will always need to be decoded back to the primary key, there is no intrinsic XML role.

      My problem was due to the fact I have multiple relational tables in play and simply duplicated a primary key value in the treeview object node record, which is not allowed.

  21. John Murphy says:

    Hi Anders
    I’m using Access 2016 to build a simplified double-entry bookkeeping system for my son’s business and the main element of this is a Chart of Accounts, which lends itself perfectly to a Treeview type approach, where Accounts can be listed in their Type groupings eg Assets; Liabilities; Equity etc.. Clicking on an account’s node would then open the account record.
    I came across your brilliant set of tutorials and have gone through each one to get a grip (albeit a finger-tip one) on how it works so that I can apply it to my project.
    I think I’m more or less there, but I have been trying to create a ‘DeleteNode’ function similar to your ‘insertNew’, such that I can have an aditional ‘Delete Node’ button, below the ‘Above/Below/Inside’ buttons, but it has the problem that I’ve got to ensure I delete any child nodes of the selected node, prior to deleting the node itself, to ensure I don’t have Orphan records floating about.
    I’ve messed about with a ‘Do/While’ loop which recursively calls my Delete function to keep checking if there are any ‘Children of Child nodes’, but I’m struggling.
    I’ve search through various blogs to see if there’s anything similar anywhere, but I can’t find anything.
    I’d appreciate very much if you could steer me towards any recommended approach for doing this sort of thing..
    Kind regards
    John

  22. John Murphy says:

    Hi again Anders
    With the exception of error-trapping, I think I’ve cracked it!
    Thanks anyway for a brilliant set of tutorials
    John

  23. Federico says:

    Hi,
    Is there anyway to get a treeview with columns
    I found this on the web, but I think is based on visual and I need to be on Access
    http://www.codeproject.com/Articles/23746/TreeView-with-Columns

  24. TheSmileyCoder says:

    A listVIEW (not the listbox) which like the treeview is found in the mscomctl.ocx will have columns, but alas that is not the same as a treeview with columns.
    I’m sure there are thirdparty add-ins that has columns, but I have not personally tried any, and as such have none to recommend.

  25. Mohamed Adel says:

    Dear please i am tring to make accounting tree by excel vba ( Treeview control )
    and i want at each parent to add node and once i dblclick on the node userofrm should be open ( Linked userform by node )

    • TheSmileyCoder says:

      The techniques should be pretty similar to what I do in my 2nd video, navigation by treeview, except in excel I believe userforms are called with .Show or .Display (been a while).

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