treeview with database access

[sal21]

The big root is NomeZona.

similar:

NomeZona
---nomeregione
-----nomeprovincia
-------cap

[HansV]

At the top of the form module:

Code: Select all

Dim cnn As ADODB.Connection
Dim arrLevels

Code to populate the treeview, for example in the Form_Load event procedure:

Code: Select all

Private Sub Form_Load()
    Dim nod As MSComctlLib.Node
    Set cnn = New ADODB.Connection
    cnn.Open ...
    arrLevels = Array("NomeZona", "NomeRegione", "NomeProvincia", "Cap")
    With Me.TreeView0
        .LineStyle = tvwRootLines
        .Style = tvwTreelinesPlusMinusText
        .Nodes.Clear
        Set nod = .Nodes.Add(Text:="Italia")
        AddChildren nod, 0
    End With
End Sub

This uses the following procedure:

Code: Select all

Sub AddChildren(nod As MSComctlLib.Node, level As Long)
    Dim rst As ADODB.Recordset
    Dim fld As String
    Dim sql As String
    Dim chl As MSComctlLib.Node
    fld = arrLevels(level)
    sql = "SELECT DISTINCT [" & fld & "] FROM Comuni_geocoded"
    If level > 0 Then
        sql = sql & " WHERE [" & arrLevels(level - 1) & "]='" & nod.Text & "'"
    End If
    Set rst = New ADODB.Recordset
    rst.Open Source:=sql, ActiveConnection:=cnn, Options:=adCmdText
    Do While Not rst.EOF
        Set chl = Me.TreeView0.Nodes.Add(Relative:=nod, Relationship:=tvwChild, Text:=rst(fld))
        If level < UBound(arrLevels) Then
            AddChildren chl, level + 1
        End If
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
End Sub

S2398.png

[sal21]

At the top of the form module:

Code: Select all

Dim cnn As ADODB.Connection
Dim arrLevels

Code to populate the treeview, for example in the Form_Load event procedure:

Code: Select all

Private Sub Form_Load()
    Dim nod As MSComctlLib.Node
    Set cnn = New ADODB.Connection
    cnn.Open ...
    arrLevels = Array("NomeZona", "NomeRegione", "NomeProvincia", "Cap")
    With Me.TreeView0
        .LineStyle = tvwRootLines
        .Style = tvwTreelinesPlusMinusText
        .Nodes.Clear
        Set nod = .Nodes.Add(Text:="Italia")
        AddChildren nod, 0
    End With
End Sub

This uses the following procedure:

Code: Select all

Sub AddChildren(nod As MSComctlLib.Node, level As Long)
    Dim rst As ADODB.Recordset
    Dim fld As String
    Dim sql As String
    Dim chl As MSComctlLib.Node
    fld = arrLevels(level)
    sql = "SELECT DISTINCT [" & fld & "] FROM Comuni_geocoded"
    If level > 0 Then
        sql = sql & " WHERE [" & arrLevels(level - 1) & "]='" & nod.Text & "'"
    End If
    Set rst = New ADODB.Recordset
    rst.Open Source:=sql, ActiveConnection:=cnn, Options:=adCmdText
    Do While Not rst.EOF
        Set chl = Me.TreeView0.Nodes.Add(Relative:=nod, Relationship:=tvwChild, Text:=rst(fld))
        If level < UBound(arrLevels) Then
            AddChildren chl, level + 1
        End If
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
End Sub

S2398.png

TKS! bro.

sorry but treeview is the only component that i can't figure out how it works.

[HansV]

Unless you have only a small number of levels, it requires recursive code...

[sal21]

At the top of the form module:

Code: Select all

Dim cnn As ADODB.Connection
Dim arrLevels

Code to populate the treeview, for example in the Form_Load event procedure:

Code: Select all

Private Sub Form_Load()
    Dim nod As MSComctlLib.Node
    Set cnn = New ADODB.Connection
    cnn.Open ...
    arrLevels = Array("NomeZona", "NomeRegione", "NomeProvincia", "Cap")
    With Me.TreeView0
        .LineStyle = tvwRootLines
        .Style = tvwTreelinesPlusMinusText
        .Nodes.Clear
        Set nod = .Nodes.Add(Text:="Italia")
        AddChildren nod, 0
    End With
End Sub

This uses the following procedure:

Code: Select all

Sub AddChildren(nod As MSComctlLib.Node, level As Long)
    Dim rst As ADODB.Recordset
    Dim fld As String
    Dim sql As String
    Dim chl As MSComctlLib.Node
    fld = arrLevels(level)
    sql = "SELECT DISTINCT [" & fld & "] FROM Comuni_geocoded"
    If level > 0 Then
        sql = sql & " WHERE [" & arrLevels(level - 1) & "]='" & nod.Text & "'"
    End If
    Set rst = New ADODB.Recordset
    rst.Open Source:=sql, ActiveConnection:=cnn, Options:=adCmdText
    Do While Not rst.EOF
        Set chl = Me.TreeView0.Nodes.Add(Relative:=nod, Relationship:=tvwChild, Text:=rst(fld))
        If level < UBound(arrLevels) Then
            AddChildren chl, level + 1
        End If
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
End Sub

