recursive search and populate results in tree view control (1 Viewer)

zfshi · May 28, 2015

Hello VBA Gurus,

I need some VBA coding to do the following tasks

Table 1: two columns - Child Tag and Parent Tag. Parent Tags can also be in Child Tag column. In other words, a parent can have multiple levels of children.

Table 2: one column - Backup Tag.

I'd like to have a form with a combo box, pick a Parent Tag, the search all its child tags and compare each Child Tag found with records in Table 2 to see if there is a match. Then populate all results in a tree view control.

A visual example :

Parent Tag
...Child Tag 1 - Back up tag found
......Child Tag 11
......Child Tag 12 - Back up tag found
...Child Tag 2
......Child Tag 21
...Child Tag 3
......Child Tag 31
.........Child Tag 311 - Back up tag found

Thanks a bunch!

Richard

CJ_London · May 29, 2015

code like that is generally commercially valuable and I don't think you will find anything freely available. But take a look at this link from which you can work out your own code

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

or google 'binary tree algorithms' or similar. There are a lot of different ways of doing what you require and which is right for you depends on a number of factors such as size of recordset, number of levels, frequency of updates etc. - the example in the link (nested sets) is fast on retrieval but slow in inserting, deleting or updating so is suited to large datasets which don't change very often.

MarkK · May 29, 2015

This isn't actually that hard. Create a query that performs a left join between the two tables, so the field is null if there is no backup tag, but has data if there is data.

Then write a recursive node creation routine into which you pass the parent node . . .

Code:

Sub CreateNodes(np as mscomctllib.Node)
   dim nc as mscomctllib.Node
   with currentdb.openrecordset("SELECT * FROM YourLeftJoinQuery WHERE ParentID = " & np.Tag)
      do while not .eof
[COLOR="Green"]          'create this node[/COLOR]
          set nc = treeview.nodes.add(np, tvwChild, , !ChildName)
[COLOR="Green"]          'check for backup tag[/COLOR]
          if not isnull(!BackupTag) then nc.text = nc.text & " Backup Tag"
[COLOR="Green"]          'save the ID of the current record to the node[/COLOR]
          nc.Tag = !ChildID
[COLOR="Green"]          'recursively add child nodes of the current node[/COLOR]
          CreateNodes nc
          .MoveNext
      Loop
      .Close
   End WIth
End Sub

You might need a little special handling in the first call if you want a root node in your tree, but see if this code looks way too scary. If not, the principles for making it work are all there.

If you have a big data set, then you don't create the nodes recursively (it takes too long), rather, you handle the expand event, and populate child nodes on demand, which is a little trickier, but again, do-able.

vbaInet · May 29, 2015

I've done something like this for a poster using this character "¬" and it was all done in a query. I can't remember how many levels down it went.

Best to upload a test db with some sample data.

zfshi · May 30, 2015

Thanks everyone for your replies. I think I'll try search 1st level of child records and populate them in a tree view, then when clicking on a node, create an click event to search and populate next level of child records and so on. Do you have it's doable?

gemma-the-husky · May 30, 2015

there is an o'reilly free book available on the subject of advanced graph theory.

graphs as in the case of connected networks. I have downloaded, but not looked at it, but it may well have information on this sort of stuff.

worth getting hold of, for free.

vbaInet · May 30, 2015

A free O'Reilly book? Never thought they gave anything for free

There's still a probability that this can be done in a couple of queries, but I would need to see some test data. You will be able to use any character to create the tree.

vbaInet · May 31, 2015

Here's a link just posted by jdraw elsewhere re your original treeview approach:

http://www.access-programmers.co.uk/forums/showpost.php?p=1430808&postcount=3

recursive search and populate results in tree view control (1 Viewer)

zfshi

New member

CJ_London

Super Moderator

MarkK

bit cruncher

vbaInet

AWF VIP

zfshi

New member

gemma-the-husky

Super Moderator

vbaInet

AWF VIP

vbaInet

AWF VIP

Similar threads

Users who are viewing this thread