<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

CCTableTree - Display and edit hierarchy tables in Access continous forms without ActiveX

Published on
14,237 Points
3,537 Views
2 Endorsements
Last Modified:
Awarded
In the last few months I developed a little system to replace the Microsoft Common Controls TreeView control. This one uses only basic features of Access in a continous form so you can display a hierarchical table in a tree with nodes and subnodes, including the lines, like in an Explorer files and folder display. It is able to display additional columns like any normal continous form and is also updatable.

Feel free to use it anywhere you like as long as you don't sell it as your own work and you keep the remarks in the code. You're also free to change the code to fit your needs.

Have fun in experimenting with the new possibility of continous forms with trees.

Happy Christmas 2014,

Christian

CCTableTreeV2.zip

The documentation (also included in the attached zip file):

CC Table Tree



Table of Contents
1 About CCTableTree
2 Different Versions
2.1 DAO Internal
2.2 DAO External
2.3 ADO/ADP External
3 Table Tree Form
3.1 Details about the form
3.2 Events
3.3 Multi Tests
4 Configuration Editor
4.1 Default configuration
4.2 Configuration for all levels
4.3 Different configuration for each level
4.4 Editing the RTF fields
4.5 The configuration columns
4.5.1 Configuration Name
4.5.2 User ID
4.5.3 Assign Cfg. To Level
4.5.4 Handle Opened/Closed
4.5.5 Textfield Format
4.5.6 Vertical Line / Branch Line / Corner Formatted
4.5.7 Level Format
 


1 About CCTableTree

CCTableTree is a system of some modules, tables and forms which allows you to create a tree view of a table that is organized in a hierarchical way, where you have a numeric (Long Integer) ID as row primary key and another column (also Long Integer) that points to the parent ID.

Normally you would use the Microsoft Common Controls, the TreeView ActiveX object, to render such a hierarchy. Although that is a great control it has several disadvantages:
  • You have only one node text to display
  • You need to extract the data from the table and create a tree in the way the ActiveX component understands it (and that can take a lot of time if you’re new to this)
  • The MS Common Control library is not guaranteed to be installed on the target computer – or not in the right version
  • In 2012 a defect update from Microsoft of this library made all programs that used one of its controls unusable
  • It’s not sure how long these controls will exist in the future
As I needed to have a tree in a table form that works with standard Access continous forms and the need to avoid the MS Common Controls library in the current project I developed this system that is able to work in any kind of Access database, as single database file, as frontend/backend solution or in an ADP (Access Database Project in conjunction with a SQL Server, possibly up to Access 2010).

