Tony_VBACoder said:
On my Access 2002 form, I have the following:
1) Add Button (btnAdd)
2) Remove Button (btnRemove)
3) Text Box (txtValue)
4) List Box (listItems)
The user enters a value in the txtValue TextBox and then
clicks the Add button (btnAdd) to add the value to the
ListBox (listItems). When the user clicks the Add Button,
I am using the .AddItem method to add the item to the
listbox. The user may enter numerous entries. What I
would like to do is sort the list Ascending order after
the .AddItem is executed.
Does anyone have an efficient algorythm for sorting the
listbox since I am not using any SQL for the RowSource of
the listbox?
Oh bleep. I was supposed to put this up on The Access Web
months ago, but lost track of myself. MVP John Spencer
worked through all the questions with this issue by
providing this code. Watch out for news post line wrapping
when you Copy/Paste it into your module.
--
Marsh
MVP [MS Access]
********************************************************
Public Sub sSortListBox(anyListbox As Control, _
Button As Integer, _
Shift As Integer, _
X As Single)
'Purpose: Sort list box by column when column is
right-clicked
'Author: Copyright by John Spencer
'Version Date: 04-14-2004
'Limitations:
' No Horizontal scroll bar in listbox
' RowSource must be query
' Uses DAO code; not tested with ADP
'Permission to use in applications is granted to all
'with the understanding that
'credit is given to the author.
'No warrantee or guaranty is given - use at your own risk.
'
'Code to sort list in ascending/descending order
'depending on which column is right-clicked
'and whether shift key is pressed.
'Uses the SQL syntax of specifying a column number as
'the sort column -
' SELECT ... FROM ... ORDER BY N
'- where N is integer reflecting the position of a field in
SELECT clause.
'Install call to this code in the Mouse Down event of a
listbox.
'Example -
' sSortListBox Me.SomeListbox, Button, Shift, X
'---------------------------------------------------------------------
'---------------------------------------------------------------------
'In the listbox's Mouse Up event add code to cancel the
Mouse up event.
' If Button = acRightButton Then DoCmd.CancelEvent
'That line will stop any popup menu from appearing.
'---------------------------------------------------------------------
'---------------------------------------------------------------------
Dim strSQL As String
Dim vGetWidths As Variant
Dim vArWidths() As Variant
Dim iColCount As Integer, iColNumber As Integer
Dim iLoop As Integer
Dim iColWidthSum As Integer
Dim iUndefined As Integer
Dim iDefaultWidth As Integer
Dim strOrderBy As String
Const strListSeparator As String = ";" 'list Separator
'If your list separator is not a ";"
'you will need to change the ";" to your list separator
On Error GoTo ERROR_sSortListBox
If Button <> acRightButton Then
'only sort based on right button being clicked
ElseIf anyListbox.RowSourceType <> "table/query" Then
'only sort listbox based on queries
MsgBox "List box must use a query as it's row source"
ElseIf Len(anyListbox.RowSource) = 0 Then
'Nothing there, so ignore the click
ElseIf Not (InStr(1, Trim(anyListbox.RowSource),
"Select", vbTextCompare)
= 1 _
Or InStr(1, Trim(anyListbox.RowSource), "Parameters",
vbTextCompare) =
1) Then
'If rowsource does not start with SELECT or PARAMETERS
then
'assume it is a table not a query
MsgBox "List box must use a query as its row source"
ElseIf anyListbox.ColumnCount <> _
DBEngine(0)(0).CreateQueryDef("",
anyListbox.RowSource).Fields.Count
Then
'Column count must be correctly set, otherwise this
routine
'could cause errors. Column count set less than actual
field count
'will cause subscript errors. Column count set higher
than actual
'field count can cause listbox to display nothing if
"Extra" column
'is clicked.
MsgBox "List box column count does not match query
field count!"
Else 'passed the error checks
With anyListbox
'Column count must be correctly set, otherwise this
routine
'could cause errors. Column count set less than
actual column count
'will cause subscript errors. Column count set
higher than actual
'column count can cause listbox to display nothing
if "Extra"
column
'is clicked.
iColCount = .ColumnCount
ReDim vArWidths(iColCount - 1, 0 To 1)
'If you are using Access 97 then you will need a
custom function to
'perform the parsing of the column widths into an
array.
vGetWidths = Split(.ColumnWidths, strListSeparator,
-1,
vbTextCompare)
'Assign values to array that holds length and
running sum of length
For iLoop = 0 To UBound(vGetWidths)
iColWidthSum = iColWidthSum +
Val(vGetWidths(iLoop))
vArWidths(iLoop, 1) = iColWidthSum
vArWidths(iLoop, 0) = vGetWidths(iLoop)
Next iLoop
'Adjust any colwidths that are unspecified:
'The minimum is the larger of 1440
'or the remaining available width of the list box
'divided by number of columns
'with unspecified lengths.
For iLoop = 0 To iColCount - 1
If Len(vArWidths(iLoop, 0) & vbNullString) = 0
Then
iUndefined = iUndefined + 1
End If
Next iLoop
If iUndefined <> 0 Then
iDefaultWidth = (.Width - iColWidthSum) /
iUndefined
End If
If iDefaultWidth > 0 And iDefaultWidth < 1440 Then
MsgBox "Sorry! Can't process listboxes with
horizontal
scrollbars"
Exit Sub 'Horizontal scroll bar present
Else
'recalculate widths and running sum of column
widths
iColWidthSum = 0
For iLoop = 0 To iColCount - 1
If Len(vArWidths(iLoop, 0) & vbNullString) =
0 Then
vArWidths(iLoop, 0) = iDefaultWidth
End If
iColWidthSum = iColWidthSum +
Val(vArWidths(iLoop, 0))
vArWidths(iLoop, 1) = iColWidthSum
Next iLoop
End If
'Set right edge of last column equal to width of
listbox
vArWidths(iColCount - 1, 1) = .Width
'Determine which column was clicked
For iLoop = 0 To iColCount - 1
'If X - .Left <= vArWidths(iLoop, 1) Then
If X <= vArWidths(iLoop, 1) Then
iColNumber = iLoop
Exit For
End If
Next iLoop
iColNumber = iColNumber + 1 'adjust since iLoop
is 0 to n-1
'rebuild sql statement
If iColNumber > 0 And iColNumber <= iColCount Then
strSQL = Trim(.RowSource)
If Right(strSQL, 1) = ";" Then strSQL =
Left(strSQL, Len(strSQL)
- 1)
iLoop = InStr(1, strSQL, "Order by",
vbTextCompare)
If iLoop > 0 Then
strOrderBy = Trim(Mid(strSQL, iLoop +
Len("Order by")))
strSQL = Trim(Left(strSQL, iLoop - 1))
End If
'Build the appropriate ORDER BY clause
If Shift = acShiftMask Then
'If shift key is down force sort to desc on
selected column
strOrderBy = " Order By " & iColNumber & "
Desc"
ElseIf Len(strOrderBy) = 0 Then
'If no prior sort then sort this column
ascending
strOrderBy = " Order by " & iColNumber & "
Asc"
ElseIf InStr(1, strOrderBy, iColNumber & " Asc",
vbTextCompare)
'If already sorted asc on this column then sort
descending
strOrderBy = " Order By " & iColNumber & "
Desc"
ElseIf InStr(1, strOrderBy, iColNumber & "
Desc", vbTextCompare)
'If already sorted desc on this column then sort
Ascending
strOrderBy = " Order By " & iColNumber & "
Asc"
Else
strOrderBy = " Order by " & iColNumber & "
Asc"
End If
strSQL = strSQL & strOrderBy
.RowSource = strSQL
End If 'Rebuild SQL if col number is in range 1
to number of
columns
End With 'current list
End If 'Passed error checks
EXIT_sSortListBox:
Exit Sub
ERROR_sSortListBox:
Select Case Err.Number
Case 9 'Subscript out of range
MsgBox Err.Number & ": " & Err.Description & _
vbCrLf & vbCrLf & "Check column count
property of list
box.", _
vbInformation, "ERROR: sSortListBox"
Case Else 'unexpected error
MsgBox Err.Number & ": " & Err.Description,
vbInformation, _
"ERROR: sSortListBox"
End Select
Resume EXIT_sSortListBox
End Sub
John Spencer (MVP - Microsoft Access)