S2398.png

TESTED, WORK PERFECT!!!

Only a little but important modify.

for NomeRegione i need : (COD_REG)-NomeRegione

For example (15)-CAMPANIA

And for NomeProvincia i need: (PR)-NomeProvincia

For example (NA)-NAPOLI

[HansV]

Code: Select all

Dim cnn As ADODB.Connection
Dim arrLevels

Private Sub Form_Load()
    Dim nod As MSComctlLib.Node
    Set cnn = CurrentProject.Connection
    arrLevels = Array("Zona", "Regione", "Provincia", "Cap")
    With Me.TreeView0
        .LineStyle = tvwRootLines
        .Style = tvwTreelinesPlusMinusText
        .Nodes.Clear
        Set nod = .Nodes.Add(Text:="Italia")
        AddChildren nod, 0
    End With
End Sub

Sub AddChildren(nod As MSComctlLib.Node, level As Long)
    Dim rst As ADODB.Recordset
    Dim fld As String
    Dim sql As String
    Dim chl As MSComctlLib.Node
    fld = arrLevels(level)
    sql = "SELECT DISTINCT [" & fld & "] FROM (SELECT ""("" & " & _
        "[codiceZona] & "")-"" & [nomezona] AS Zona, ""("" & [COD_REG] & " & _
        """)-"" & [nomeRegione] AS Regione, ""("" & [PR] & "")-"" & " & _
        "[nomeProvincia] AS Provincia, Comuni_geocoded.cap FROM Comuni_geocoded)"
    If level > 0 Then
        sql = sql & " WHERE [" & arrLevels(level - 1) & "]='" & nod.Text & "'"
    End If
    Set rst = New ADODB.Recordset
    rst.Open Source:=sql, ActiveConnection:=cnn, Options:=adCmdText
    Do While Not rst.EOF
        Set chl = Me.TreeView0.Nodes.Add(Relative:=nod, Relationship:=tvwChild, Text:=rst(fld))
        If level < UBound(arrLevels) Then
            AddChildren chl, level + 1
        End If
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
End Sub

[sal21]

Code: Select all

Dim cnn As ADODB.Connection
Dim arrLevels

Private Sub Form_Load()
    Dim nod As MSComctlLib.Node
    Set cnn = CurrentProject.Connection
    arrLevels = Array("Zona", "Regione", "Provincia", "Cap")
    With Me.TreeView0
        .LineStyle = tvwRootLines
        .Style = tvwTreelinesPlusMinusText
        .Nodes.Clear
        Set nod = .Nodes.Add(Text:="Italia")
        AddChildren nod, 0
    End With
End Sub

Sub AddChildren(nod As MSComctlLib.Node, level As Long)
    Dim rst As ADODB.Recordset
    Dim fld As String
    Dim sql As String
    Dim chl As MSComctlLib.Node
    fld = arrLevels(level)
    sql = "SELECT DISTINCT [" & fld & "] FROM (SELECT ""("" & " & _
        "[codiceZona] & "")-"" & [nomezona] AS Zona, ""("" & [COD_REG] & " & _
        """)-"" & [nomeRegione] AS Regione, ""("" & [PR] & "")-"" & " & _
        "[nomeProvincia] AS Provincia, Comuni_geocoded.cap FROM Comuni_geocoded)"
    If level > 0 Then
        sql = sql & " WHERE [" & arrLevels(level - 1) & "]='" & nod.Text & "'"
    End If
    Set rst = New ADODB.Recordset
    rst.Open Source:=sql, ActiveConnection:=cnn, Options:=adCmdText
    Do While Not rst.EOF
        Set chl = Me.TreeView0.Nodes.Add(Relative:=nod, Relationship:=tvwChild, Text:=rst(fld))
        If level < UBound(arrLevels) Then
            AddChildren chl, level + 1
        End If
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
End Sub

i test it in access:

SELECT DISTINCT [NomeZona] FROM (SELECT "(" & [codiceZona] & ")-" & [nomezona] AS Zona, "(" & [COD_REG] & ")-" & [nomeRegione] AS Regione, "(" & [PR] & ")-" & [nomeProvincia] AS Provincia, Comuni_geocoded.cap FROM Comuni_geocoded)

have error in immagine.jpg

in vb6 this:

rst.Open Source:=sql, ActiveConnection:=CNN, Options:=adCmdText<<<<< error in immagine1jpg

[sal21]

sorry bro...
i have read the with attention...

arrLevels = Array("Zona", "Regione", "Provincia", "Cap")
this is correct!

not this:
arrLevels = Array("NomeZona", "NomeRegione", "NomeProvincia", "Cap")

resolved.
Tks

[HansV]