The big root is NomeZona.
similar:
NomeZona
---nomeregione
-----nomeprovincia
-------cap
treeview with database access
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
treeview with database access
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: treeview with database access
At the top of the form module:
Code to populate the treeview, for example in the Form_Load event procedure:
This uses the following procedure:
Code: Select all
Dim cnn As ADODB.Connection
Dim arrLevels
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
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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
Re: treeview with database access
TKS! bro.HansV wrote: ↑17 May 2023, 11:56At the top of the form module:
Code to populate the treeview, for example in the Form_Load event procedure:Code: Select all
Dim cnn As ADODB.Connection Dim arrLevels
This uses the following 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
S2398.pngCode: 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
sorry but treeview is the only component that i can't figure out how it works.
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: treeview with database access
Unless you have only a small number of levels, it requires recursive code...
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
Re: treeview with database access
TESTED, WORK PERFECT!!!HansV wrote: ↑17 May 2023, 11:56At the top of the form module:
Code to populate the treeview, for example in the Form_Load event procedure:Code: Select all
Dim cnn As ADODB.Connection Dim arrLevels
This uses the following 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
S2398.pngCode: 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
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
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: treeview with database access
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
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
Re: treeview with database access
i test it in access:HansV wrote: ↑17 May 2023, 16:16Code: 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
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
You do not have the required permissions to view the files attached to this post.
-
- PlatinumLounger
- Posts: 4355
- Joined: 26 Apr 2010, 17:36
Re: treeview with database access
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
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
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: treeview with database access
Best wishes,
Hans
Hans