Combobox that simulates TreeView question

[mpant]

Hi everybody,
I've been building an access application where we have Entries and Categories among other things.
Each Entry can belong to a Category (Nullable FK Relationship) and Categories can belong to other Categories (multilevel hierarchy).

So, (..over-simplified..) table Entries is like:
ID
EntryTitle
CategoryID (nullable)

and table Categories is like:
ID
ParentID (nullable)
Category


Now, in detail forms bound to the Entries table I'm using a TreeView control to display the Category, the current Entry belongs to (..if any).

But I also need a Continuous Forms type of form to list all the Entries (like an advanced DataSheet). So, I made one and used a Combobox control bound to the CategoryID field to display each entry's category.

Until now everything works great.
The only problem is that the Combobox does not indicate the hierarchical structure of the Categories. It just displays them all in a list.
So, I had the idea to not bind the combo directly to the CategoryID field, but create a custom sql query (built dynamically with vba) for its Row Source property, which creates indentations and also adds the "└" character in front of the (sub) categories names to provide visual feedback of the hierarchical structure of the table Categories.

A sample of such an SQL query would be:

SELECT "1" AS ID, "Cat A" AS CategoryName, "Cat A" AS CategoryTreeName FROM Categories UNION
SELECT "2" AS ID, "Cat A1" AS CategoryName, "    └ Cat A1" AS CategoryTreeName FROM Categories UNION
SELECT "3" AS ID, "Cat A2" AS CategoryName, "    └ Cat A2" AS CategoryTreeName FROM Categories UNION
SELECT "4" AS ID, "Cat A2-1" AS CategoryName, "        └ Cat A2-1" AS CategoryTreeName FROM Categories;

As you can see we have 3 columns returned by this query.
The ID which is the actual Category.ID field (that we will bind to the Entry.CategoryID field),
The CategoryName which is the plain/unaltered CategoryName that we will display in the combobox after a selection has been made,
and finally we have the CategoryTreeName which is used to display the Categories in a hierarchical manner when the combo is dropped down.

We also setup the combo so that it has 3 columns, it's bound column being column 1, and for the column widths "0;0,1;"
The column widths are setup like this so the 1st (ID) column is never shown, the 2nd (CategoryName) column is shown in the combo only after a selection has been made (not when it is dropped down), and finally the 3rd (CategoryTreeName) column is shown only in the dropped down box (not in the actual combo).
We are doing this because we don't want to see for example " └ Category A" in the actual combo. It's better to show just "Category A".

And now the hard part...
What's the recursive procedure in vba that will create this SQL query dynamically for us??
(Also note that we want the combo/tree sorted)

