ListView Control Drag-Drop Sort Events ~ LEARN MS-ACCESS TIPS AND TRICKS

Rearranging Rows of Data in ListView Control.

In the earlier episode of this Tutorial, we learned how to rearrange the Columns by enabling this feature: AllowColumnReorder option on the Property Sheet. But, relocating a row is done by dragging and placing it on another row. For rearranging the ListView control rows, the ListItem Drag and Drop action requires enabling this feature on the Property Sheet. But this alone will not work, needs VBA Code to rearrange the item to its required order.

Let us create a sample Access Form with controls and VBA Code in our database for this exercise. The sample image of the Form with ListBox and ListView Controls is given below.

We have created a list of Tables and Queries (not Action Queries) in the List Box. Selecting one of the list items will display the records instantly on the ListView control, as we see them in the DataSheet view.

The Design Task.

Create a new Table with a single Text field, with the field name DataList.
Save the Table with the name lvTables (lv stands for ListView).
Open the Table in Datasheet View.
Add a few table names and Select Query names from your database into the Table. I have imported Tables from the Northwind sample database for my list.

Note: The Attachment Field is not valid in ListView Control. Create Select Queries for tables, with attachment field, and select all fields except the Attachment field.
Create and open a new Form in Design View.
Insert a ListBox Control on the form, display the Property Sheet and change its Name property value to List0.
Change its child label Caption value to Tables.
Display the Property Sheet of ListBox control and set the Row Source property value to the lvTables name.
Check whether the Row Source Type is set as Table/Query and the Bound Column property value is 1. If different then change.
Insert a ListView Control from the ActiveX Controls List and change its Name Property Value to ListView1.
Resize both the controls as shown on the demo Form image given above.
Insert a Label above the Controls and change its Name and Caption Property Values to Heading. The Caption value will be changed from vba code when a Table or Query is selected from the ListBox.
Create a Command Button below the Controls and change its Name property value to cmdClose and the Caption property value to Close.
Right-Click on the ListView Control, highlight the ListViewCtrl Object option, and select Properties.
Change the property settings to match the settings in the General Tab image given below.
ListView Control Property Sheet image - General tab view is given below:

Some of these options we have already set in the earlier sessions. Here we need the following options for our Drag Drop action:
- OLEDragAutomatic - 1
- OLEDropManual - 1
- FullRowSelect - True
- HotTracking - True

Ensure that the above settings match with your property sheet, then save the Form.

Display the VBA Module of the Form.

The Form Module VBA Code.

Copy and Paste the following VBA Code into the Module, overwriting the existing Code Lines, if any:

Option Compare Database
Option Explicit

Dim lvwList As MSComctlLib.ListView
Dim strTable As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

Private Sub Form_Load()
    Set lvwList = Me.ListView1.Object

End Sub


Private Sub Form_Unload(Cancel As Integer)
On Error GoTo Form_Unload_Err
Dim lvItem As ListItem
Dim tmp As Long
Dim criteria As String
Dim strfield As String
Dim flag As Boolean
Dim fld As String

If strTable = "" Then
Set lvwList = Nothing
    Exit Sub
End If

Set db = CurrentDb
Set rst = db.OpenRecordset(strTable, dbOpenDynaset)
flag = False
For Each lvItem In lvwList.ListItems
    tmp = lvItem.Index
    strfield = lvwList.ColumnHeaders(1).Text
    criteria = strfield & " = " & Chr(34) & lvItem.Text & Chr(34)
    
    rst.FindFirst criteria
  
    If Not rst.NoMatch Then
       If (rst.Fields(strfield).Value = lvItem.Text) _
       And (rst.Fields(1).Value = tmp) Then
         'GoTo nextitem
       Else
            rst.Edit
            rst.Fields(1).Value = tmp
            rst.Update
       End If
    Else
        MsgBox "Item: " & tmp & " Not Found!"
    End If
Next
rst.Close

Set lvwList = Nothing
Set lvItem = Nothing
Set rst = Nothing
Set db = Nothing

Form_Unload_Exit:
Exit Sub

Form_Unload_Err:
MsgBox Err & " : " & Err.Description, , "Form_Unload()"
Resume Form_Unload_Exit

End Sub

Private Sub ListView1_ColumnClick(ByVal ColumnHeader As Object)
' When a ColumnHeader object is clicked, the ListView control
' sorts the data of that column. On the first Click on the Column
'will sort in Ascending Order, second Click will sort in Descending
With Me.ListView1
    ' Set the SortKey to the Index of the ColumnHeader - 1
    .SortKey = ColumnHeader.Index - 1
    