This system has the following features:
  • A row column from 1 to x for all nodes of a tree
  • A selection column where each node can be selected, including the possibility to select all subnodes or deselect the parent node automatically (in the way the old Microsoft installers displayed the feature selection)
  • A RTF formattable handle icon (i.e. a symbol character from a symbol font, but it doesn’t need to be a symbol) that shows the state of the current node if it has children and if they are expanded or not, AND that can be clicked to open or close the children nodes
  • A method to expand or collapse all children beginning with the current one
  • An RTF formattable level string that shows the level of the current node, additionally with the option to add an ASCII Unicode value to the level number to show special font characters like ① , ②, ③
  • The possibility to show or surpress the numeric level numbers with the “Show Level No.” option if you want to display a fix level text only (or even nothing)
  • Separated RTF formattable line characters to build a tree like this (also included in this example is another way of displaying levels with the ASCII Unicode value addition):
    -Ⓐ- ┣ Test Node Entry
    -Ⓑ- ┃ ┣ Test Node Entry 1
    -Ⓑ- ┃ ┗ Test Node Entry 2
    -Ⓒ- ┃ ┣ ┗ Test Node Entry 3
    -Ⓐ- ┗ Test Node Entry 4
  • An RTF formattable node text entry where you can also add fix prefixes and suffixes if you want
  • The possibility to JOIN any table to the tree table so you can display and change the values of the JOINed columns in the original table (must be an INNER JOIN where for each node there is only one record in the JOINed table)
  • An additional Custom ID field that is saved only in the tree table (not the original table) for your own purpose
  • The option to select a maximum level that should be loaded into the tree
  • Quick selection of a tree style using a combobox
  • One default setting (applied to all levels) and an unlimited number of additional settings, either as general setting or saved together with the User ID as individual setting of one user only
  • Either one configuration line for all levels or more than one if each level should be displayed in a different way
  • Automatic assignment of the “0”-level configuration line in a multiline-configuration if more levels exist in the tree than configuration levels exist
  • Events that are fired if the user changes a selection checkbox or if another node row is clicked the delivers the node ID to the event consumer, and events “BeforeNodeOpen/Close” with a Cancel parameter that makes it possible to forbid opening or closing a node and “OnNodeOpen/Close” when the node is opened or closed
  • The tree can be reloaded if the tree GUID is specified to the Init procedure so the tree can be displayed faster without complete rebuild
  • Saveable and loadable configurations in a configuration table
  • Trees are maintained separated by a Long Integer User ID and an additional Tree GUID so the same user can use several tree forms at the same time and also several users can use the same tree table (if you have a central tree table database file or a central frontend file) – but it’s also possible to give any user an own table tree for his local frontend
  • A comfortable configuration editor to enter all these things into a configuration table including an RTF preview field and a text editor for the RTF fields that allows one to change the tags manually (i.e. to enter hex color numbers that the Access built-in mini RTF editor doesn’t offer).
  • The tree form only exists to demonstrate the features; the tree classes are flexible because you can define the control names of the form to be used on your own and also the layout so you can display your tree in the way you want (the tree can also be displayed in the Datasheet View with full formatting, but without the possibility to open/close the subnodes by clicking on the handle)
  • The tree uses my “Reposition” class code so the tree is requeried without changing the record and screen position
  • The system is free to be used in any database project you want as long as you do not try to sell these as your own invention. You can also change the code to fit your needs as long as the informations about the author(s) kept unchanged.

2 Different Versions

There are three different versions to demonstrate the different kind of usage of the CCTableTree system.

2.1 DAO Internal

The DAO Internal version shows the usage of the system if you want to have all in one frontend file (or single database file). They contain the 1.0 class versions of the system that can only be used in this kind of database usage. Here the tables “tblCCTableTree”, the “tblCCTableTreeConfig”, the form “frmCCTableTreeConfig” and the classes “clsCCTableTree” and “clsCCTableTreeConfig” must be inserted into the database where you want to use them. The other objects from the demo database are the data tables to show the usage with two different tree tables, a test module that shows how to open the “frmDemoTableTree” with more than one user and/or more than one tree and the “frmDemoTableTree” that shows how to display a tree and how you can consume the events the class fires.

Moreover there is a little module “modVBA” that I used to create table and field constants and properties. You can use that in your own projects, if you like. But that has nothing to do with the CCTableTree system, you don’t need to import that.

2.2 DAO External

The DAO External version uses the 2.0 class versions of the system. The difference is that these classes search for an external database file named “CCTableTreeData.mdb” that contains the tables “tblCCTableTree”, “tblCCTableTreeConfig” and the form “frmCCTableTreeConfig” that is the configuration editor.

The demo form “frmDemoTableTree” and the module “modCCTableTree” remains in the normal frontend as do, of course, the two classes. The “modCCTableTree” contains replacements for DCount and DMax that are used in the SQL of the table tree classes.

This is needed because the domain aggregate functions (DCount/DMax and so on) are not part of the JET/ACE database engine but part of the Access application. If you open a database file without linking it to the current database the aggregate functions don’t work even if you use the right workspace object because they are called locally and search for the tables in the local workspace only. So these replacements (“XCount” and “XMax”) in the module are needed to perform a standard SQL method on the external database file.

Why is it necessary to use aggregate functions? This is because Access unfortunately is not able to use Sub-SELECTs inside an UPDATE query. If you use them, the SELECT is not updatable anymore. As a replacement you must use a VBA function or the domain functions to get the data you need, then the SELECT remains updatable.