The only working recursive procedure that comes to my mind as a starting point is the one I used to populate the actual TreeView control in the detail form. I grabbed this from MS site (http://support.microsoft.com/kb/209891):

'=================Load Event for the Form=======================
'Initiates the routine to fill the TreeView control
'============================================================

Private Sub Form_Load()
Const strTableQueryName = "Categories"
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset(strTableQueryName, dbOpenDynaset, dbReadOnly)
AddBranch rst:=rst, strPointerField:="ParentID", strIDField:="ID", strTextField:="Category"
End Sub

'================= AddBranch Sub Procedure ======================
'      Recursive Procedure to add branches to TreeView Control
'Requires:
'   ActiveX Control:  TreeView Control
'              Name:  xTree
'Parameters:
'               rst:  Self-referencing Recordset containing the data
'   strPointerField:  Name of field pointing to parent's primary key
'        strIDField:  Name of parent's primary key field
'      strTextField:  Name of field containing text to be displayed
'=============================================================
Sub AddBranch(rst As Recordset, strPointerField As String, _
              strIDField As String, strTextField As String, _
              Optional varReportToID As Variant)
   On Error GoTo errAddBranch
   Dim nodCurrent As Node, objTree As TreeView
   Dim strCriteria As String, strText As String, strKey As String
   Dim nodParent As Node, bk As String
   Set objTree = Me!xTree.Object
   If IsMissing(varReportToID) Then  ' Root Branch.
      strCriteria = strPointerField & " Is Null"
   Else  ' Search for records pointing to parent.
      strCriteria = BuildCriteria(strPointerField, _ 
           rst.Fields(strPointerField).Type, "=" & varReportToID)
      Set nodParent = objTree.Nodes("a" & varReportToID)
   End If

      ' Find the first emp to report to the boss node.
   rst.FindFirst strCriteria
   Do Until rst.NoMatch
         ' Create a string with LastName.
      strText = rst(strTextField)
      strKey = "a" & rst(strIDField)
      If Not IsMissing(varReportToID) Then  'add new node to the parent
         Set nodCurrent = objTree.Nodes.Add(nodParent, tvwChild, strKey, strText)
      Else    ' Add new node to the root.
         Set nodCurrent = objTree.Nodes.Add(, , strKey, strText)
      End If
         ' Save your place in the recordset so we can pass by ref for speed.
      bk = rst.Bookmark
         ' Add employees who report to this node.
      AddBranch rst, strPointerField, strIDField, strTextField, rst(strIDField)
      rst.Bookmark = bk     ' Return to last place and continue search.
      rst.FindNext strCriteria   ' Find next employee.
   Loop

exitAddBranch:
   Exit Sub

      '--------------------------Error Trapping --------------------------
errAddBranch:
   MsgBox "Can't add child:  " & Err.Description, vbCritical, "AddBranch Error:"
   Resume exitAddBranch
End Sub

I need a similar recursive procedure to create the sql query to feed in the combo's row source (..remember: sorted).

I think I'm loosing my mind inside the whole recursive way of thinking!..
Any help/info would be much appreciated.

Thanks in advance,
Maria

 

[jal]

You have a hierarichical table called Categories. You've already created a TreeView to display it, because you are well aware that it is difficult for other controls (such as a combobox) to display hierarchical data. Yet you are now asking for precisely such, that is, you now want to display the data in a combobox. Frankly I'm not entirely sure that makes a whole lot of sense. Personally, I would allow the user to make selections from the Treeview and thus forego the combobox altogether.

Secondly, I'm not sure that a SQL query is what you need to populate such a cbo. If we consider your sample SQL statement, it's not actually selecting anything from the table (it's simply displaying the values hardcoded into the SQL statement). Therefore you could probably use a more direct approach, that is, set the cbo's rowsource type to

cbo.RowsourceType = "Value List"

and then build a Value List, that is a set of string values to populate the cbo, by looping through the treeView. You would show the levels/indentations by prepending a number of tab chars (vbTab) to each string value (as necessary). For each row/string, you would store (in a hidden column in the cbo) the tableID so that you can pull that row from the actual table when the user makes a selection from the cbo. But I just don't see why go to all this trouble if you've already got a working Treeview.

 

[John Big Booty]

You could use a series of cascading combo boxes to simulate a tree view, or as jal has pointed out cut straight to the chase and use a tree view control.

 

[jal]

You could use a series of cascading combo boxes to simulate a tree view, or as jal has pointed out cut straight to the chase and use a tree view control.

Nice suggestion. Hadn't occurred to me to use cascading cbos.

 

[John Big Booty]

Perhaps the code present here by ChrisO could be bashed to simulate a tree view?

 

[mpant]

Hi and the thanks for your recommendations.

As I said I'm already using a TreeView control for the Details-View Form where there is plenty of space on the form to make it fit. But this is not a continuous-forms type of form. It's just a single record one.
Then I need to also create a continuous form bound to the same table which will have a height equal a line-height , just like a DataSheet does. I'm not using directly a DataSheet because I want some advanced functionality added with buttons etc.
A TreeView will not fit there because of its height. There is no drop-down TreeView control (in MSComCtl.ocx) so we are left with comboboxes.

The solution to use cascading comboboxes is not going to work because:
1) The form will get very cluttered with the countless combos for just the Category field and that defeats the purpose of having a continuous form for quick view and editing of records without having to open the full-blown Details view.
2) The cascading combos technique cannot be used with n-level hierarchies. The Categories table does not limit the depth of the hierarchy branches. We can have a Category at the root level and we can have a Category 20 levels deep (I know that this is silly usage but me as the developer can not start limiting functionality because of this. I should create a logical framework that may or may not be used in logical ways..).
So, how many combos do you think we should have on our form to accommodate for this scenario? 20???
(..and if the user drops a Category to level 21 or maybe 41???..)

