Sort List box

[Sam]

Hi folks,

I have two Listboxes - lbAvailablePersons and lbSelectedPersons. When
form opens lbAvailablePersons is populated using the rowsource from the
listbox's properties, i have there the following sql:

SELECT tblPerson.SupportedPersonID, "" & [surname] & ", " & [firstname]
& " " & [middlename] & "" AS Expr1 FROM tblPerson ORDER BY
tblPerson.SurName;

lbSelectedPerson is populated via pbAddPerson, code is:

Call AddToListBox(ListPersonsAvailable, ListPersonsSelected, strDebug,
strDebugAlt, itm)


..


My problem is that lbSelectedPersons is not sorted.

My question is how do i sort the second listbox?

Thanks for any help or hints!

Sam

 

[Douglas J. Steele]

I'm assuming that lbSelectedPersons is simply using the AddItem method that
was added to the listbox control in Access 2003 (or was it Access 2002). All
that does is append the latest item to the end of the string that's been
defined as the listbox's RowSource property. You could take that string, use
the Split function to break it into an array, sort the array, then reset the
RowSource property. Of course, if you're displaying more than 1 column in
the listbox, that's going to take a bit of coding...

If that's not the case, post the details of what AddToListBox is doing.

 

[Dale Fye]

Sam,

A lot of times, when I need this functionality, I will add an IsSelected
column to tblPerson, or whatever table I am using for that purpose.

Then I set the rowsource of lbAvailablePersons to (the advantage of this is
that once you have selected someone, they are removed from the available
list). You also need to set your listbox so that the bound column is column
#1.

SELECT tblPerson.SupportedPersonID, "" & [surname] & ", " & [firstname] & "
" & [middlename] & "" AS Expr1
FROM tblPerson
WHERE NOT IsSelected
ORDER BY tblPerson.SurName;

Then, I set the rowsource of lbSelectedPersons to:

SELECT tblPerson.SupportedPersonID, "" & [surname] & ", " & [firstname] & "
" & [middlename] & "" AS Expr1
FROM tblPerson
WHERE IsSelected
ORDER BY tblPerson.SurName;

Then, all you have to do in the click event of the listbox is run an update
query to update tblPersons, and then requery the other listbox.

Private Sub lbAvailablePersons_Click()

Dim strSQL As String

strSQL = "UPDATE tblPerson SET IsSelected = -1 WHERE ID = " &
Me.lbAvailablePersons
CurrentDb.Execute strSQL
Me.lbAvailablePersons.Requery
me.lbSelectedPersons.Requery
End Sub

You can also put code similiar to this (set the value to 0) in the
lbSelectedPersons, to unselect the person.

HTH
Dale

 

[Sam]

Thanks Doug and Dale for the replies,

I'll try both of them tonight - always nice to have a variety of
techniques available.

Doug, I have been using the addItem function, and after chatting with a
colleague he suggested the same approach of using an array to sort my
data then repopulate my listbox.

I'll post back tomorrow let you both know how i got on.

thanks again for the help *smile*

Sam

 

[Sam]

All working grand now. thank again guys.

Sam