Sort By Clicking on Column Heading 2

[newfrontiers]

Hello. Any suggestions for allowing a user to sort a listbox or combo box data by clicking on any column heading with the control? I am thinking of something exactly like MS Outlook. When a user clicks on the heading once it sorts Ascending and when the user clicks again it sorts Descending.

Thanks,

John

 

[devnull69]

Try to use the common control "ListView". It contains the functionality you need. You will have to set a reference to mscomctl.ocx first (i.e. Microsoft Windows Common Controls 6.0).

 

[mp9]

It isn't very pretty but I use the following code in a MouseDown event to allow me to sort by the column headings in a list box:

Private Sub List0_MouseDown(Button As Integer, Shift As Integer, x As Single, Y As Single)
If Y <= 225 Then ' you might have to change this - it checks if the pointer is over a column heading
    Dim strOrd As String, strSQL As String
    If Button = 1 Then strOrd = &quot; Asc;&quot; Else strOrd = &quot; Desc;&quot; 'Left click to sort asc, right for desc
    strSQL = &quot;SELECT ...your regular SQL here...ORDER BY &quot;
    Select Case x
    Case 0 To 1694 ' width of my first column in pixels
        strSQL = strSQL & &quot;Logged&quot; & strOrd
    Case 1695 To 3389 ' second column
        strSQL = strSQL & &quot;Name&quot; & strOrd
    Case Else 'third column
        strSQL = strSQL & &quot;Issue&quot; & strOrd
    End Select
    List0.RowSource = strSQL
    List0.Requery
End If
End Sub

http://www22.brinkster.com/accessory/

 

[newfrontiers]

Thank you for your help it will save me a lot of time.

Thanks again.

 

[mp9]

Glad to help. I've written a slightly longer article about this method, and have added an alternative that lets you toggle sort-order with repeated left-clicks, at

http://www22.brinkster.com/accessory/forms/004.shtml

http://www22.brinkster.com/accessory/

 

[thromada]

mp9,

This isn't ugly at all. This is the most straight-forward solution to sorting a listbox with an SQL recordsource. Good work and thanks!