As far as the nature of combos I have to agree with you.
These are not designed for hierarchical representation of data but more like a list.
And I'm not trying to change this concept.
All I want is to feed the combo with a list that has this hierarchical representation burnt into it like:

line 01: "ID:1", "Category A"
line 02: "ID:7", " └ Category A1"
line 03: "ID:4", " └ Category A2"
line 04: "ID:6", " └ Category A2a"

This is still a linear behavior.
The hierarchy only exists in the text being displayed.
And here is my first question. What's the vba recursive procedure to create this list from the Categories table?

And as far as the type of the combo row source type you are absolutely right.
I should change it to Value list and not query. I have forgotten that option as I have never used a combo with hard-coded values. I always get the data from a table.


Anyways,
Thanks again for the recommendations

 

[vbaInet]

Interesting concept but going 20+ levels deep I think is pushing it and probably not very good design. What are the possibilities that it could go that deep in your db? Or what is the actual number of levels it could go down to?

If you say there could be 20 or more levels, then have you thought about how wide the combobox may become?

What you're trying to do is probably achievable but how do you determine what level a particular Category ID should be at? Have you got a field in the Categories table that holds these level values?

Sorting can be done in the query once you have an answer to the (immediate) question above.

By the way, I don't think "└" is an Ascii character, it probably is just a symbol in which case cannot be included as text in your sql statement.

 

[jal]

mpant,

You say it's a question of space. I agree that a cbo is designed to minimize space - but let's consider how it does that. A cbo is about the size of a button. When the user clicks this "button" (so to speak), it drops down (i.e. expands) to a larger size. So we start with something small and end up with something large, right?
In concept this is no different than a button that pops up a form with a tree view on it - here too we start with something small and end up with something large. Therefore, I am not yet convinced that a cbo is better than a treeview here (but then, this is perhaps due to the fact that I have virtually no knowledge of Access forms).

 

[skokkinos]

Hello Maria,

What you are asking for can absolutely be done through vba.
And I also find it a great idea that gives an additional dimension of usability to comboboxes.

I have created a sample database (both .accdb and .mdb) that does absolutely what you want (..I hope ).

Inside you will find three different ways in three different forms to achieve the same result. The population of the combobox hierarchy.

1.
In form "frmTreeView" I am creating a regular MS TreeView control in code to take advantage of its nodes/hierarchy setup and functions.
I populate its nodes and then read it back and create the combobox rowsource.
Note that the actual TreeView control is not added in the form's controls collection and though not visible. It just acts as a helper object / intermediate step in code.
This solution of course is not the preferred one because of the memory overhead that the TreeView control introduces and also the dependency to the MsComCtlLib.ocx library.
I just placed it inside the db just as a sample of what can be done with vba.

2.
In form "frmClasses_SQL" I am using two simple custom created class objects (clsComboTree & clsComboTreeNode) to simulate the hierarchical behavior of the MS TreeView control (..excluding the visuals of course..).
Then I populate the combobox providing a dummy sql query as its row source.
This solution is still not the preferred one because it makes silly use of the query mechanism and also needs an extra column created ("OrderNum") to handle the sorting issues introduced by the querying engine that sorts the returned dummy records by ID ruining the already sorted hierarchy.

..and finally the best and preferred solution
3.
In form "frmClasses_ValueList" I am using the same two custom classes (clsComboTree & clsComboTreeNode) in the same way as in the previous sample but then I feed the combobox with a "Value List" row source type.
This solution is the best because it consumes the least possible memory, is pretty fast to calculate, does not involve access mechanisms that are not really needed (like the querying engine), has the least (and most readable) code and does not depend on any additional ActiveX libraries (like the actual TreeView does). Just a little bit of Object Oriented Programing to make things simple and off we go!..


Anyway, as I said the preferred way to go with this is solution number 3 but I also left the other two in case you want to scratch your head a little more.

I hope this helped,
Stamatis Kokkinos

 

[mpant]

Thanks skokkinos !!!!!!!
This is EXACTLY what I was looking for!

I knew that it could be done but could not figure out the way..
..and you gave me three! (..pretty embarrassing )

Thank you again!