One MSAccess table to populate the treeview

[euanwillox]

Hi, I have the following table in access and I am trying to code a treeview control on a form to populate the treeview from the table. The data in the table is similar to that below. I was wanting to use this structure so there is no restriction on the amount of levels. I was hoping to call a function on itself which I have tried but after 3 levels I loose a reference to the original parent. Any help would be much appreciated as I have been searching for a while
__________________________________________________ ______________

Asset_ID Asset               Parent_ID
   1     Pump                   0
   2     UPS                    0
   3     Transmitter            0
   4     UPS & Battery          2
   5     Battery Banks          4
   6     Battery Charger        4
   7     Interconnecting cables 4
   8     Test1                  7
   9     Test2                  7
  10     Test3                  6
  11     Test1a                 8
  12     Test1b                 8
  13     Test1a_1              12

Thanks in advance

Euan

[NeoPa]

I can't possibly consider why your current code is being restricted to three levels without any comments about your current code.

What are you doing now?

How are you intending to display the data (other than simply using a treeview)?

[ADezii]

Euan, I'm not exactly sure that I understand your request, but I put a little code together that will demonstrate how to programmatically populate a TreeView Control for Parent/Child Tables. For simplicity, I used the Customers and Orders Tables of the Sample Northwind Database. If you are having trouble understanding the code, simply download the Attached Database that I used for this Thread, and use it as a Template. Any questions, please feel free to ask:

Dim nodX As Node
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim MyRSChild As DAO.Recordset
Dim strSQL As String
 
Me![TReeview1].LineStyle = 1    'Root lines
Me![TReeview1].Style = 7        'TreelinesPlusMinusPictureText
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("Customers", dbOpenDynaset)
 
'1-Relative, tvwChild-Relationship, "g"-Key, "George"-Text "ROOT Node"
Set nodX = TReeview1.Nodes.Add(, , , "Customers and Related Orders")
 
'Populate Customer Nodes
Do While Not MyRS.EOF
  Set nodX = TReeview1.Nodes.Add(1, tvwChild, "Customer" & MyRS![CustomerID], MyRS![CompanyName])
    nodX.EnsureVisible
  MyRS.MoveNext
Loop
 
strSQL = "Select * From Orders"
Set MyRSChild = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
 
'Populate Orders Nodes
Do While Not MyRSChild.EOF
  Set nodX = TReeview1.Nodes.Add("Customer" & MyRSChild![CustomerID], tvwChild, "A" & CStr(MyRSChild![OrderID]), _
             "Order " & MyRSChild![OrderID] & " shipped on " & MyRSChild![ShippedDate] & " Freight Charge " & _
             Format(MyRSChild![Freight], "Currency"))
  MyRSChild.MoveNext
Loop
 
'Accentuate the Root by making Text Bold
TReeview1.Nodes(1).ForeColor = QBColor(4)
 
'Select the Root
TReeview1.Nodes(1).Selected = True
 
Me!TReeview1.SetFocus
SendKeys "^{HOME}"
 
MyRSChild.Close
MyRS.Close
Set MyRSChild = Nothing
Set MyRS = Nothing

[euanwillox]

Euan, I'm not exactly sure that I understand your request, but I put a little code together that will demonstrate how to programmatically populate a TreeView Control for Parent/Child Tables. For simplicity, I used the Customers and Orders Tables of the Sample Northwind Database. If you are having trouble understanding the code, simply download the Attached Database that I used for this Thread, and use it as a Template. Any questions, please feel free to ask:

Dim nodX As Node
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim MyRSChild As DAO.Recordset
Dim strSQL As String
 
Me![TReeview1].LineStyle = 1    'Root lines
Me![TReeview1].Style = 7        'TreelinesPlusMinusPictureText
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("Customers", dbOpenDynaset)
 
'1-Relative, tvwChild-Relationship, "g"-Key, "George"-Text "ROOT Node"
Set nodX = TReeview1.Nodes.Add(, , , "Customers and Related Orders")
 
'Populate Customer Nodes
Do While Not MyRS.EOF
  Set nodX = TReeview1.Nodes.Add(1, tvwChild, "Customer" & MyRS![CustomerID], MyRS![CompanyName])
    nodX.EnsureVisible
  MyRS.MoveNext
Loop
 
strSQL = "Select * From Orders"
Set MyRSChild = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
 
'Populate Orders Nodes
Do While Not MyRSChild.EOF
  Set nodX = TReeview1.Nodes.Add("Customer" & MyRSChild![CustomerID], tvwChild, "A" & CStr(MyRSChild![OrderID]), _
             "Order " & MyRSChild![OrderID] & " shipped on " & MyRSChild![ShippedDate] & " Freight Charge " & _
             Format(MyRSChild![Freight], "Currency"))
  MyRSChild.MoveNext
Loop
 
'Accentuate the Root by making Text Bold
TReeview1.Nodes(1).ForeColor = QBColor(4)
 
'Select the Root
TReeview1.Nodes(1).Selected = True
 
Me!TReeview1.SetFocus
SendKeys "^{HOME}"
 
MyRSChild.Close
MyRS.Close
Set MyRSChild = Nothing
Set MyRS = Nothing

