By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,720 Members | 2,135 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,720 IT Pros & Developers. It's quick & easy.

One MSAccess table to populate the treeview

P: 5
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
__________________________________________________ ______________
Expand|Select|Wrap|Line Numbers
  1. Asset_ID Asset               Parent_ID
  2.    1     Pump                   0
  3.    2     UPS                    0
  4.    3     Transmitter            0
  5.    4     UPS & Battery          2
  6.    5     Battery Banks          4
  7.    6     Battery Charger        4
  8.    7     Interconnecting cables 4
  9.    8     Test1                  7
  10.    9     Test2                  7
  11.   10     Test3                  6
  12.   11     Test1a                 8
  13.   12     Test1b                 8
  14.   13     Test1a_1              12
Thanks in advance

Euan
Aug 5 '08 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,489
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)?
Aug 9 '08 #2

ADezii
Expert 5K+
P: 8,634
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:
Expand|Select|Wrap|Line Numbers
  1. Dim nodX As Node
  2. Dim MyDB As DAO.Database
  3. Dim MyRS As DAO.Recordset
  4. Dim MyRSChild As DAO.Recordset
  5. Dim strSQL As String
  6.  
  7. Me![TReeview1].LineStyle = 1    'Root lines
  8. Me![TReeview1].Style = 7        'TreelinesPlusMinusPictureText
  9.  
  10. Set MyDB = CurrentDb()
  11. Set MyRS = MyDB.OpenRecordset("Customers", dbOpenDynaset)
  12.  
  13. '1-Relative, tvwChild-Relationship, "g"-Key, "George"-Text "ROOT Node"
  14. Set nodX = TReeview1.Nodes.Add(, , , "Customers and Related Orders")
  15.  
  16. 'Populate Customer Nodes
  17. Do While Not MyRS.EOF
  18.   Set nodX = TReeview1.Nodes.Add(1, tvwChild, "Customer" & MyRS![CustomerID], MyRS![CompanyName])
  19.     nodX.EnsureVisible
  20.   MyRS.MoveNext
  21. Loop
  22.  
  23. strSQL = "Select * From Orders"
  24. Set MyRSChild = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
  25.  
  26. 'Populate Orders Nodes
  27. Do While Not MyRSChild.EOF
  28.   Set nodX = TReeview1.Nodes.Add("Customer" & MyRSChild![CustomerID], tvwChild, "A" & CStr(MyRSChild![OrderID]), _
  29.              "Order " & MyRSChild![OrderID] & " shipped on " & MyRSChild![ShippedDate] & " Freight Charge " & _
  30.              Format(MyRSChild![Freight], "Currency"))
  31.   MyRSChild.MoveNext
  32. Loop
  33.  
  34. 'Accentuate the Root by making Text Bold
  35. TReeview1.Nodes(1).ForeColor = QBColor(4)
  36.  
  37. 'Select the Root
  38. TReeview1.Nodes(1).Selected = True
  39.  
  40. Me!TReeview1.SetFocus
  41. SendKeys "^{HOME}"
  42.  
  43. MyRSChild.Close
  44. MyRS.Close
  45. Set MyRSChild = Nothing
  46. Set MyRS = Nothing
Aug 10 '08 #3

P: 5
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:
Expand|Select|Wrap|Line Numbers
  1. Dim nodX As Node
  2. Dim MyDB As DAO.Database
  3. Dim MyRS As DAO.Recordset
  4. Dim MyRSChild As DAO.Recordset
  5. Dim strSQL As String
  6.  
  7. Me![TReeview1].LineStyle = 1    'Root lines
  8. Me![TReeview1].Style = 7        'TreelinesPlusMinusPictureText
  9.  
  10. Set MyDB = CurrentDb()
  11. Set MyRS = MyDB.OpenRecordset("Customers", dbOpenDynaset)
  12.  
  13. '1-Relative, tvwChild-Relationship, "g"-Key, "George"-Text "ROOT Node"
  14. Set nodX = TReeview1.Nodes.Add(, , , "Customers and Related Orders")
  15.  
  16. 'Populate Customer Nodes
  17. Do While Not MyRS.EOF
  18.   Set nodX = TReeview1.Nodes.Add(1, tvwChild, "Customer" & MyRS![CustomerID], MyRS![CompanyName])
  19.     nodX.EnsureVisible
  20.   MyRS.MoveNext
  21. Loop
  22.  
  23. strSQL = "Select * From Orders"
  24. Set MyRSChild = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
  25.  
  26. 'Populate Orders Nodes
  27. Do While Not MyRSChild.EOF
  28.   Set nodX = TReeview1.Nodes.Add("Customer" & MyRSChild![CustomerID], tvwChild, "A" & CStr(MyRSChild![OrderID]), _
  29.              "Order " & MyRSChild![OrderID] & " shipped on " & MyRSChild![ShippedDate] & " Freight Charge " & _
  30.              Format(MyRSChild![Freight], "Currency"))
  31.   MyRSChild.MoveNext
  32. Loop
  33.  
  34. 'Accentuate the Root by making Text Bold
  35. TReeview1.Nodes(1).ForeColor = QBColor(4)
  36.  
  37. 'Select the Root
  38. TReeview1.Nodes(1).Selected = True
  39.  
  40. Me!TReeview1.SetFocus
  41. SendKeys "^{HOME}"
  42.  
  43. MyRSChild.Close
  44. MyRS.Close
  45. Set MyRSChild = Nothing
  46. 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.
Aug 12 '08 #4

P: 5
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?
Aug 12 '08 #5

ADezii
Expert 5K+
P: 8,634
  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:
    Expand|Select|Wrap|Line Numbers
    1. Me![TreeView1].OLEDragMode = ccOLEDragAutomatic
  5. You can also initiate a Manual Drag Mode by:
    Expand|Select|Wrap|Line Numbers
    1. 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).
Aug 12 '08 #6

NeoPa
Expert Mod 15k+
P: 31,489
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.
Aug 12 '08 #7

ADezii
Expert 5K+
P: 8,634
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:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Load()
    2. Dim nodX As Node
    3.  
    4. Me![TreeView1].LineStyle = 1    'Root lines
    5. Me![TreeView1].Style = 7        'TreelinesPlusMinusPictureText
    6.  
    7. Set nodX = TreeView1.Nodes.Add(, , , "TreeView Drag-N-Drop Demonstration (ROOT)")         'Root of Treeview Control
    8. 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:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub TreeView1_OLEDragDrop(Data As Object, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)
    2. Dim nodX As Node
    3. Dim varFileName As Variant
    4. Dim strKey As String
    5.  
    6. If Shift = 1 Then       'only if Shift is held, Drag-N-Drop
    7.   If Data.GetFormat(ccCFFiles) Then
    8.     For Each varFileName In Data.Files
    9.      '1-Relative, tvwChild-Relationship, "g"-Key, "George"-Text
    10.      'The Unique Key will be the Absolute Path to the File without the Extension
    11.      'The Displayed Text will be the Absolute Path to the File with the Extension
    12.      strKey = Left$(varFileName, Len(varFileName) - 4)
    13.      Set nodX = TreeView1.Nodes.Add(1, tvwChild, strKey, varFileName)
    14.     Next
    15.   Else    'Data is not in the desired Format
    16.     Effect = ccOLEDropEffectNone
    17.   End If
    18. Else
    19. End If
    20. 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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub TreeView1_NodeClick(ByVal Node As Object)
    2. Dim Msg As String
    3.  
    4. If Node.Text = "TreeView Drag-N-Drop Demonstration (ROOT)" Then
    5.   Me![lblDescription].Caption = "Root Node - No Information available!"
    6. Else
    7.   Msg = "Selected Node: " & Node.Text & vbCrLf & "Path: " & Node.FullPath & vbCrLf
    8.   Msg = Msg & "Number of Children: " & Node.Children & vbCrLf
    9.   Msg = Msg & "Parent: " & Node.Parent
    10.     Me![lblDescription].Caption = Msg
    11. 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.
Aug 12 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.