' Set Sorted to True to sort the list.
 If .SortOrder = lvwAscending Then
    .SortOrder = lvwDescending
 Else
    .SortOrder = lvwAscending
 End If
 
    .Sorted = True
End With

End Sub

Private Sub List0_Click()

strTable = List0.Value

Call LoadListView(strTable)

End Sub

Private Sub LoadListView(ByVal s_Datasource As String)
On Error GoTo LoadListView_Err
    Dim j As Integer
    Dim tmpLItem As MSComctlLib.ListItem
    Dim strHeading As String
    
    strHeading = UCase(s_Datasource)
    With Me.Heading
        .caption = strHeading
        .FontName = "Courier New"
        .FontSize = 20
        .FontItalic = True
        .FontBold = True
    End With
    
   'Initialize ListView Control
    lvwList.ColumnHeaders.Clear
    lvwList.ListItems.Clear
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset(s_Datasource, dbOpenSnapshot)
       
    'Initialize ListView & Column Headers Property Values
     With lvwList
        .Font.Size = 10
        .Font.Name = "Verdana"
        .Font.Bold = False
        .GridLines = True
    End With
    
    With lvwList
        'Syntax: .ColumnHeaders.Add Index, Key, Text, Width in Pixels, Alignment, Icon
       For j = 0 To rst.Fields.Count - 1
        .ColumnHeaders.Add , , rst.Fields(j).Name, IIf(j = 0, 3000, 1400), 0
       Next
    End With
   Dim I As Long
    rst.MoveFirst
    Do While Not rst.BOF And Not rst.EOF
    'Syntax: lvwList.ListItems.Add Index, Key, Text, Icon, SmallIcon
        Set tmpLItem = lvwList.ListItems.Add(, , rst.Fields(0).Value) 'Name column
        
         'Syntax: tmpLItem.ListSubItems.Add Index, Key, Text, ReportIcon, ToolTipText
          With tmpLItem
                For j = 1 To rst.Fields.Count - 1
                    .ListSubItems.Add , , Nz(rst.Fields(j).Value, "")
                Next
          End With
        rst.MoveNext
    Loop
    rst.Close
    
    With lvwList
        If .ListItems.Count > 0 Then
            .ListItems(1).Selected = True
        End If
    End With
  
    Set db = Nothing
    Set rst = Nothing
    
LoadListView_Exit:
Exit Sub

LoadListView_Err:
MsgBox Err & " : " & Err.Description, , "LoadListView()"
Resume LoadListView_Exit
End Sub


Private Sub ListView1_OLEDragOver(data As Object, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single, State As Integer)
    'Highlight the item when draged over it
    Set ListView1.DropHighlight = ListView1.HitTest(x, y)

End Sub

Private Sub ListView1_OLEDragDrop(data As Object, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)

'Item being dropped
Dim lvwDrag As ListItem
'Item being dropped on
Dim lvwDrop As ListItem
'Item being readded to the list
Dim lvwTarget As ListItem
'Subitem reference in dropped item
Dim lvwSub As ListSubItem
'Drop position
Dim intTgtIndex As Integer
Dim j As Integer

Set lvwDrop = lvwList.HitTest(x, y)
Set lvwDrag = lvwList.SelectedItem 'save a copy of draged item

'Ignore overlapping drag or drop Item actions
If (lvwDrop Is Nothing) Or (lvwDrag Is Nothing) Or (lvwDrop = lvwDrag) Then
    Set lvwList.DropHighlight = Nothing
    Set lvwDrop = Nothing
    Set lvwDrag = Nothing
    Exit Sub
End If

'Save the droped position Index Number
intTgtIndex = lvwDrop.Index
'Remove Dragged Item from its old position
lvwList.ListItems.Remove lvwDrag.Index

'For j = intTgtIndex To ListItems.Count
    
'Creates a new Item in the Target Item position
'with the Dropped Item Index Number and Dragged Item.Text.
'Saves the new Item reference in lvwTarget Item.

'* The original Droped-on Target) Item will be moved down
'* by incrementing its original Index Number
Set lvwTarget = lvwList.ListItems.Add(intTgtIndex, , lvwDrag.Text)

'Copy the original Draged Item's subitems to the new item
If lvwDrag.ListSubItems.Count > 0 Then
    For Each lvwSub In lvwDrag.ListSubItems
        lvwTarget.ListSubItems.Add , lvwSub.Key, lvwSub.Text
    Next
End If

