ghetto_banjo said:
After snooping around the internets, it appears I have to recreate the
rowsource SQL and change the Order By clause to match the label being
clicked on. That's certianly do-able, but just let me know if there
is any built in method.
No, there's no built-in method. Here's a rudimentary version of a function
to do it:
'----- start of code -----
Function fncOrderListboxBy( _
lstListBox As Access.ListBox, _
strField As String)
Dim strRowSource As String
Dim strOrderBy As String
Dim strOrderField As String
Dim strOrderSeq As String
Dim I As Long
' Find out how the list box is currently ordered,
' if at all.
strRowSource = lstListBox.RowSource
I = InStr(strRowSource, "ORDER BY")
If I = 0 Then
strOrderBy = vbNullString
Else
strOrderBy = Trim(Mid(strRowSource, I + 8))
strRowSource = Left$(strRowSource, I - 1)
End If
If Not strRowSource Like "SELECT *" Then
strRowSource = "SELECT * FROM " & strRowSource
End If
If Len(strOrderBy) = 0 Then
strOrderField = vbNullString
strOrderSeq = vbNullString
Else
If strOrderBy Like "* DESC" Then
strOrderSeq = "DESC"
strOrderField = Left$(strOrderBy, Len(strOrderBy) - 5)
ElseIf strOrderBy Like "* ASC" Then
strOrderSeq = "ASC"
strOrderField = Left$(strOrderBy, Len(strOrderBy) - 4)
Else
strOrderSeq = "ASC"
strOrderField = strOrderBy
End If
End If
Debug.Print "RowSource = '"; strRowSource; "'"
Debug.Print "OrderBy = '"; strOrderBy; "'"
Debug.Print "OrderField = '"; strOrderField; "'"
Debug.Print "OrderSeq = '"; strOrderSeq; "'"
If strField = strOrderField Then
If strOrderSeq = "ASC" Then
strOrderSeq = "DESC"
Else
strOrderSeq = "ASC"
End If
End If
lstListBox.RowSource = _
strRowSource & " ORDER BY " & strField & " " & strOrderSeq
End Function
'----- end of code -----
You could call the function directly from the OnClick property of each
label, using an expression like this:
=fncOrderListboxBy([lstMyListbox],"FieldName")
