Sort records in the listbox

[sabavno]

hi

I have a list box containing of 5 columns. My first column contains the list of last names. I want to have a button at the top of that column so the user could click on it and sort the last names whether in ascending or discending order.
Please advise me on the ways to achieve this
Thanks

 

[BlackKnight]

Hi,

You can try something like this:

Private Sub cmdSort_Click()

If cmdSort.Caption = "Sort ASC" Then

lstPatients.RowSource = "SELECT PatientID,LastName,FirstName, Sex, Age FROM Patient ORDER BY LastName DESC"

cmdSort.Caption = "Sort DESC"
Else
lstPatients.RowSource = "SELECT PatientID,LastName,FirstName, Sex, Age FROM Patient ORDER BY LastName ASC"

cmdSort.Caption = "Sort ASC"
End If

lstPatients.Requery
End Sub

Have a good one!
BK

 

[BlackKnight]

Hi again,

You could also try it this way. <g> Notice in the form load you would have to ensure the sort caption matched the sort order of the list.

Private Sub cmdSort_Click()
Dim SortOrder As String

SortOrder = Trim$(Right$(lstPatients.RowSource, 6))
'Eliminate semi-colon at end.
SortOrder = Mid$(SortOrder, 1, Len(SortOrder) - 1)
cmdSort.Caption = SortOrder
SortOrder = IIf(SortOrder = &quot;ASC&quot;, &quot;DESC&quot;, &quot;ASC&quot
lstPatients.RowSource = &quot;SELECT PatientID,LastName,FirstName, Sex, Age FROM Patient ORDER BY LastName &quot; & SortOrder & &quot;; &quot;
lstPatients.Requery
End Sub

Private Sub Form_Load()
Dim SortOrder As String

SortOrder = Trim$(Right$(lstPatients.RowSource, 6))
'Eliminate semi-colon at end.
SortOrder = Mid$(SortOrder, 1, Len(SortOrder) - 1)
SortOrder = IIf(SortOrder = &quot;ASC&quot;, &quot;DESC&quot;, &quot;ASC&quot
cmdSort.Caption = SortOrder
End Sub

Have a good one!
BK