When the tree is built, the tree table is filled in the external database file, so the frontend will not grow as it would in the “DAO Internal” version. The table tree is heavily used and so often loaded with data and deleted that without compacting the file will grow bigger. It is set to automatically compact on close but you need to open it by yourself and close it; simply opening a connection and closing it doesn’t compact the database file.

Another difference from the V1 class is that the V2 class has a conditional compiling constant at the top of the class code. You can set that to “ADO” or “DAO”. In the “DAO External” version it is set to “DAO” to compile anything with a DAO frontend. See below in “ADO/ADP External” when you need to switch that to “ADO” instead.

2.3 ADO/ADP External

The ADP file in this folder has no server connection. To use it you must connect that to any SQL Server that contains the usual “AdventureWorks” database which contains the “HumanResources.Employee” table used in this demo. The other little tree table that is in the frontend in the other versions can be created and filled with data if you use the “CreateTblTree1.sql” script in this folder.

The V2 class also available in this class has the conditional compiler constant “ADOorDAO” set to “ADO” so this can be used with an ADP. That is used to fill the tree table using ADO; nevertheless, the external tree table file is used as a DAO database in the same way as in “DAO Internal” above. You often hear that an ADP file cannot use local tables. That is of course true for the ADP file itself but this sample shows also that you can of course use local tables if you use a second file. It also shows that you can JOIN between DAO and the SQL Server database tables which is also updatable – using an ADP to show the DAO recordset. That’s possible because Access forms can use both kind of recordsets either in ACCDB/MDB and also in ADPs.

Except that the class now works in an ADP and that the conditional compiler constant is “ADO” here, there is no difference between DAO External and ADO External; they work the same way.

3 Table Tree Form


3.1 Details about the form

The demo table tree form should only show how to use the CCTableTree system. You do not need to import that to your own database if you don’t want; you can completely build your own form because the class has an Init procedure that you can provide with any reference and names needed to use the class with other forms, too.

To use the click function on the handle it is necessary to design a transparent button over the handle textbox. In this way it is possible to click on the handle without marking the complete textbox and without a cursor appearing in the textbox. It needs to be a textbox because only that control is able to show an RTF formatted content; the same is true for the TreeNodeText and Level fields. Otherwise you would see the HTML tags in these fields.

You can of course also use a normal designed button beneath the handle and simply set the handle textbox to locked and deactivated, but that doesn’t look so impressive as directly clicking on the handle. You need that method if you want to use a version older than A2007 because transparent buttons were introduced in A2007.

The controls in the header shows the other methods possible with the class, like limiting the maximum number of displayed levels, selecting the style and switching the tree to another one. The expand and collapse buttons start at the current selected node in the tree.

3.2 Events

The form shows how to use the events the class fires. Don’t wonder that you cannot open the branch "adventure-works\stephen0" of the Human Resources tree. The “Cancel” parameter can be used to block opening the current branch, in the same way the “BeforeNodeClose” event with its “Cancel” parameter shows that you can also open a node but not close it again. That’s the case with “adventure-works\amy0" node.

3.3 Multi Tests

The “modTest” module demonstrates how to use the same form to open either two trees for the same user or the same tree by two users using the same external or internal tree table. Before starting any if these procedures you should comment the SelectTree call in Form Load of the demo form; otherwise this would create the tree once more for each form.


4 Configuration Editor


4.1 Default configuration

The default configuration “_Default” must not be deleted from the table; this is used if no other configuration is assigned to the current tree. Also the “UserID 0” and “Assign to Level 0” must not be changed. The other fields are open to be changed, so you can configure your own default configuration if you want.

4.2 Configuration for all levels

If you want to create a configuration which will be assigned to all levels of the tree then set the “Assign Cfg. to level” value to 0. That’s all for this variant.

4.3 Different configuration for each level

This variant is useful if you want to design a tree where every level is displayed in a different way. You are free to design each level completely independent of the level before. To achieve that, each level number must be entered in the “Assign Cfg. to level” column, so 1 for level 1, 2 for level 2 and so on, one row in the config table for each level. Create an additional “0” row for any further level, e.g. if your tree has 6 levels but your config only goes up to level 3.

