[AccessD] Recursive data in treeview control

thanks Bryan. you're a star.

-----Original Message-----
From: Bryan Carbonnell [mailto:carbonnb at sympatico.ca] 
Sent: 12 February 2004 11:13
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Recursive data in treeview control


On 12 Feb 2004 at 10:40, Roz Clarke wrote:

> Does anyone have any code for recursively populating a treeview 
> control? I have a table like this:
> 
> TaskID		ParentID	Description
> 1		0		fleg
> 2		1		subfleg a
> 3		1		subfleg b
> 4		2		sub-subfleg a
> 
> and so on. I don't want to limit the number of levels.
> 
> I can see that I need to run through the dataset and populate the 
> treeview with the first level, then do it again n times until all the 
> children are added at the appropriate level, skipping any record 
> already added as a node.
> 
> I'm in a hurry - I have 2 hours to nail this (working hours not
> real-time) so although it feels lazy and I would love to crack this on 
> my own, if anyone has done it before I would appreciate any help you 
> can give.

Roz,

That is sort of the approach I too in my Treview Switchboard wizard.

Here is the code I pulled out of it. If I missed any functions, you 
can download the wizard from:
http://www3.sympatico.ca/carbonnb/bryan/Access/TreeViewSB.html

-=-=-=-=Private Sub fFillTreeView()
'---------------------------------------------------------------------
-----
'.Purpose      : To (re)Fill the treeview with top level nodes
'.Author       : Bryan Carbonnell
'.Date         : 29-Nov-2002
'.Called by    : Form_Open, tvw_NodeClick
'.Calls        : fFillChildren
'.Revised      : 29-Nov-2002 - Original
'---------------------------------------------------------------------
-----

Const cstrProcName  As String = "fFillTreeView"
Dim strSQL          As String
Dim rst             As DAO.Recordset
Dim nd              As Node

'Clear Treeview nodes
tvw.Nodes.Clear

'Build SQL to get Enabled Root Level Items
strSQL = "Select * FROM " & mstrTableName & _
          " WHERE SwitchboardID=1" & _
          " AND ItemNumber<>0" & _
          " AND Enabled=True" & _
          " ORDER BY ItemNumber"
'Open the Recordset
Set rst = CurrentDb().OpenRecordset(strSQL)
'Loop Through and build the nodes
Do While Not (rst.EOF)
  'Create the Node
  Set nd = tvw.Nodes.Add(, , , rst!ItemText)
  'Build Node Tag
  nd.Tag = "Command=" & rst!Command & ";Argument=" & rst!Argument
  'nd.Tag = fBuildTag(rst)
  'Check to see if we have an Open Switchboard Item
  If rst!Command = sbeOpenSwitchboard Then
    'This is an Open SB so we need to fill the children
    sFillChildren nd
  End If
  'Move to next record
  rst.MoveNext
Loop

'Close and release
rst.Close
Set rst = Nothing
End Sub

Private Sub sFillChildren(nd As Node)
'---------------------------------------------------------------------
-----
'.Purpose      : To Fill any children nodes
'.Author       : Bryan Carbonnell
'.Date         : 29-Nov-2002
'.Called by    : fFillTreeView, fFillChildren (recursive)
'.Calls        :
'.Inputs       : nd - Node - Parent node of these children
'.Revised      : 29-Nov-2002 - Original
'---------------------------------------------------------------------
-----

Const cstrProcName  As String = "sFillChildren"
Dim aryPairs()      As String
Dim arySplit()      As String
Dim strSQL          As String
Dim lngArgument     As Integer
Dim lngLoop         As Long
Dim rst             As DAO.Recordset
Dim ndNew           As Node

'Split tag into pairs
aryPairs = Split(nd.Tag, ";")
'Loop through split to get SwitchboardID that this node
' opens, which is the Argument
For lngLoop = LBound(aryPairs) To UBound(aryPairs)
  'Now split each
  arySplit = Split(aryPairs(lngLoop), "=")
  If arySplit(0) = "Argument" Then
    'Get the Argument Value, which is the SB to Open
    lngArgument = Val(arySplit(1))
    Exit For
  End If
Next

'Build SQL to select all the items in the Switchboard
' but not the 0 record, which is just info about the
' switchboard
strSQL = "SELECT * FROM " & mstrTableName & _
          " WHERE SwitchboardID=" & lngArgument & _
          " AND ItemNumber<>0" & _
          " AND Enabled=True"
Set rst = CurrentDb.OpenRecordset(strSQL)
'Loop through reordset to add new tags
Do While Not (rst.EOF)
  'Add New Node
  Set ndNew = tvw.Nodes.Add(nd, tvwChild, , rst!ItemText)
  'Build Node Tag
  ndNew.Tag = "Command=" & rst!Command & ";Argument=" & rst!Argument
  'Check and see if we just added an Open Switchboard
  If rst!Command = sbeOpenSwitchboard Then
    'We did, so add children
    sFillChildren ndNew
  End If
  'Move to next record
  rst.MoveNext
Loop

End Sub

This is probably one of the better commented chunck of code I have 
written :-))

--
Bryan Carbonnell - carbonnb at sympatico.ca
Blessed are they who can laugh at themselves, for they shall never 
cease to be amused.


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com