Solved: Populate Treeview

[gibbo1715]

All

Im trying to populate a treeview on a userform from an access table using the code below

I want the parent to be the table(Recordset) name and the child to be the contents of the column FieldName2 but Im getting nowhere

My code works and I ve figured out how to populate it with the table column titles but what i want to be able to do is list the records in the columns

Anyone got any ideas as I cant find the answer to this anywhere

Thanks

Gibbo

Sub Populate_Treeview()
Dim cnt As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDb As String, strSQL As String
Dim xlCalc As XlCalculation
Dim j As Long, x As Long

'SET YOUR VARIABLES HERE:
'Path to the database and SQL-statement to execute
strDb = "C:\Gibbos.mdb"
strSQL = "SELECT tbl_TEST.* From tbl_Test"

'In order to increase performance
With Application
xlCalc = .Calculation
.EnableEvents = False
.ScreenUpdating = False
End With

'Establish and ADO connection
Set cnt = New ADODB.Connection

'Create the connection string
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDb & ";"

'Retrieve the recordset and count fields & records
rst.Open strSQL, cnt
'Add the items to the listbox
With UserForm1
UserForm1.TreeView1.Nodes.Add , , "Table", "Table"
'Child
For x = 0 To rst.Fields.Count - 1
UserForm1.TreeView1.Nodes.Add "Table", 4, , rst.Fields(x).Name
Next x
End With
AddCounter

'Close the recordset, connection and release objects from memory
With rst
.Close
.ActiveConnection = Nothing
End With
cnt.Close
Set rst = Nothing
Set cnt = Nothing

'Restore the settings
With Application
.Calculation = xlCalc
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

[Bob Phillips]

Gibbo,

Quite a few errors in this code, but the worst one (IMO) is getting mixed up with variable names, sometimes you use a variable called rst for the Revordset variable, sometimes rstADO. As you only declared rst (which I have changed), this is a problem. It is the worst because it can be avoided. Put Option Explicit at the head of your code, and you cannot compile with undeclared variabkles, so the compiler will bring your attention to it.

Anyway, this works for me


'>>>>> Always use this next line
Option Explicit

Sub Populate_Treeview()
Dim cnt As ADODB.Connection
'>>>>> be consistent with the variable
Dim rstADO As New ADODB.Recordset
Dim strDb As String, strSQL As String
Dim xlCalc As XlCalculation
Dim j As Long, x As Node '>>>> Not Long!!!!!!!!
Dim lcols
Dim vaData

'SET YOUR VARIABLES HERE:
'Path to the database and SQL-statement to execute
strDb = "C:\Gibbos.mdb"
strSQL = "SELECT tbl_TEST.* From tbl_Test"

'In order to increase performance
With Application
xlCalc = .Calculation
.EnableEvents = False
.ScreenUpdating = False
End With

'Establish and ADO connection
Set cnt = New ADODB.Connection

'Create the connection string
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDb & ";"

'Retrieve the recordset and count fields & records
rstADO.Open strSQL, cnt

With rstADO
'>>>>> You canot close the connection and then work with it
lcols = .Fields.Count
'Populate a variant array with the recordset.
vaData = .GetRows
'.ActiveConnection.Close
'Set .ActiveConnection = Nothing
'Disconnect recordset.
End With

'Add the items to the treeview
With UserForm2 'UserForm1
'>>>>> Specify the linestyle
.TreeView1.LineStyle = tvwRootLines
With .TreeView1.Nodes
Set x = .Add(, , rstADO.Fields(2).Name, rstADO.Fields(2).Name)
For j = LBound(vaData, 2) To UBound(vaData, 2)
Set x = .Add(rstADO.Fields(2).Name, tvwChild, , vaData(2, j))
Next j
End With
End With

'Close the recordset, connection and release objects from memory
With rstADO
.Close
.ActiveConnection = Nothing
End With
cnt.Close
Set rstADO = Nothing
Set cnt = Nothing

'Restore the settings
With Application
.Calculation = xlCalc
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub

[gibbo1715]

xld

Thanks again

I know it was all over the place, that was down to me re-using other bits of code I had created and hadnt really looked too hard at that point

Works great for me

Cheers

Gibbo

[gibbo1715]

Set x = .Add(, , rstADO.Fields(2).Name, rstADO.Fields(2).Name)
For j = LBound(vaData, 2) To UBound(vaData, 2)
Set x = .Add(rstADO.Fields(2).Name, tvwChild, , vaData(2, j))
Next j

Can some one explain the format of LBound(vaData, 2) please, I get the LBound Ubound are top and bottom of my array, but what does the two mean please

Cheers

Gibbo

[Bob Phillips]

Can some one explain the format of LBound(vaData, 2) please, I get the LBound Ubound are top and bottom of my array, but what does the two mean please

It is explained in VBA help.