Great thanks for your help I have manipulated the code to suit my db. Next though is I want to add specific functioanlity to the tree ie drag and drop and add remove nodes with a right click of the mouse.

[euanwillox]

I can't possibly consider why your current code is being restricted to three levels without any comments about your current code.

What are you doing now?

How are you intending to display the data (other than simply using a treeview)?

Just my lack of understanding - it now works to infinative levels. As on my other post I would like to take iut further and add drag and drop then update the table and add and delete nodes from a right click. Thanks for any help. Am I going down the correct path or should I build it in VB?

[ADezii]

1. The TreeView Control exposes the following Events:
   1. OLEDragDrop()
   2. OLEDragOver()
   3. OLECompleteDrag()
   4. OLEStartDrag()
   5. OLEGiveFeedback()
   6. OLESetData()
2. It also exposes the MouseDown() Event giving you the capability to detect the Right Mouse Button via the Button Argument.
3. It would appear the functionality that you are requesting is at hand.
4. You can initially set the Drag Mode of TreeView1 to Automatic via:
   

   Me![TreeView1].OLEDragMode = ccOLEDragAutomatic

5. You can also initiate a Manual Drag Mode by:
   

   Me![TreeView1].OLEDragMode = ccOLEDragManual

6. Sorry I can't be more helpful, since it is not everyday that I use this type of functionality within Access. Used to in Visual Basic, but that was a long time ago! (LOL).

[NeoPa]

Just my lack of understanding - it now works to infinative levels. As on my other post I would like to take iut further and add drag and drop then update the table and add and delete nodes from a right click. Thanks for any help. Am I going down the correct path or should I build it in VB?

I'm very pleased that you've found the answers you're looking for. I will continue to monitor the thread in case, but I suspect that ADezii has more experience in this area than I do. I would have to go and research it all first anyway. So, I'll leave you in his ultra-capable hands. I can still step in if necessary though of course.

[ADezii]

Just my lack of understanding - it now works to infinative levels. As on my other post I would like to take iut further and add drag and drop then update the table and add and delete nodes from a right click. Thanks for any help. Am I going down the correct path or should I build it in VB?

I took the liberty of putting together a little code snippet that demonstrates simple Drag-N-Drop capability within the context of the TreeView Control. With this code you can open Windows Explorer select one or more Files, hold the Shift Key down, then Drag and Drop them into the TreeView Window. The File Names (with Absolute Paths) will then be added as individual Nodes under a main Root Node. The Unique Key defining each added Node is the Path to the File minus the extension, while the displayed Text is the Path including the extension. Take the following steps in sequence, or simply download the Test Database I designed for this demo.

1. Place the following code in the Load() Event of your Form to dynamically set a couple of Properties, and to create the Root Node:
   

   Private Sub Form_Load()
   Dim nodX As Node
    
   Me![TreeView1].LineStyle = 1    'Root lines
   Me![TreeView1].Style = 7        'TreelinesPlusMinusPictureText
    
   Set nodX = TreeView1.Nodes.Add(, , , "TreeView Drag-N-Drop Demonstration (ROOT)")         'Root of Treeview Control
   End Sub

2. Set the following Properties of the TreeView Control manually via:
   1. Select TreeView Control
   2. Right Click
   3. TreeCtl Object
   4. Properties
   5. General Tab
   6. Set OLEDragMode = 1-ccOLEDragAutoMatic
   7. Set OLEDropMode = 1-ccOLEDropManual
3. Place the following code in the OLEDragDrop() Event:
   

   Private Sub TreeView1_OLEDragDrop(Data As Object, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)
   Dim nodX As Node
   Dim varFileName As Variant
   Dim strKey As String
    
   If Shift = 1 Then       'only if Shift is held, Drag-N-Drop
     If Data.GetFormat(ccCFFiles) Then
       For Each varFileName In Data.Files
        '1-Relative, tvwChild-Relationship, "g"-Key, "George"-Text
        'The Unique Key will be the Absolute Path to the File without the Extension
        'The Displayed Text will be the Absolute Path to the File with the Extension
        strKey = Left$(varFileName, Len(varFileName) - 4)
        Set nodX = TreeView1.Nodes.Add(1, tvwChild, strKey, varFileName)
       Next
     Else    'Data is not in the desired Format
       Effect = ccOLEDropEffectNone
     End If
   Else
   End If
   End Sub

4. Place the following code in the NodeClick() Event. If you do all this manually, you'll need a Label Control named lblDescription.
   

   Private Sub TreeView1_NodeClick(ByVal Node As Object)
   Dim Msg As String
    
   If Node.Text = "TreeView Drag-N-Drop Demonstration (ROOT)" Then
     Me![lblDescription].Caption = "Root Node - No Information available!"
   Else
     Msg = "Selected Node: " & Node.Text & vbCrLf & "Path: " & Node.FullPath & vbCrLf
     Msg = Msg & "Number of Children: " & Node.Children & vbCrLf
     Msg = Msg & "Parent: " & Node.Parent
       Me![lblDescription].Caption = Msg
   End If

5. Now, forget everything I just said and download the Attachment! (LOL).
6. Judging from your ability to adapt previous code to your specific needs, I don't think you will need further help in this matter, but if you do, please let me know.