Excel Hints

Results 1 to 5 of 5

Thread: Solved: Populate Treeview

  1. #1

    Solved: Populate Treeview

    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

    [VBA] 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
    [/VBA]
    Last edited by gibbo1715; 10-22-2005 at 11:12 AM. Reason: change to code

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,101
    Location
    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

    [VBA]
    '>>>>> 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
    [/VBA]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    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

  4. #4
    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

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,101
    Location
    Quote Originally Posted by gibbo1715
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •