[AccessD] Treeview control

Many, many thanks for your suggestions everyone. Lots to try out & learn!

-----Original Message-----
From: Michael Maddison [mailto:michael.maddison at ddisolutions.com.au] 
Sent: 30 April 2003 05:09
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Treeview control


Hi Roz,

Further to the other suggestions may I suggest that you have a look at using
the MSDataShape provider to return a heirachical recordset. It allows you to
return the child recordsets at the same time.  This means that you no longer
have to loop through what can be a quite large rslevel2 recordset and can
speed up the process by a significant amount...

Quick example...

'Open a connection
    sConnect = "Data Source = " & sFileName & "; " & _
                "Provider = MSDataShape; " & _
                "Data Provider = Microsoft.Jet.OLEDB.4.0; "

   Set cnn = New ADODB.Connection
   cnn.CursorLocation = adUseClient
   cnn.ConnectionString = sConnect
   cnn.Open
   
   Set rsLevel1 = New ADODB.Recordset
   rsLevel1 .CursorLocation = adUseClient
   rsLevel1 .CursorType = adOpenStatic

strSQL = "SHAPE {SELECT Forest.Forest FROM Forest GROUP BY Forest.Forest
ORDER BY Forest.Forest}" _ & " APPEND ((SHAPE {SELECT Base.Compartment,
Forest.Forest  FROM (Base INNER JOIN Forest ON Base.ForestKey =
Forest.ForestKey) INNER JOIN Patch ON Base.BaseKey = Patch.BaseKey GROUP BY
Forest.Forest, Base.Compartment ORDER BY Base.Compartment})) AS Comp RELATE
Forest  to Forest)" 

'What is happening here is we have a parent query that returns all the
Forests.  We then append all the compartments in each forest 'joined on the
Forest key using the RELATE command. 'When we open the rsLevel1 rs for each
row if there are child records, the last field (in this instance called
Comp) will contain 'another recordset that will contain those child records.

   rsLevel1 .Open strSQL, cnn, , , adCmdText

  If not rsLevel1.eof Then
	Do while not rsLevel1.eof
	    'Add the parent node	    
	    Set nodX = Me.trv.Nodes.Add(rsLevel1.Fields("Forest").Value,
tvwChild, rsLevel1.Fields("Forest"), UCase(rsLevel1![Forest]), 49, 53)
		'Retrieve the child recordset
		Set rsLevel2 = rsLevel1.Fields("Comp").Value
		Do while not rsLevel2.eof
			Set nodX =
Me.trv.Nodes.Add(rsLevel2.Fields("Forest").Value, tvwChild, x &
rsLevel2.Fields("Compartment"), UCase(rsLevel2![Compartment]), 48, 52)
		rsLevel2.movenext
		Loop
	    rsLevel1.movenext
	loop
   End if


		
I hope you find this usefull.  I've used this technique to build a treeview
up to 5 levels deep and it works very well.

cheers

Michael M



I may be WAY off on this, but whenever I use a treeview, I assign my own
key, and text, with the .Add function.  It doesn't look like you are
assigning keys.

Drew
-----Original Message-----
From: Roz Clarke [mailto:roz.clarke at donnslaw.co.uk]
Sent: Tuesday, April 29, 2003 9:47 AM
To: 'accessd at databaseadvisors.com'
Subject: [AccessD] Treeview control


Hi all 
I've been playing with the treeview control (v6) for the first time today.
I've worked my way through an MS tutorial, adapting it to suit my purposes.
Can anyone help explain why the following code, which should give me 2
levels on the tree, only gives me the 1st level? None of the child nodes
appear. Private Sub Form_Open(Cancel As Integer) 
Dim db As Database 
Set db = CurrentDb 
Dim nd As Node 
Dim cnn As New ADODB.Connection 
Set cnn = CurrentProject.Connection 
Dim rsLevel1 As New ADODB.Recordset 
rsLevel1.Open "qryTopLevelTasks", cnn, adOpenKeyset, adLockOptimistic 
Dim rsLevel2 As New ADODB.Recordset 
rsLevel2.Open "qry2ndLevelTasks", cnn, adOpenKeyset, adLockOptimistic 
Dim intIndex As Integer 
rsLevel1.MoveFirst 
'add all the top level nodes (tasks with no child records) 
Do Until rsLevel1.EOF = True 
        Set nd = Treeview.Nodes.Add() 
        nd.Text = rsLevel1!tsk_Description 
        intIndex = nd.Index 
        'add all the 2nd level nodes matching this as parent task 
        Do Until rsLevel2.EOF = True 
            If rsLevel2!tsk_ParentTaskID = rsLevel1!tsk_TaskID Then 
                Set nd = Treeview.Nodes.Add(intIndex, tvwChild) 
                nd.Text = rsLevel2!tsk_Description 
            End If 
            rsLevel2.MoveNext 
        Loop      
        rsLevel1.MoveNext 
Loop 
End Sub 
Stepping through the code, the indices match between the parent and child
records and no error comes up. 
TIA 
Roz 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com