'Highlight the draged item in its new position
lvwTarget.Selected = True

'Destroy all objects
Set lvwTarget = Nothing
Set lvwDrag = Nothing
Set lvwDrop = Nothing
Set lvwList.DropHighlight = Nothing

End Sub

Private Sub cmdClose_Click()
    DoCmd.Close acForm, Me.Name
End Sub

You are familiar with the above VBA Code, except the newly added subroutines: ListView1_OLEDragOver(), ListView1_OLEDragDrop(), Form_Unload(), and the ListView1_ColumnClik() procedures. The first two procedures will help us drag an Item (row) and Drop it over another Item to insert it into a new location. The procedures Form_Unload() and ListView1_ColumnClick() will sort the Items.

The following Images show the Drag and Drop action sequences of its execution

The first image below shows the drag-and-drop action sequence. The ListItem, with EmployeeID 7, is dragged up by the user and drops over the ListItem with ID 3.

The second image shows the move of ListItem in reverse order.

When the mouse pointer moves over a row with the dragged item, between the source and target rows, will highlight one after the other on the way up.

The Drag and Drop Action in Pictures.

The row with Employee ID 7 is dropped on the Item, with Employee ID 3 above.

The VBA Code Segment-wise Analysis.

In an item selected from the ListBox, the List0_Click() event procedure runs and loads the records in the ListView Control.

Private Sub List0_Click()
Dim strTable As String

strTable = List0.Value

  Call LoadListView(strTable)

End Sub

The selected Table/Query name is saved in the strTable string variable. The LoadListView() subroutine runs with the strTable variable as the parameter. We have gone through this Code more than once in earlier sessions, and you may visit those Pages using the Links provided at the bottom of this page for details. You may find a few minor changes I have made in this Code.

We have not used the ImageList Control in this episode the Icon, SmallIcon Parameter values in the ListItems.Add() Method and ReportIcon, TooltipText parameter values in the ListSubItems.Add() method also not used.

Let us look at what is happening in the ListView1_OLEDragOver() and ListView1_OLEDragDrop() VBA Code Segments.

The ListView1_OLEDragOver() Procedure.

Private Sub ListView1_OLEDragOver(Data As Object, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single, State As Integer)
    'Highlight the item when draged over it
    Set ListView1.DropHighlight = ListView1.HitTest(x, y)
End Sub

This procedure automatically executes when you attempt to click and hold on a row, start to drag, and move over other rows on the way towards the target row. The drag action moves over another row it will get highlighted.

The ListView1.HitTest(x, y) function reads the x, y coordinates that determines the row position on the ListView Control and highlights that row. This process continues when you are over other rows till you drop it on the target row by releasing the mouse button. The drop action triggers the ListView1_OLEDragDrop() procedure and executes the source row's changeover procedures.

The ListView1_OLEDragDrop Procedure.

Private Sub ListView1_OLEDragDrop(Data As Object, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)

'Item being dragged
Dim lvwDrag As ListItem
'Item being dropped on
Dim lvwDrop As ListItem
'Item being added to the list
Dim lvwTarget As ListItem
'Subitem reference used in For . . .Next loop
Dim lvwSub As ListSubItem
'Drop position index
Dim intTgtIndex As Integer

Set lvwDrop = lvwList.HitTest(x, y) 'save the source item
Set lvwDrag = lvwList.SelectedItem 'save a copy of draged item

'Ignore overlapping drag or drop Item actions
If (lvwDrop Is Nothing) Or (lvwDrag Is Nothing)  Or (lvwDrop = lvwDrag) Then
    Set lvwList.DropHighlight = Nothing
    Set lvwDrop = Nothing
    Set lvwDrag = Nothing
    Exit Sub
End If

'Save the droped position Index Number
intTgtIndex = lvwDrop.Index

'Remove Dragged Item from its old position
lvwList.ListItems.Remove lvwDrag.Index

'Creates a new Item in the Target Item position
'with the Dropped Item Index Number and Dragged Item.Text.
'Saves the new Item reference in lvwTarget Item.

'* The original Droped-on Target) Item will be moved down
'* by incrementing its original Index Number
Set lvwTarget = lvwList.ListItems.Add(intTgtIndex, , lvwDrag.Text)

'Copy the original Draged Item's subitems to the new item
If lvwDrag.ListSubItems.Count > 0 Then
    For Each lvwSub In lvwDrag.ListSubItems
        lvwTarget.ListSubItems.Add , lvwSub.Key, lvwSub.Text
    Next
End If

