Using Treeview to display 3 linked tables from access db

[Oliver2003]

I am not familiar with the treeview control and was wondering if it is possible to display linked data from tables using a root item for the data in the first table, then a child for the data related to this root from the second table and then another child to display data from the third table that is related to table two.

Don’t know if Im describing it that clear, Something like this:

-Root(table1)
+Child(table2)
+Child(table2)
-Child(table2)
--Item(table3)
--Item(table3)
+Root(table1)
+Root(table1)


The tables are linked by a autoID number, the relationship is like this:

Table1-------Table2--------Table3

i.e. Table1 is the main table, table2 is a sub table of table1 and tablee3 is a sub table of table2.

I found this example from planet source code, but not sure how to add the third or more levels?

oRS.Open "SHAPE {SELECT employeeid,FirstName, " _
& "LastName FROM employees} " _
& "APPEND ({SELECT OrderID, shipname, employeeid " _
& "FROM orders WHERE shipname ='Save-a-lot Markets'} " _
& "AS oRSOrder " _
& "RELATE EmployeeID TO EmployeeID)", oConn

Do While Not oRS.EOF
Set oparNode = TreeView1.Nodes.Add(Text:=oRS.Fields(0) _
& " " & oRS.Fields(1) & " " & oRS.Fields(2))
Set oRSChild = oRS.Fields("oRSOrder&quot.Value
Do While Not oRSChild.EOF
TreeView1.Nodes.Add relative:=oparNode.Index, _
relationship:=tvwChild, Text:=oRSChild.Fields(0) _
& " " & oRSChild.Fields(1)
oRSChild.MoveNext
Loop
oRS.MoveNext
Loop

Thanks for your help

 

[vb5prgrmr]

Yes it is possible to do what you want but I am not sure about your method especially if there is a lot of records. You may want to break it up so that you start off with just retrieving the parent tables records (once again this depends upon how many records are in the db)...
[tt]
+Table1.Record1
+Table1.Record2
...
[/tt]
Then when a user selects one if the nodes then retrieve only the records for that node.
[tt]
+Table1.Record1
+Table2.Record1.Based Upon Foreign Key Query (Table1.Table2)
+Table2.Record2.Based Upon Foreign Key Query (Table1.Table2)
+Table2.Record3.Based Upon Foreign Key Query (Table1.Table2)
+Table1.Record2
...
[/tt]
and then do the same for the final child
[tt]
+Table1.Record1
+Table2.Record1.Based Upon Foreign Key Query (Table1.Table2)
+Table3.Record1.Based Upon Foreign Key Query (Table2.Table3)
+Table3.Record2.Based Upon Foreign Key Query (Table2.Table3)
+Table3.Record3.Based Upon Foreign Key Query (Table2.Table3)
+Table2.Record2.Based Upon Foreign Key Query (Table1.Table2)
+Table2.Record3.Based Upon Foreign Key Query (Table1.Table2)
+Table1.Record2
...
[/tt]
You might also want to look at the LockWindowUpdate API to help speed up the loading of the TV.

Good Luck

 

[Oliver2003]

vb5prgrmr thanks for the reply.

There are not that many records, but it makes sense to only retrieve the records for a node that is expanded. I am looking at using it to build up a reference number based on what the user has clicked on. Combo box might work just as well!