Solved TreeView control

[Cguizzardi]

Dear All
i m sorry to post this topic but i spend all evening of the last 2 weeks without get any good results.

i m trying to add nodes to the Tree view control on Access, but basically i have 2 cases:
1 - if i stop to the first level of nodes i don't have any issue but when i go to the seconds i get the error related to the not unique key
2- i m trying to add the condition where to the vba code but i m not able to set the correct words for the Where condition on the sql string

Private Sub cmdCarica_Click()

Dim tempNode As MSComctlLib.Node
Dim rsSys As DAO.Recordset 'contiene i record dei systemi
Dim rsSSys As DAO.Recordset 'contiene i record dei Sotto Sistemi
Dim rsItem As DAO.Recordset 'contiene i record degli Item
Dim rsAct As DAO.Recordset 'contiene i record delle attività
tv.Nodes.Clear

Set tempNode = tv.Nodes.Add(, , "S", "Project")
Set rsSys = CurrentDb.OpenRecordset("SELECT DISTINCT Qry_Progress.[System Number], Qry_Progress.[System Name]FROM Qry_Progress ORDER BY Qry_Progress.[System Number]", , dbReadOnly)
Do While Not rsSys.EOF
        'CARICO I SYSTEMI
        Set tempNode = tv.Nodes.Add("S", _
                        tvwChild, _
                        "SYS" & rsSys.Fields("System Number"), _
                        rsSys.Fields("System Number") & " - " & rsSys.Fields("System Name"))
            'CARICO I SOTTO-SYSTEMI
            Set rsSSys = CurrentDb.OpenRecordset("SELECT DISTINCT Qry_Progress.[Sub System Number], Qry_Progress.[Sub System Name] FROM Qry_Progress WHERE Qry_Progress.[Sub System Number] = ""rsSys.Fields("Sub System Number")"" ORDER BY Qry_Progress.[Sub System Number]", , dbReadOnly)
            Do While Not rsSSys.EOF
            Set tempNode = tv.Nodes.Add("SYS" & rsSys.Fields("System Number"), _
                                        tvwChild, _
                                        "Sub_SYS" & rsSSys.Fields("Sub System Number"), _
                                        rsSSys.Fields("Sub System Number") & " - " & rsSSys.Fields("Sub System Name"))