'Highlight the draged item in its new position
lvwTarget.Selected = True

'Destroy all objects
Set lvwTarget = Nothing
Set lvwDrag = Nothing
Set lvwDrop = Nothing
Set lvwList.DropHighlight = Nothing

End Sub

Let us review this procedure part by part and understand what is happening there. The following Code Segment declares necessary Object Variables to handle the Drag and Drop action:

'Item being dragged
Dim lvwDrag As ListItem
'Item being dropped on
Dim lvwDrop As ListItem
'Reference of the Item being added to the list
Dim lvwTarget As ListItem
'Subitem reference used in For . . .Next loop
Dim lvwSub As ListSubItem
'Drop position index
Dim intTgtIndex As Integer

Set lvwDrop = lvwList.HitTest(x, y)
Set lvwDrag = lvwList.SelectedItem 'save a copy of draged item

The first three ListItem temporary Objects declares with different names.

The lvwDrag ListItem object will hold the copy of the row we pick to drag to a new location.

The lvwDrop ListItem Object will save the reference of the row on which we drop the dragged list item.

During the changeover of the ListItems action, we will delete the Source item from its original location, then creates it in the target location, with the source ListItem Index number. The references of this new ListItem are saved in the lvwTarget ListItem object variable.

The lvwSub Variable is declared as a sequencing Object Variable in the For . . .Next Loop. This looping requires to sequence through the ListSubItems, (2nd Column onwards) one-by-one, from the lvwDrag object. Even though we have deleted the original ListItem we have saved a copy of it in the lvwDrag ListItem object.

The lvwDrop ListItem Index number is saved in the intTgtIndex Variable.

The lvwList.HitTest(x, y) Function reads the x, y coordinates of the ListView Control and identifies the target ListItem where we have dropped the source ListItem and makes a copy of it in lvwDrop Object.

We will select a ListItem first before we drag it to the new position.

The lvwList.SelectedItem Property will be set as True. With the help of this property status, we make a copy of the selected ListItem into the lvwDrag ListItem Object. The next Code segment validates both Source and Target ListItem Objects.

Validation Checks on the Drag-Drop Action.

'Ignore overlapping drag or drop Item actions, 
'OR drag and drop happens on the same ListItem.
If (lvwDrop Is Nothing) Or (lvwDrag Is Nothing)  Or (lvwDrop = lvwDrag) Then
    Set lvwList.DropHighlight = Nothing
    Set lvwDrop = Nothing
    Set lvwDrag = Nothing
    Exit Sub
End If

The above code segment validates the drag and dropping action. If these actions did not start or end on a valid item, then the lvwDrop or lvwDrag Objects or both of them will be empty. Or another invalid move can happen as the user moves any row but may change his mind and drop it back in the same row. Detection of these kinds of wrong moves will terminate the program.

If the above test proves valid then the program will continue executing the next procedure to rearrange the Rows.

'Save the dropped position ListItem Index Number
intTgtIndex = lvwDrop.Index

'Remove Dragged Item from its old position
lvwList.ListItems.Remove lvwDrag.Index

'Creates a new Item in the Target Item position
'with the Dropped Item Index Number and Dragged Item.Text.
'Saves the new Item reference in lvwTarget Item.

'* The original Droped-on Target) Item will be moved down
'* by incrementing its original Index Number
Set lvwTarget = lvwList.ListItems.Add(intTgtIndex, , lvwDrag.Text)

'Copy the original Draged Item's subitems to the new item
If lvwDrag.ListSubItems.Count > 0 Then
    For Each lvwSub In lvwDrag.ListSubItems
        lvwTarget.ListSubItems.Add , lvwSub.Key, lvwSub.Text
    Next
End If

'Highlight the draged item in its new position
lvwTarget.Selected = True

The above nine lines of executable Code (other lines are comments) actions are somewhat straightforward.

The intTgtIndex = lvwDrop.Index statement saves the target ListItem's Index number in intTgtIndex Variable.

Since we have already saved the Source Row Listitem in the temporary Object lvwDrag, the next step is to remove the source ListItem from the ListView Control. The ListItems.Remove() procedure is called, with the statement lvwList.ListItems.Remove lvwDrag.Index.

In short, the Drag Drop action is to delete a ListItem from its original location and create it again at the target location with the target row index number.

The statement Set lvwTarget = lvwList.ListItems.Add(intTgtIndex, , lvwDrag.Text) creates the new ListItem with its target location index number intTgtIndex and the Text value of Source ListItem saved in the lvwDrag Object earlier.

