ActiveX was manifested by Microsoft as drop-in and interactive controls, though ActiveX didn't have to be used in that way, in Visual programming languages and so for that Visual BASIC used in Microsoft Office products. One could design a form in some Office product, drop in a text box, or combo box, or - ActiveX control - and use a VBA (acrostic for Visual BASIC for Applications) 'macro' backend to control it. Controls with similar user features were also placed in the updated .NET framework (which is to say that beyond the general purpose, some of the commands would be similar or remain the same).

A few ActiveX controls seem essential for development of sites such as this, when Office products are brought into the process. The 'incremental loading' bar is useful. The spinner control might be useful on a form; click up or down button to increment/decrement a value, or slide out for rapid change. The datagrid control is seen as useful, and now particularly as a page control using .NET, and which 'grid' is just a table of labels and values. But two others are really essential. The first handles the hierarchies of the website itself, including that to generate/compile pages, local linking, external, and all the content of each page. It's the treeview control, gone though subtle incarnations (as all Microsoft libraries are incrementally changed with new versions). It's essentially the collapsible 'directory'/hierarchy view one gets in the Windows File Explorer, when one looks through files and directories. The other is one that ships with the Internet Explorer browers. And that's an editable HTML browser page (not quite FrontPage, but pretty complete HTML editing and output, nonetheless).

Of these ActiveX controls, and in the context here, the treeview control is the most essential. So it's a good place to begin. One can imagine various data, easily and intuitively, as a sort of table of contents - a hierarchy. Such would be the the organizational levels of a website itself. Such would be the order in which information is presented even on a page like this, or page by page in a book. There are a host of web pages now describing the intricacies of this treeview control. Run a google today, and you'll find nothing but treeview tutorials, going back to the late 90s versions, and up to the latest .NET incarnation. But this following will concern the ActiveX treeview controlled by the 'classic VB', the 5/6 versions of Visual BASIC, and specifically VBA found as the programming/macro language in Microsoft Office products.

In a program like Access, in something like Access 2000 (which like Access97, I'm sure is still used by many), one can create a new form. And in addition to dropping in a textbox and whatever else, one may also drop-in a treeview control. A few things. First, you need permission to drop/insert a new treeview. ActiveX controls required permissions (I suppose the idea was that third parties would produce a host of such controls, and then license and sell them). I believe Office Pro of that era came with permission, at least for this control. I'm not sure. If you try to insert one of these treeviews and get some 'permission denied' message, you might try to download another sample database from the internet which does use the control, open the database, and just copy the control and paste to your new form. It might work. Second, the source library should already be established. But just to make sure, again in Access as the example, one can open a module for programming and check, Tools, References, from the menu bar. There should be a check by an entry for - Microsoft Windows Common Controls 6.0 (SP6) - or similar, pointing to the file - C:\Windows\System\MSCOMCTL.OCX . That's generally the means of getting it, even if the actual 'com' control resides in another library altogether, with this ocx containing merely a cover/wrapper. And so back at the form's design, looking from, Insert, ActiveX Control, there should appear as one of the controls - Microsoft Treeview Control 6.0 . Click on that, and the box will appear on the form. Its class should read - MSComctlLib.TreeCtrl.2 . Ready to begin.

I don't know if there is a standard prefix for referring to a treeview control on a form. I use the letters - tvw. Since it's the only treeview on this example form, we'll just call it - tvw. So we save, name and close the form. But to open it as just a barebones treeview, some things need to be initialized. Here's just one example of such:

In this example, the data for the treeview is kept in a table called, tblSelfRefData. As noted above, this goes to the idea that while it isn't necessary, one may keep such hierarchical data in a self-referential table (though some might discourage the practice). It assumes, minimally, that there is a unique number for each field, an upward link from any item in any immediate sublist to that number, and also a field with a label - i.e. anID, lngSupID, and txCategory, respectively.

It's using the older DAO type, but that's also not essential. Once the treeview is set as an object, objtree, certain general properties can also be set, before it is first displayed. Here the text in the treeview will be Arial, 8, with a weight of 600, as you can see. Background, line types, and much else can also be specified for the entire treeview.

It's that subroutine, sFillTvw, that does all the work. Given the table/dataset to use, given an initial Where clause or criteria, it fills in the entire treeview, line by line, from the table.

In this case there is also a flag, True, to indicate that the entire tree won't be filled out, initially. It will be filled out on an as-needed basis. Instead, only two or three levels down will be filled in, whatever level you set. Then whenever you click on an item/node in the treeview, a routine is called to fill out a little more. Generally, it might be best to fill out smaller trees all at once, when first loaded. But if the meat of the database, the main tables, even start to push 8000-10000 entries (and even for smaller non-commercial databases that might not take as long as you might think), then even a fast pentium-4 can make you wait 10-15 seconds or more for the entire treeview to be filled in. That can be annoying. The idea of filling out a few levels at a time, instead of just one, and then one down, and then one down, etc., is that this ActiveX, VB6 type treeview does not allow one to set the expand/contract (plus/minus) button, directly. In order to show that the item is a folder, or contains a sublist, there have to actually be items under it. And this solves that, without forcing the complete database to be placed into the treeview, at once. [Now, it's true that one can use SendMessage from the Windows API, and an elaborate series of cover/wrapper functions, to conceivably switch on the plus/minus box even if there are no child nodes in the treeview. But it might pose problems of its own, face unexpected delays, and this method here is just as easy, frankly, particularly as it ties in with the fill-once scheme that might typically be used for treeview (on the assumption that most of the treeviews one uses will represent pretty light hierarchies).]