'
'                 'CARICO GLI ITEM
'                Set rsItem = CurrentDb.OpenRecordset("SELECT DISTINCT Qry_Progress.[TAG NO], Qry_Progress.[Tag Name] FROM Qry_Progress", , dbReadOnly)
'                Do While Not rsItem.EOF
'                Set tempNode = tv.Nodes.Add("Sub_SYS" & rsSSys.Fields("Sub System Number"), _
'                                            tvwChild, _
'                                            "Item" & "Sub_SYS" & rsSSys.Fields("Sub System Number") & rsItem.Fields("TAG NO"), _
'                                            rsItem.Fields("TAG NO") & " - " & rsItem.Fields("Tag Name"))
'
'                 '   'CARICO LE ATTIVITà
'                  '  Set rsAct = CurrentDb.OpenRecordset("SELECT DISTINCT Qry_Progress.[BASIC ACTIVITY LIST], Qry_Progress.[ITR Form] FROM Qry_Progress", , dbReadOnly)
'                   ' Do While Not rsAct.EOF
'                    'Set tempNode = tv.Nodes.Add("Item" & rsItem.Fields("TAG NO"), _
'                                                tvwChild, _
'                                                "ACT" & rsAct.Fields("BASIC ACTIVITY LIST"), _
'                                                rsAct.Fields("BASIC ACTIVITY LIST") & " - " & rsAct.Fields("ITR Form"))
'                    'rsAct.MoveNext
'                    'Loop
'                rsItem.MoveNext
'                Loop
'
            rsSSys.MoveNext
            Loop

        rsSys.MoveNext
        Loop

rsSys.Close
rsSSys.Close
'rsItem.Close
'rsAct.Close

End Sub

 

[MajP]

See detailed discussion on a better way to do this and make life a lot easier.

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

Just build consistent queries to make it easier. Every query should look like this with alias

NodeID "concatenate and Identifier with the ID to create a unique ID per table"
NodeText "Concatenated information to display"
ParentID "NodeID of the parent query"
Identifier "The identifier you concatenate to the ID"

qryNodeSys qryNodeSys

NodeID	NodeText	ParentID	Identifier
SYS10-001-P	10-001-P-HP NATURAL GAS	S	SYS
SYS10-009-P	10-009-P-C02 REMOVAL SYSTEM	S	SYS
SYS10-010-P	10-010-P-GV SOLUTION PREPARATION AND STORAGE SYSTEM	S	SYS
SYS10-035-P	10-035-P-FUEL GAS DISTRIBUTION	S	SYS

qryNodeSubSys qryNodeSubSys

NodeID	NodeText	ParentID	Identifier
Sub_SYS10-001-P-001	10-001-P-001-HP NATURAL GAS	SYS10-001-P	Sub_SYS
Sub_SYS10-009-P-001	10-009-P-001-CO2 ABSORBER (C302)	SYS10-009-P	Sub_SYS
Sub_SYS10-009-P-002	10-009-P-002-GV REGENERATION (C301 AND C303) AND LEAN SOLUTION ON PUMPS	SYS10-009-P	Sub_SYS
Sub_SYS10-010-P-001	10-010-P-001-GV SOLUTION FILTRATION AND PREPARATION	SYS10-010-P	Sub_SYS
Sub_SYS10-035-P-001	10-035-P-001-FUEL GAS DISTRIBUTION	SYS10-035-P	Sub_SYS

qryNodeItems qryNodeItems

NodeID	NodeText	ParentID	Identifier
ITEMSub_SYS10-001-P-001H10-F102	H10-F102-NATURAL GAS FEED FILTER	Sub_SYS10-001-P-001	ItemSub_SYS
ITEMSub_SYS10-009-P-001H10-P350a	H10-P350a-SEAL BOOSTER PUMP FOR H10-RP301-A REVERSE PUMP	Sub_SYS10-009-P-001	ItemSub_SYS
ITEMSub_SYS10-009-P-001H10-P350B	H10-P350B-SEAL BOOSTER PUMP FOR H10-RP301-A REVERSE PUMP	Sub_SYS10-009-P-001	ItemSub_SYS
ITEMSub_SYS10-009-P-002H10-P302B	H10-P302B-LEAN SOLUTION PUMP	Sub_SYS10-009-P-002	ItemSub_SYS
ITEMSub_SYS10-010-P-001H10-F306	H10-F306-1ST MECHANICAL FILTER	Sub_SYS10-010-P-001	ItemSub_SYS
ITEMSub_SYS10-010-P-001H10-F307	H10-F307-ACTIVATED CARBON FILTER	Sub_SYS10-010-P-001	ItemSub_SYS
ITEMSub_SYS10-010-P-001H10-F308	H10-F308-2ND MECHANICAL FILTER	Sub_SYS10-010-P-001	ItemSub_SYS
ITEMSub_SYS10-035-P-001H10-F101	H10-F101-FUEL GAS FILTER	Sub_SYS10-035-P-001	ItemSub_SYS

Now look how simple the code is. It is the same code every level

Private Sub cmdCarica_Click()

Dim tempNode As MSComctlLib.Node
Dim rsSys As DAO.Recordset 'contiene i record dei systemi
Dim rsSSys As DAO.Recordset 'contiene i record dei Sotto Sistemi
Dim rsItems As DAO.Recordset 'contiene i record degli Item
Dim rsAct As DAO.Recordset 'contiene i record delle attività
Dim db As DAO.Database

tv.Nodes.Clear

Set tempNode = tv.Nodes.Add(, , "S", "Project")
'Set rsSys = CurrentDb.OpenRecordset("SELECT DISTINCT Qry_Progress.[System Number], Qry_Progress.[System Name]FROM Qry_Progress ORDER BY Qry_Progress.[System Number]", , dbReadOnly)
Set db = CurrentDb
Set rsSys = db.OpenRecordset("qryNodeSys")
Do While Not rsSys.EOF
'        'CARICO I SYSTEMI
        Set tempNode = tv.Nodes.Add("S", _
                        tvwChild, _
                        rsSys!NodeID, _
                       rsSys!NodeText)
        tempNode.Tag = rsSys!Identifier
        Debug.Print tempNode.Key
 rsSys.MoveNext
 Loop
 
 'CARICO I SOTTO-SYSTEMI
 Set rsSSys = db.OpenRecordset("qryNodeSubSys")
 Do While Not rsSSys.EOF
    Set tempNode = tv.Nodes.Add(CStr(rsSSys!ParentID), _
                    tvwChild, _
                    rsSSys!NodeID, _
                   rsSSys!NodeText)
     tempNode.Tag = rsSSys!Identifier
     rsSSys.MoveNext
 Loop

 Set rsItems = db.OpenRecordset("qryNodeItems")
 Do While Not rsItems.EOF
    Set tempNode = tv.Nodes.Add(CStr(rsItems!ParentID), _
                    tvwChild, _
                    rsItems!NodeID, _
                   rsItems!NodeText)
     tempNode.Tag = rsItems!Identifier
     rsItems.MoveNext
 Loop

See if you can make the query for the last level. The code is simply a cut and paste for the next level.

 

[MajP]

FYI. There has always been a bug with the tree view control. You almost always have to explicitly cast the key to a string even if the variable you pass is a variant string. This codes fails without the CSTR
CStr(rsItems!ParentID)

Even though the value of the ParentID is a string as you can see, it is a variant since values pulled from tables are variants.

 

[MajP]

If you were to Union these Node queries into a single query then my thread shows how you can load a treeview with a single line of code using the class module provided. Then you can also double click an node an pull up the corresponding record.

 

[Cguizzardi]

THANK YOU SO MUCH Majp... i have prepared the QRY and now is working fine...now i ll improve the tree with picures etc etc

thanks again

 

[MajP]

And because each query is the same you can make the code even smarter. Replace all your code with this.

Private Sub cmdCarica_Click()
  Dim tempNode As MSComctlLib.Node

  tv.Nodes.Clear
  Set tempNode = tv.Nodes.Add(, , "S", "Project")
  LoadNodes tv.Object, "qryNodeSys"
  LoadNodes tv.Object, "qryNodeSubSys"
  LoadNodes tv.Object, "qryNodeItems"
 LoadNodes tv.Object, "qryNodeActs"
End Sub

Public Sub LoadNodes(tv As TreeView, NodeQryName As String)
  Dim rs As DAO.Recordset
  Dim tempNode As MSComctlLib.Node

  Set rs = CurrentDb.OpenRecordset(NodeQryName, dbReadOnly)
  Do While Not rs.EOF
    Set tempNode = tv.Nodes.Add(CStr(rs!ParentID), _
                    tvwChild, _
                    rs!NodeID, _
                   rs!NodeText)
     tempNode.Tag = rs!Identifier
     rs.MoveNext
Loop

End Sub