While creating the ListItem for the first time, we used only these two values, the index, and the Text parameter values. We have not used the other parameter options Key, Icon, and SmallIcon otherwise we must include those parameter values also from the lvwDrag object.

As per our Drag Drop example Images shown above, we have moved the 7th ListItem and dropped it on the 3rd ListItem. After that, we deleted the 7th Item (or source ListItem) from the ListView Control. Created a new ListItem with the target index number 3.

Now, there are two items with the same Index number 3, the existing one with index number 3 and the new one we have created with index number 3. All other information is taken from the lvwDrag Object (or the 7th ListItem saved in lvwDrag Object earlier).

The System automatically increments the existing ListItem 3 onwards to the next sequence numbers 3,4,5 . . . to 4,5,6 . . ., and moves them forward to give space for the incoming item to insert in between.

The Impact of Delete a Row and Create it elsewhere.

Assume that we make that move in the reverse order, like drag ListItem number 3 from above and drop it on item number 7 then what happens?

Naturally, we will delete the 3rd Item and will attempt to create a new item with index number 7 in the new location. When Item number 3 is deleted item number 4 onwards will shift up or 4,5,6,7,8,9 will become 3,4,5,6,7,8 (to make all the items in sequence) or the earlier item with index number 7 will become 6.

When we create the new Item with index number 7 the existing 7,8 will become 8,9 again. When we watch the movement of rows while row deletion and creation time the first example will move the target row down to make way for the incoming item. In the second example explained (moving 3 to 7) the destination row will move up.

Note: Watch the Employees ID value for its placing as a clue for ListItem shifting down or moving up when we rearrange ListItem.

I have mentioned ListItem everywhere in drag-drop operations. The ListItem refers to the first Column of the ListView Row only. Other column values are ListSubItems or the child-items of the ListItem. That means you will be able to Drag and Drop the first Column only. Other Columns or ListSubItems will be moved under the ListItem with VBA Code.

This is true if you have not enabled the FullRowSelection on the ListView Control Property Sheet on the General Tab.

If enabled, you can select any column, but the System refers to the ListItem Index for Rows re-order purposes. Compare the above two images with another set of two sample images, the third and fourth images from the top of this page.

The Drag and Drop action will not work if the following two property values are not Set on the ListView Control Property Sheet on the General Tab.:

ccOLEDragAutomatic = 1
ccOLEDropManual = 1

The next five statements will move the ListSubItems, if any, to the ListItem newly created in the new location.

Next, the newly created ListItem is highlighted.

Next, all temporary objects created are cleared from memory.

Note: Another important point to note here is that this arrangement is a temporary one and is lost when you close the Form or load another Table/Query on the ListView Control.

If we want the changed order of ListItems to remain permanently, or until the order is changed next time, then we must be able to update the current indexed order number on the table itself. We have added a new Integer field with the field-name ID of the Employees Table.

The sample screen with the Employee data rearranged in Alphabetical order is given below:

Since the Employees ID field is an AutoNumber field and linked with other related tables we have added a new Number Field with the field name ID. This field value is set initially with the same sequence numbers from the Employees ID manually. This field value will be initially in this order. But, the ListView Rows data may change their order when you rearrange the data on the ListView Control due to Drag and Drop action.

Look at the EmployeesQ Query SQL given below:

SELECT [FirstName] & " " & [LastName] AS EmployeeName, 
Employees.ID, 
Employees.EmployeeID, 
Employees.TitleOfCourtesy, 
Employees.Title, 
Employees.Address, 
Employees.City, 
Employees.Region, 
Employees.PostalCode, 
Employees.Country, 
Employees.HomePhone, 
Employees.Extension, 
Employees.Notes
FROM Employees
ORDER BY Employees.ID;

The above Query is used as Data Source for the ListView Control and they are sorted in the ID Field. The ID field is updated with the changed order of Index Numbers on the ListView Control. The updating process runs from the Form_Unload() Event Procedure when you close the Form. This method ensures that when you open the ListView Control next time the data will be in the order you reordered last time.

The Form_Unload() Event Procedure VBA Code.

Private Sub Form_Unload(Cancel As Integer)
Dim lvItem As ListItem
Dim tmp As Long
Dim criteria As String
Dim strfield As String
Dim fld As String

If strTable = "" Then
Set lvwList = Nothing
    Exit Sub
End If

Set db = CurrentDb
Set rst = db.OpenRecordset(strTable, dbOpenDynaset)