The configuration is grouped by the class using the identical configuration name and user ID, where “0” is for general configurations and any other for a specific user ID from your user maintenance.

4.4 Editing the RTF fields

All RTF fields use a kind of “light version” of HTML. Unfortunately only some few tags of HTML are usable in Access RTF fields. You can directly edit the RTF fields in the list using the Access mini RTF editor that appears when you edit a field. The disadvantage is that you have no control over what it saves into the field. Moreover the number of colors is limited to the number of colors displayed in the mini editor.

For this reason there is a text version of the field displayed in the footer of the form whenever you enter an RTF field. You can also edit the contents here; it will change in the field above at the same time.

You’ll see that the field is always surrounded by “div” tags that Access automatically inserts. Don’t worry, these are automatically stripped off the field when the tree is shown (otherwise the div tags would create a new paragraph in the output).

The advantage of entering the field as text is that you have full control over the contents, you can remove unneeded texts and you can enter any color you want using the hexadecimal version like “#FFFFFF” for white. Don’t use color names like “Red” or anything like this although the RTF field is able to display that. The tree classes need this hexadecimal style to replace vertical lines with the background color (that’s the trick to display the tree lines with spaces between some nodes, you see that if you mark such a node). In other RTF fields except the “Vertical Line Formatted” field it doesn’t matter if you use hex colors or named colors.

Moreover there is a little preview field showing the result of your configuration for one level (for multiple levels you must use the demo form to see the result).

4.5 The configuration columns


4.5.1 Configuration Name

Any configuration need to have a unique name in the configuration table. If you have a multi-level config (one config for each level) you must repeat the same name in each config line with a different “Assign Cfg. To Level” value. The config name is unique inside of one user ID, so different users can create the same configuration name for them.

4.5.2 User ID

The user ID is a long integer that should come from your user maintenance (a login table etc.). This can be used to separate lists of configurations for different users. If the user ID is 0 then the configuration can be used by all users. There is a “FillConfigCombobox” method in the “clsCCTableTree” which allows you to automatically fill a combobox either with the general or with the config of a specified user ID.

4.5.3 Assign Cfg. To Level

Use the value 0 for a default configuration in a multi-level config or always in a single-level config. Use any higher number for the desired level. This makes it possible to have different ways of displaying a tree, i.e. you can have different colors or font sizes for each levels, but also it is possible to have a config for only one specific level and all others using the 0 config.

To group the configurations together the same config name must be entered with the same user ID and different level numbers.

4.5.4 Handle Opened/Closed

These two fields are used to display a different handle for all nodes that have children nodes. Depending of the state of such nodes either the “Opened” or “Closed” handle string is displayed. Feel free to experiment with any font or style you want. It can also be one or more characters (the demo form would display only one because it is so small but you can make the handle field larger and display longer handle strings).

4.5.5 Textfield Format

The textfield format must contain the string “{#}” which will be replaced by the node text later. The normal way would be to only insert this string here and format it in any style you want, but you can of course add a fix prefix or suffix before and after this string that would be displayed on any node.

4.5.6 Vertical Line / Branch Line / Corner Formatted

These three fields are used to build the tree lines. Normally these consist of one character only, but it is not limited. In case of the vertical line you need to make sure that the text is surrounded by only one font tag that must contain a color in hexadecimal format like “#FFFFFF” because the class searches for this color whenever the line should be hidden and replaces it with the textbox background color (which cannot be transparent for correct display).

You should make sure that also the other line fields don’t contain more than one “color” attribute.

4.5.7Level Format

The level field must also contain the text “{#}” which will be replaced by the level number. It can, like the textfield format, also contain a fix prefix or suffix text, also with different colors and so on.

The field will display the level number if the field “Show Level No.” is checked. If not, the “{#}” is replaced by an empty string. That can be used to display a fix text instead of a level number like shown in the “Human Resources” style.

Additionally there is a field “Add ASCII To Level” which can be used to add a unicode ASCII value to the level number. So “9311” as in the demo would be result in “9312” for level 1, “9313” for level 2 and so on. That parameter is useful to use special characters in a font like 9312 that is a circled 1, where the other characters following are a circled 2, 3 and so on. You can also use this if you design a unique font with special characters to be displayed for each level number. The only thing that is needed is that they must be designed beneath each other (like with 9312, 9313, 9314 and so on).

The circled numbers in the used font here are limited up to level 9, but in the “User 3” style you can see that also circled A up to circled Z can be used to display levels which gives a greater range of possible levels.
2
Comment
Author:Bitsqueezer
  • 5
  • 3
  • 2
10 Comments
LVL 44

Expert Comment

by:PatHartman
Hi Christian,
I tried to download the sample but I get errors when extracting the files from the zip.  Each file raises the same error.
Error message
0
LVL 25

Author Comment

by:Bitsqueezer
Hi Pat,

you're the second one who has problems extracting the file, strange, because it was of course tested by the EE team before publishing it. Maybe it's because you're using the Windows extract method, I always use the freeware 7-Zip.

If there's really an error in the upload file, you can find a copy of the file on my download page here:
Bitsqueezer's Access Downloads
(which also contains a bunch of other of my projects)

Hope that helps,

Christian
0
LVL 44

Expert Comment

by:PatHartman
Thanks,
I could unzip that one.  It doesn't seem to be the same contents though.  The download from here created three subdirectories and included documentation.  Can you post the documentation file separately.

PS - many companies don't allow software installation so the windows zip app is all we get.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

LVL 25

Author Comment

by:Bitsqueezer
Hi Pat,

as far is I could see from the download counter you downloaded another file ("AccessTreeView") which also is a way to create a kind of treeview with Access methods (but only up to 7 levels), the file I meant has the same filename as here and contains the document file and the three subfolders.

Click on the "filename" header to sort the list by this column, then you can find the "CCTableTreeV2" file easily.

Cheers,

Christian
0
LVL 44

Expert Comment

by:PatHartman
That one worked!!!
I have trouble downloading files from EE.  Up to now, it has only been with .accdb files.  One of the moderators looked into it and determined that it is an incompatibility between EE and IE.  I doubt that MS will change IE so we know where that leaves EE.
Thanks again.  I'll take a look at it.
0
LVL 25

Author Comment

by:Bitsqueezer
Glad to hear that it worked now. If you didn't hear about that, MS is currently in the process to create a new IE, completely different to the current one.
I use Firefox and never had download problems here, but of course if you are not allowed to install anything...
But why not download it on your private computer instead where you can install anything?

Thanks for downloading it, hope you find it useful.

Christian
0

Expert Comment

by:Paul Sondervan
Hi Christian,
I downloaded your CC Table Tree.
Works and looks very nice.
I started working with DAOInternal (CCTableTreeV2_0_DAOInternal.accdb) version in MS Access 2013.
There is a problem I noticed.
- Open CCTableTreeV2_0_DAOInternal.accdb
- Click arrow row 1
- Click arrow row 2
- Click arrow row 3
- Click arrow row 3 to close it, it doesn't close
- Click arrow row 2 to close it and again to open it
- Click arrow row 7 to open it, it won't open
Can you solve this problem?

I would love to use CC Table Tree for my project in which I have to show various levels of data.
The sometimes not closing/opening of levels is keeping me from using it.
So I really hope you can solve this issue.

Regards,
Paul
0
LVL 25

Author Comment

by:Bitsqueezer
Hi Paul,

without looking into the database it sounds to me as if you talk about the two rows which have exactly this behaviour by design. If you look into the code of this form you'll find some event subs which have a "Cancel" property, these event subs show what happens if you set the Cancel value to True (like with Cancel in i.e. "BeforeUpdate") - you can use that to block closing a node or block opening it. The code shows you that it tests special IDs of this tree and if they are used in the event they get blocked (it's commented in the code).

If that's not the issue please write again.

Cheers,

Christian
0

Expert Comment

by:Paul Sondervan
Oops, you are right Christian.
Sorry for the misunderstanding.
It works super now.
:thumbs up:
0
LVL 25

Author Comment

by:Bitsqueezer
No problem, glad if I could help you and it's useful for you.
0

Featured Post

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Join & Write a Comment

In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month