Listbox contents and sorting

  • Thread starter Thread starter mscertified
  • Start date Start date
M

mscertified

Is there a simple way to make the selections in a listbox 'float' to the top
whilst still keeping the non-selected items sorted alphabetically underneath.
 
What exactly is the point of posting a reply like that?
All it does is confess your ignorance.
 
Is there a simple way to make the selections in a listbox 'float' to the top
whilst still keeping the non-selected items sorted alphabetically underneath.
Click to expand...

It could be done, but it won't be particularly simple! You would need a YesNo
field in the query, Selected; use the listbox's AfterUpdate event to set this
field to True for each selection, and base the listbox on a query sorting
first by the Selected field. You'll need to Requery the listbox after toggling
the Selected field.

John W. Vinson [MVP]
 
Sure, but it would involve some coding and a change to the table structure
of the table that provides the data for your listbox.

1. Add an IsSelected (Yes/No) field to the table
2. Add it to the query that supports your listbox. Mine looks like:

SELECT intValue, SomeLetter, SomeDate, tbl_Number.IsSelected
FROM tbl_Number
ORDER BY tbl_Number.IsSelected, tbl_Number.intValue;

Note that this is sorted first by the IsSelected field, then by the field
you want to use as your primary sort field.

3. Add code similiar to the following to the click event of the listbox:
Basically, what this does is set the IsSelected value in the table to the
Selected property of each of the items in the listbox. It then requeries
the listbox and sets the Selected property of the list item based on the
value of the IsSelected field

If your list contains a lot of info, this might take a while, but with small
lists, it should not be noticable.

Private Sub lst_Numbers_Click()

Dim intLoop As Integer
Dim strSQL As String

For intLoop = 0 To Me.lst_Numbers.ListCount - 1
strSQL = "UPDATE tbl_Number SET IsSelected = " &
Me.lst_Numbers.Selected(intLoop) _
& " WHERE intValue = " & Me.lst_Numbers.Column(0, intLoop)
Debug.Print strSQL
CurrentDb.Execute strSQL
Next

Me.lst_Numbers.Requery

For intLoop = 0 To Me.lst_Numbers.ListCount - 1
Me.lst_Numbers.Selected(intLoop) = Me.lst_Numbers.Column(3, intLoop)
Next

End Sub

HTH
Dale
 
Back
Top