The treeview must be 'populated'. There are two routines at any rate. One fills the top level, and then passes control to a recursive routine that fills in subsequent levels. If there is a limit on levels, the recursive routine will stop at that point. If not, it fills out the entire treeview from the table specified, based on a changing criteria. And that criteria, in this case, for an 'adjacency list', or self-referential table, is simply assuming that the current record in the table has a sublist associated with it. If not, the record is just placed as a node in the treeview, whatever field is used for the name. If it does have a sublist, that is recursively read, and any under it, and so on. That's literally how the control is filled in - programatically - not simply from a SQL view or any 'flat' table. It's filled in by loop or recursion (recursion just seems easier in this case), under program control.

So the top level:

As an example, even this might be improved by using a SQL view/SELECT rather than passing a reference to the entire DAO recordset. Unlike treeview, which starts to take a while to fill at about 8-10K entries, surely it would take far more records to slow things down in this way. But when the recordset gets too big, this could become a bottleneck. Whichever one prefers, then.

You can see at the top that the treeview is emptied, cleared out. The treeview, overall, has a property - nodes. Technically - it's a collection; the Nodes collection of a treeview. One method one can use with nodes is, clear. This is a complete refresh or filling of the treeview, from scratch. What had been in the treeview - is gone (of course, that's just in the treeview, not the underlying table or data). Then the entire recordset is read, in order, by the next record with the same superior node as that which was specified on recursive entry to this routine. Again, see the notes on hierarchies. Nodes are added, using the add method of the tree's general nodes property, and here with text from the recordset's, txCategory field. And you can see that each new node is sorted. This refers to any potential sublist. If one is added, the items/nodes will be alphabetically sorted by their text property - again, the text read from txCategory in this example. The add method shows two parameters at the start which are not even used, only the commas are used to hold their places, here. The first parameter used is the "a". This is the node's key property. Each node, in turn, has certain properties; the text property just mentioned, and a key which must be unique in the entire treeview. In addition, this key is expected to started with a letter, not a number. So here "a" is prefixed to the unique number for the underlying record in the table. The assumption is that the treeview is only being filled from this one table (as this is all done programmatically, you can see it doesn't necessarily have to be so). That also means when someone clicks on this node, it's easy to find the underlying record. It's simple enough to add a letter like, "a", and then strip it off when it comes time to read the number. Even different letters, one or more, could be used, if you allowed for such in your programming, perhaps helping to identify different types of nodes. There is an additional property, however, for each node - tag. And you can put whatever you want in the tag, including such info about node type, etc.

So the treeview itself has this add method, and the clear method, seen above. And each node has at least the properties - .text, .key, .tag, .index, and .sorted, and whatever else shown in the chart. And there are a few more. These Nodes can also contain images, as well as text. That's not covered here. Now, having seen the example, just forget about the 'sorted' line. It's not needed. You'll see why in the recursive routine, below. Since Sorted must be set for any new addition, it's even just generally recommended to set the superior node's Sorted property as the last thing after all else has been completed:

And so all the sublists have to be filled-in. As noted above, here an option is presented to call a recursive routine that stops after so many levels down into the hierarchy, or else one that simply keeps at it until all the data is placed in the treeview as the form is first loaded. The simpler, sAddBranches, just keeps going. So it would be quite similar to that above, and looks like:

Again, it's basically just the top routine, but it calls itself, recursively. You can see the .Sorted has been moved down here. Because once a sublist is filled out, once that's completed, then the superior node is set to sorted. That alphabetically sorts the sublist by the text property of each node. And the other obvious difference is that the first two parameters of the add method have to be used. It's a child node. So the tvwChild constant is used (this doesn't have to be declared, but comes with establishing a reference to the control - see above). And the node to which this list/sublist is attached is also used, here called - nodSup. That's it.

A different scheme was noted, above, allowing that only a certain level would be reached by this recursion. Only part of the treeview would be filled in. And each click on a node would call up another routine, at that time, to attempt to fill out a few levels in, and so on each time:

And so even this is not all that different from the previous, which was not so different from the previous to that. Apart from having to call the limited recursion routine from the treeview's form on nodeclick and expand, here the difference is clearly that a flag is set if a sublist already exists for that node. Maybe there is no sublist for that node. Maybe it's not in the data. In that case, it proceeds normally, and makes a quick exit back out. And of course, in that case, if there is a sublist, but it hasn't been filled in yet, then it will be. But if there is one already existing, then instead of adding a new node, the correct node is only read/selected and then passed back in recursively. You can also clearly see that it is very useful that nodes is able to find a particular node by its - key. And at some point, the routine will start filling in new sublists, before hitting the limit, here previously set to 2 or 3 (see above). You can see how the recursion is used to increment the counter to compare with the limit - intLimit (which itself is simply passed in unaltered every time). So - that's that.

Interesting, or boring, as it may be, all this did was fill in the treeview. It's nice that you can see it all organized. But now what? How do you relocate sublists? which is the real feature of using treeview and such hierarchies, as opposed to the relational model which fixes hierarchies both by key assignments in the tables, and by the tables themselves. How do you then update the underlying table, using a treeview? And so on.

Continue