Solved msaccess: populate treeview (no activex) with resultset

[linux_s2002]

Hi,
I have a resultset like this:

|stars|idR|idS|idU|
|---|--- | ---|---|
|5 | | | |
|5 | 1 | 3 | 0 |
|5 | 1 | 4 | 0 |
|5 | 2 | 27 | 1 |
|5 | 2 | 30 | 0 |
|5 | 3 | 33 | 0 |
|5 | 3 | 33 | 1 |
|5 | 3 | 33 | 2 |
|5 | 3 | 33 | 3 |

How can I populate treeview with this fields?

The result i want is like this:
+5
+1
+3
+4
+2
+27
+1
+30
+3
+33
+1
+2
+3
I try with this code:
Set rstBlq = dbs.OpenRecordset(S1)
If Not rstBlq.EOF And Not rstBlq.BOF Then
Do While Not rstBlq.EOF
i = i + 1
If rstBlq.Fields(1) & "" = "" Or rstBlq.Fields(1) = 0 Then
Else
sKy = DLookup("desc", "R", "IDR=" & rstBlq.Fields(1))
Set cNode = cRoot.AddChild(sKey:=rstBlq.Fields(1) & " 5\_" & i + 1,vCaption:=(sKy))
If rstBlq.Fields(2) & "" = "" Or rstBlq.Fields(2) = 0 Then
Else
sKy2 = DLookup("desc", "S", "IDS=" & rstBlq.Fields(2) & " and idR=" & rstBlq.Fields(1))
Set cNode2 = cNode.AddChild(sKey:=rstBlq.Fields(2) & " 5\_" & i + 1000,vCaption:=(sKy2))
If rstBlq.Fields(3) & "" = "" Or rstBlq.Fields(3) = 0 Then
Else
sKy3 = DLookup("desc", "U", "IDU=" & rstBlq.Fields(3) & " and idS= " & rstBlq.Fields(2))
Set cNode3 = cNode2.AddChild(sKey:=rstBlq.Fields(3) & " 5\_" & i + 10000,vCaption:=(sKy3))
End If
End If
cNode.Expanded = False
End If
rstBlq.MoveNext
Loop
Set cNode3 = Nothing
Set cNode2 = Nothing
Set cNode = Nothing
End If

but don't work

thanks!!

 

[MajP]

When you say no ActiveX are you using the JKP treeview control?

Dozens of Free Excel Downloads For You

jkp-ads.com: Free Downloads Excel files, Excel add-ins

jkp-ads.com

Also if you want code to load any tree view with just a single line of code see my class. If you want to know anything about tree views see thread.

Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

I have had some people ask questions on working with Hierarchical data and Tree Views, which often involves using recursion. If not familiar with recursion this is where a procedure repeatedly calls itself until hitting a condition where it is told to stop. This is used often where you have to...

www.access-programmers.co.uk

 

[MajP]

That data structure is not very conducive to storing hierarchical data. Read my thread.
Put the data into some kind of sensible structure.

SELECT DISTINCT "stars" & [stars] AS NodeID,
                table1.stars      AS TheValue,
                NULL              AS ParentID
FROM   table1
UNION
SELECT DISTINCT "idr" & [idr]     AS NodeID,
                table1.idr        AS TheValue,
                "stars" & [stars] AS ParentID
FROM   table1
UNION
SELECT DISTINCT "ids" & [ids] AS NodeID,
                table1.ids    AS TheValue,
                "idr" & [idr] AS ParentID
FROM   table1
UNION
SELECT DISTINCT "idu" & [demoid] AS NodeID,
                table1.idu       AS TheValue,
                "ids" & [ids]    AS ParentID
FROM   table1
ORDER  BY 1,
          2;

Code
If the structure is correct the code is trivial

Public Sub LoadTree()
  Dim rstBlq As DAO.Recordset
  Dim strSql As String
  Set rstBlq = CurrentDb.OpenRecordset("S1")
 
  'Add all nodes to tree then move them
  strSql = "Select * from qryNode"
  Set rstBlq = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
 
 
  Do While Not rstBlq.EOF
     tvw.Nodes.Add , , "N" & rstBlq!NodeID, rstBlq!thevalue & ": " & rstBlq!NodeID & " Get Text "
     rstBlq.MoveNext
  Loop
  rstBlq.MoveFirst
  'second pass to move under their partens
  rstBlq.MoveFirst
 
  Do While Not rstBlq.EOF
     If Not IsNull(rstBlq!ParentID) Then
       Set tvw.Nodes.Item("N" & rstBlq!NodeID).Parent = tvw.Nodes.Item("N" & rstBlq!ParentID)
     End If
     rstBlq.MoveNext
  Loop
End Sub

 

[arnelgp]

using temp table.

 

[linux_s2002]

Thank you!!!! It's perfect for me........

 

[linux_s2002]

Sorry, but with this resultset don't work very well..
can you help me?
resultset resultset

stars	idR	idS	idU
2​	1​	2​	0​
2​	1​	3​	0​
2​	1​	4​	0​
2​	1​	10​	0​
2​	1​	11​	0​
2​	2​	19​	0​
2​	2​	20​	0​
2​	2​	26​	0​
2​	2​	27​	0​
2​	3​	33​	0​
2​	3​	33​	2​
2​	3​	34​	0​
2​	3​	35​	0​
2​	4​	36​	0​
2​	4​	37​	0​
2​	4​	39​	0​
2​	4​	250​	0​
2​	13​	96​	0​
2​	13​	97​	0​
2​	13​	98​	0​
2​	13​	246​	0​
2​	26​	212​	0​
2​	1000​	0​	0​

thank you!!

 

[MajP]

I do not know what your problem is, it continues to work for me. Did you follow what I said to do? Also this is a stupid way to save Hierarchical data. You really should reconsider your data structure. This does not make any sense and is overly complicated. Again see my thread in my first post.

 

[MajP]

FYI, I can not explain the ? mark but I think it cam from importing the data.

 

[linux_s2002]

your example is perfect. maybe my bug is caused by absence of counter (demoID) in table?

 

[MajP]

Yes you need something to create a unique ID for each every node needs a key and that is how you add a child to it.

 

[linux_s2002]

Thanks!!!!! now it works!!