MS ACCESS Drag and drop LISTVIEW to TREEVIEW help

[johny6685]

Hi Team,

I am working on developing a reporting tool, user would like to show all the column headers in one list box and they would like to drag and drop the required fields in a treeview box. From Tree view box they will be filtering further more for the report.

This is similar to PIVOT TABLE functionality we use in excel. list of fieldnames, drag and drop option for ROWS and COLUMNS treeview box. An additional requirement is we will have the filter option with the checkboxes in Treeview, when the treeview loads the child value from query based on the fieldnames.


I have designed the LISTVIEW and took some code that helps me to construct the code to bring all the required table headers in listbox. Now I am having challenge to drag and drop the listview item to treeview and the treeview should load the child from a query (QryConsolidate dData) data.

Please could someone help me to drag and drop the listview item to treeview item and the tree view will immediately loads the childs for the dropped column.


So far my code goes like this

Private Sub Form_Load()

    Dim strSQL As String
    Dim nodSelected As Node
    Dim lv As ListView
    Set lv = Me.FColumns.Object
    Dim adCmdText
    adCmdText = 1
    lv.ListItems.Clear
    lv.ColumnHeaders.Clear

'Update the listview box to show the positions held
    strSQL = "SELECT [B]BuildKey[/B]([ID]) AS ItemKey" & _
             " ,[1FieldNames] as Columns" & _
             " FROM [test]" & _
             " WHERE BuildKey([1Required]) = TRUE"


'Build the list view
            lv.ListItems.Clear
            With CurrentProject.Connection
            [B]AddLVItems [/B]lv, .Execute(strSQL, , adCmdText), , , "2400,0"
            '"2880,2160"
            End With


End Sub



Public Function BuildKey(ParamArray varKeyValues() As Variant) As String
'Joins the values of the passed varKeyValues to build a key.
'ALL Node keys MUST begin with a character.  For simplicity, this code
'uses a constant, tv_conKeyDesignator, for this character.  Also, for
'simplicity, all values used to build the key are separated by a constant,
'tv_conKeySeparator.  This methodology is used in order to create a consistent
'approach to building the nodes/keys on a tree view object.
'
'NOTE: In order to maintain consistency, it is recommended that you
'set the PathSeparator property of the treeview object to be equivalent to
'tv_conKeySeparator
    
    Dim strTemp As String 'A 'working' string value
        
    'Build the key.
    strTemp = tv_conKeyDesignator & Join(varKeyValues(), tv_conKeySeparator)
    
    'Return the result
    BuildKey = Trim(strTemp)
    
End Function



Public Sub AddLVItems(lv As ListView, _
                      rst As ADODB.Recordset, _
                      Optional intKeyOrdinal As Integer = 0, _
                      Optional intItemOrdinal As Integer = 1, _
                      Optional strColumnWidths As String)
'Fills a List View object with the records from the passed rst
    
    Dim intTotCount As Integer
    Dim fld As ADODB.Field
    Dim colNewColumn As ColumnHeader 'A list view column header
    Dim liNewRow As ListItem 'An Item in the list
    Dim x As Long
    Dim lngSubItem As Long
    Dim strArrColumnWidths() As String

    'Clear the list view
    lv.ListItems.Clear
    lv.ColumnHeaders.Clear

    'Set Column Headers and set to report view
    For Each fld In rst.Fields
        If fld.Name <> rst(intKeyOrdinal).Name Then
            Set colNewColumn = lv.ColumnHeaders.Add(, fld.Name, fld.Name)
        End If
    Next fld
    lv.View = lvwReport
    
    'Set Column widths
    If strColumnWidths = "" Then
        'Do Nothing
    Else
        strArrColumnWidths = Split(strColumnWidths, lv_conCSVSeparator)
        For x = LBound(strArrColumnWidths) To UBound(strArrColumnWidths) - 1
                    lv.ColumnHeaders(x + 1).Width = CInt(strArrColumnWidths(x))
        Next x
    End If
    
    'Loop through the rst and fill the list view
    Do Until rst.EOF
    
        'Add the row identifier ... which is the first field of the rst
        Set liNewRow = lv.ListItems.Add(, rst(intKeyOrdinal).Value, CStr(rst(intItemOrdinal).Value))
        
        'Add the sub-items (other columns) of the row
        For Each fld In rst.Fields
            If fld.Name <> rst.Fields(intKeyOrdinal).Name _
               And fld.Name <> rst.Fields(intItemOrdinal).Name Then
                lngSubItem = lngSubItem + 1
                liNewRow.SubItems(lngSubItem) = Nz(fld.Value, "")
            End If
        Next fld
        lngSubItem = 0  'Clears the sub item index

        'Move to the next record
        rst.MoveNext
        
    Loop
    
End Sub

[Seth Schrock]

Try looking through the video series by TheSmileyCoder. He has done extensive work on his tutorials and I'm sure that it will help you.

[johny6685]

Thank for the reply Seth. But unfortunately I do not have access to view the video in office, I will try to look at it once I reach home.

In the mean time please could you help me to code the treeview part.

I managed to move the selected data from List view to tree view. I am now stuck in to the Treeview part. How can I load the child nodes for all the field items I populated in the Treeview.

Please could someone help me to populate the child nodes from query for all fieldnames I have populated.