TreeViews -- A Powerful Yet Simple Alternative

Treeviews – A Powerful  Alternative

I have written previously on using the TreeView within Access (Using TreeViews). There are a number of good reasons to use TreeViews – perhaps highest among them is that they are everywhere within the Windows UI, with the result that virtually everyone knows how to use them.

TreeViews are not especially easy to program. Further, they have a number of limitations. However, I have stumbled upon a powerful alternative that delivers an interface much more to my liking. Not only that: it’s very powerful and flexible, and delivers precisely the amount of control that I want. Best of all, it’s dead-simple to create – so simple, in fact, that I wonder why it took me so long to think of it.

The following example was developed in Access 2007 and will work in later versions. I have not tested it in previous versions.

Figure 1: Three-Level "TreeView"

This looks a lot like a formless browse of the parent table. Access does this automatically when you have defined relationships among your tables. However, it happens automatically only when any given parent table has exactly one child table. When two or more child tables exist, Access can’t determine which one to use, and therefore prompts you for the table to use. And unfortunately, once you've made that decision, you're stuck with it: every time you open the parent table, you get the same child you originally selected. 

Although Figure 1 looks a lot like a formless browse, it is actually a series of forms created in DataSheet style, then nested. If you wish, you can even create normal forms for each level, and open them on double-click anywhere within the row of interest. (In this app, I did not do that, because the target users are all very comfortable in Excel, and used to the "spreadsheet" format depicted above.)

By using DataSheet forms rather than simple table-browses, you get complete control  over columns, events, validations, lookups and so on that are available in a form. In this example, I chose to freeze the first two or three columns at each level, so the user never loses track of which row is active.

To create such a “treeview” form, follow these steps:

1. Decide the hierarchy of tables or queries you want to display.
2. Starting at the bottom of the tree, create a datasheet form for each level. This command is hidden a little bit on the Create ribbon. Figure 2 shows where it is.

Figure 2: Create a DataSheet form.

3. Starting one level above the bottom, add a subform. In this example, we create three DataSheet forms and save them. (Call them DS1, DS2 and DS3.) Then we add a subform to DS2 , selecting DS3 as the subform to add. Then we add a subform to DS1, selecting DS2 as the subform. Presto! Your hierarchical form is ready to go.

4. Customize each form to suit your requirements. Write code for events, add validations, combo-boxes. Do everything you would do to a standard form that displays only one record at a time. Decide whether it would be useful to freeze one or more of the columns.

That’s all there is to this simple trick. When I presented the app to the users, they were wowed. With their approval, I used this technique throughout the whole app, wherever hierarchies were involved.