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

    VB:
    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 
    
    
    Formatting tags added by mark007
    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,020
    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

    VB:
     '>>>>> 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 
    
    
    Formatting tags added by mark007
    ____________________________________________
    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,020
    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
  •