TreeView

[Gomlalaw]

I have MS access table which I want to view in Tree View in hierarchical levels from a table called “TesfaPersonal”. • I want to select all the AAs first and list them in TreeView (BY Tesfa_Name) in hierarchical level as per Tesf_level field indicated and then BBs in levels and so forth till all are listed in the treeview in a form. The DDs max level is 4.
On a new Form - drag the ActiveX-TreeView (6.0) into the form and call it "Treeview1"

Private Sub Form_Load()

Dim db As DAO.Database
Dim rs As DAO.Recordset
….
I had a hard time writing VBA codes for this. PLEASE HELP
The table is something like this
Tesfa_ID Tesfa_Type Tesfa_Level Tesfa_Name
1 AA 0 James
2 BB 0 Joe
3 CC 0 Jon
4 DD 0 Rahel
5 AA 1 Abebe
6 BB 1 kebede
7 CC 1 Kim
8 DD 1 James
9 AA 2 Abraham
10 BB 2 Mohammed
11 CC 2 Mary
12 DD 2 Martha
13 AA 3 Garrard
14 BB 3 Steven
15 CC 3 Chala
16 DD 3 Joe
17 DD 4 Kim

Regards
Tesfayesus

 

[galorin]

Do you have any code that you could share?

Also, take a look here for getting data into the treeview

http://support.microsoft.com/kb/209891

I've personally never used a treeview. For me it seems like a lot of code that there may be better ways of accomplishing the same, or similar results with less complex code.

 

[MajP]

Private Sub LoadTree()
  Dim rsType As dao.Recordset
  Dim rsLevel As dao.Recordset
  Dim strSqlType As String
  Dim strSqlLevel As String
  Dim TesfaID As Integer
  Dim TesfaName As String
  Dim TesfaLevel As Integer
  Dim strWhere As String
  Dim nodeParent As Node
  Dim nodeCurrent As Node
  Dim objTree As TreeView
  Dim tempLevel As Integer
  
  Set objTree = Me!xTree.Object
  strSqlType = "Select Distinct Tesfa_Type from tblTesfa order by Tesfa_Type"
  Set rsType = CurrentDb.OpenRecordset(strSqlType, dbOpenDynaset)
  
  Do While Not rsType.EOF
    strWhere = "Tesfa_Type = '" & rsType.Fields("Tesfa_Type") & "' ORDER BY Tesfa_Level"
    strSqlLevel = "Select * from tblTesfa Where " & strWhere
    Set rsLevel = CurrentDb.OpenRecordset(strSqlLevel, dbOpenDynaset)
    
    Do While Not rsLevel.EOF
      TesfaName = rsLevel.Fields("Tesfa_Name")
      TesfaID = rsLevel.Fields("Tesfa_ID")
      TesfaLevel = rsLevel.Fields("Tesfa_Level")
      'Check if root Node
      If TesfaLevel = 0 Then
        Set nodeCurrent = objTree.Nodes.Add(, , "ID " & TesfaID, TesfaName)
      Else
      'Not a root node
       If tempLevel = TesfaLevel Then
           Set nodeParent = nodeCurrent.Parent
        Else
           Set nodeParent = nodeCurrent
        End If
        Set nodeCurrent = objTree.Nodes.Add(nodeParent, tvwChild, "ID " & TesfaID, TesfaName)
        End If
        tempLevel = TesfaLevel
        rsLevel.MoveNext
    Loop
    rsType.MoveNext
  Loop
End Sub

 

[formerTexan]

Hello Tesfayesus

As Galorin indicated, using an alternative method to display the data will be less coding intensive and may serve the user just as well. Perhaps a series of linked combo/list boxes.

However if you want to pursue the treeview control, then here are a few considerations.

Continuing with your current approach, you will need to nest and open recordsets in order to retrieve the data in a heirachial manner.

The 1st (which you already have) is by Tesfa_Type
Inside of this will be by Tesfa_Level for each Tesfa_Type
Inside the 2nd you will have Tesfa_Name for each Tesfa_Level

I suggest that you work through one level at a time and be sure you are getting the results you want before tackling the next level.

Can you explain what you are intending by using the tempLevel variable? This seems like something that might be unnecessary.

Also its good practice to explicitly close all objects such as controls and nodes as soon as possible.

Cheers, Bill

 

[MajP]

formerTexan,
The above code works fine, not sure what your point are. Since the data as posted is not self referencing, the tempLevel is needed to determine who the parent is for two nodes of a given type within the same level. Another possibility is to store the level within the tag property of a node, and then check the parents node level.

 

[formerTexan]

My apologies for the skepticism about MajP's code. It works fine and my "point" is moot.

A small code addition will avoid the raising of an error in the event a root record is unavailable. For example, if record 1 has been deleted.

If Not nodeCurrent Is Nothing Then

'Not a root node
If tempLevel = TesfaLevel Then
Set nodeParent = nodeCurrent.Parent
Else
Set nodeParent = nodeCurrent
End If
Set nodeCurrent = objTree.Nodes.Add(nodeParent, tvwChild, "ID " & TesfaID, TesfaName)

End If

Cheers, Bill