For Each lvItem In lvwList.ListItems
    tmp = lvItem.Index
    strfield = lvwList.ColumnHeaders(1).Text 'EmployeeName
    criteria = strfield & " = " & Chr(34) & lvItem.Text & Chr(34)
    
    rst.FindFirst criteria
  
    If Not rst.NoMatch Then
       If (rst.Fields(strfield).Value = lvItem.Text) And (rst.Fields(1).Value = tmp) Then
         'GoTo nextitem
       Else
            rst.Edit
            rst.Fields(1).Value = tmp 'replace ID number
            rst.Update
       End If
    Else
        MsgBox "Item: " & tmp & " Not Found!"
    End If
Next
rst.Close

Set lvwList = Nothing
Set lvItem = Nothing
Set rst = Nothing
Set db = Nothing

End Sub

Check the EmployeeName Field Value in the above image. They are arranged in Alphabetical order. The new ID field value on the Employees Table will be updated with their current ListView Control ListItem index number sequence.

If you note the following points you can easily understand what we do with the above code:

The ListItem's (first column) Text parameter value is the employee name and is arranged in Alphabetical Order.
The ListItems in the ListView Control have index numbers from 1 to 9 in the order it is shown on the screen, i.e., the first item's index number is 1 and the last one is 9. The original data on the Employees Table ID field value is not in this order.
We take the Text Value (Employee Name) of the first ListItem and search for the name on the table.
When the record is found, the current ListItem's Index number is updated (replaced) on the ID field on the table.
This process was repeated for all the remaining records on the table.

Let us review the VBA Code. In the beginning, we check whether the Source data Table/Query was loaded into the ListView Control or not?

If the strTable Variable is not initialized with the Query name, then the ListView Control is empty. If this is the case, then the user opened the Form and closed it without selecting the Query name to load the data into the ListView control. The Form_Unload Event Procedure is aborted at this point and closes the form.

If the ListView control has data, then the next step is executed and opens the Source data query EmployeesQ to update.

The next step is to go through each ListItem and update the index number in the ID field of the Employees record.

First, the current row index number is saved in the tmp Variable.

The first lvwList.ColumnHeader name EmployeeName and the employee's name is taken from the ListItem.Text into an expression in the Criteria string variable, like EmployeeName = "Andrew Fuller".

The rst.FindFirst Criteria command searches the Source data table to find the record with the given name. When the record is found, the current ListItem Index number is updated in the ID Field.

This process is repeated for all the rows in the ListView Control and when finished the Form is closed.

Next time you load the records from this Query into the ListView Control they will be displayed in the same order when you closed the form last time.

Note: The Query became necessary here to sort the data on the ID field and display them in the changed order on the ListView Control.

All this work was for saving the data in the last sorted order so that the next time you open the Form the data on the ListView Control will be in that order.

Windows Explorer Like Sorting Method.

In Windows Explorer, the displayed list can be sorted in Ascending or Descending Order, by clicking on any Column Heading. The Column Header will work like a Toggle Button. Repeated clicks on the Column Header will Sort the column data in Ascending/Descending Order by the following ListView1_ColumnClick() Event Procedure:

Private Sub ListView1_ColumnClick(ByVal ColumnHeader As Object)
' When a ColumnHeader object is clicked, the ListView control is
' sorted by the subitems of that column.

With Me.ListView1
' Set the SortKey to the Index of the ColumnHeader - 1
    .SortKey = ColumnHeader.Index - 1
    
 If .SortOrder = lvwAscending Then
    .SortOrder = lvwDescending
 Else
    .SortOrder = lvwAscending
 End If
 ' Set Sorted to True to sort the list.
     .Sorted = True
End With
End Sub

Note: The Sorting of all data is in text compared mode only. The ListItems and ListSubItems Add() method's third Parameter, the displayed information on the ListView Control is Text type. Date and Numeric Values are all treated as Text only.

Windows Explorer saves the last sorted order of items in the folder. When we open that folder again the list will be displayed in the earlier sorted order.

With the Form_Unload() Event Procedure this feature of Windows Explorer becomes possible on the Employees Table. When you close the Form after sorting on any column that indexed order sequence will be saved in the Employees Table in the ID field. The Query EmployeesQ always sorts the data on the ID field setting when opened.

The Demo database is attached for Downloading. There are two demo forms in the Database. The first Form demonstrates the opening of Tables and Queries in the ListView Control to view the data in Datasheet View. The second form uses only the EmployeesQ Query alone for Drag, Drop, Sort, and saving of the last sort order of data for future use.

Download ListView Dag-Drop.zip