Sorting the value list in a listbox

[LARiot]

Hi. Thanks for your help.

How do I sort the value list in a listbox? It seems there should be a sort property but there isn't.

Thanks

-Nima

 

[Andrzejek]

Where do you get the values from?
If from the data base, you can sort it when you request the data.

Have fun.

---- Andy

 

[LARiot]

That's why I wrote value list, as in not from a table. The items are being added using the additem method.

-Nima

 

[MajP]

you can find several examples on the internet

http://www.ozgrid.com/forum/showthread.php?t=71509

I know in .net and think in vb6 there is a sort property, but I think there is not in an MSFORMS.

 

[TheAceMan1]

How are ya LARiot . . .

It would be better to setup an [blue]independent table[/blue] (no relationships) to peofrm this. This way you could setup a query to sort as necesary and use that for the [blue]RowSource[/blue] of the combo/list box ...

I say this because thru query/SQL sorting becomes easy no matter how many fields are involved in the sort order.

The code to sort on additional fields/controls > 1 ... increases the complexity of sort ... handled directly in SQL. So a query with sort parameters is your best bet ... why sort thru code when ... access can do it for you automatically!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[LARiot]

Hey Aceman, it's been a while.

The point was to do it without a table. If I were going to do that I'd just create a virtual recordset and sort that (to avoid creating a table).

There's gotta be a simple way. Maybe some simple array function or something similar?

Bizarre that Access listboxes (at least for version 2003) don't have a sort property. One of my first vb (not access) programs used a list box to sort as a poor man's sort function (instead of creating code to do it). You'd think if it's in VB it's in VBA... oh well.

Any help would be appreciated.

-Nima
"Pluralitas non est ponenda sine necessitate", i.e., "Plurality is not to be posited without necessity" aka K.I.S.S. (Keep It Short and Simple) -- Ockham's Razor

 

[TheAceMan1]

LARiot . . .

Roger That ... and sorry to get back so late. The following uses the [blue]bubble sort[/blue] method to sort the value list. Note [blue]you[/blue] substitute the proper name in [purple]purple[/purple]:

[blue]Dim prp As Property, Ary, hld As String, Pak As String
Dim x As Integer, y As Integer
   
   Set prp = Me.[purple][B][I]ListBoxName[/I][/B][/purple].Properties("RowSource")
   Ary = Split(prp, ";")
   
   For x = LBound(Ary) To UBound(Ary) - 1
      For y = x + 1 To UBound(Ary)
         If Ary(y) < Ary(x) Then
            hld = Ary(x)
            Ary(x) = Ary(y)
            Ary(y) = hld
         End If
      Next
   Next

   For x = LBound(Ary) To UBound(Ary)
      If Pak <> "" Then
         Pak = Pak & ";" & Ary(x)
      Else
         Pak = Ary(x)
      End If
   Next

   prp = Pak
   
   Set prp = Nothing[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

Same idea without arrays. It is a little more complicated with an access listbox versus other listboxes because the list item is read only. So to change the value you have to remove then add back in.

Public Sub SortList(lst As Access.ListBox)
  Dim strTemp As String
  Dim i As Integer
  Dim j As Integer
  For i = 0 To lst.ListCount - 1
    For j = i + 1 To lst.ListCount - 1
      If lst.ItemData(i) > lst.ItemData(j) Then
        strTemp = lst.ItemData(i)
        lst.RemoveItem (i)
        lst.AddItem lst.ItemData(j - 1), i
        lst.RemoveItem (j)
        lst.AddItem strTemp, j - 1
       End If
     Next j
   Next i
End Sub

 

[LARiot]

Just to add some code refractoring to this. Are there better variable names for i and j?

-Thanks

-Nima
"Pluralitas non est ponenda sine necessitate", i.e., "Plurality is not to be posited without necessity" aka K.I.S.S. (Keep It Short and Simple) -- Ockham's Razor

 

[LARiot]

This isn't working I'm getting:

Runtime error 424: Object Required

when SortList (Me.lstLetters) executes.

I thought it might be because the listbox hadn't loaded yet however same error after it was moved from the form load event to the form open event. Same error it was moved to a command button.

Any help would be appreciated.




-Nima
"Pluralitas non est ponenda sine necessitate", i.e., "Plurality is not to be posited without necessity" aka K.I.S.S. (Keep It Short and Simple) -- Ockham's Razor

 

[LARiot]

Apparently it needs an exact reference.

SortList (Forms!frmMain.lstLetters)

made it worked.

-Nima
"Pluralitas non est ponenda sine necessitate", i.e., "Plurality is not to be posited without necessity" aka K.I.S.S. (Keep It Short and Simple) -- Ockham's Razor

 

[MajP]

Apparently it needs an exact reference.

No. It needs to be passed a reference to a listbox object. So if the code is called from the form on which the listbox is located then Me.lstletters is equivalent to Forms!frmMain.lstletters. If not called from the form on which the listbox is located then me.lstletters is improper syntax and will not return a reference to a listbox.

 

[LARiot]

The function is called from the form, but the function resides in a Module (outside of the form). I would've thought the me.lstLetters would pass an absolute address (since it's being sent from the form that contains the listbox). As long as it works now.

-Nima
"Pluralitas non est ponenda sine necessitate", i.e., "Plurality is not to be posited without necessity" aka K.I.S.S. (Keep It Short and Simple) -- Ockham's Razor

 

[LARiot]

Thanks Majp. It's working now. Better than the array which while I was coding was thinking, why can't I just eliminate one step and use the listbox as the destination, sorted array.

-Nima
"Pluralitas non est ponenda sine necessitate", i.e., "Plurality is not to be posited without necessity" aka K.I.S.S. (Keep It Short and Simple) -- Ockham's Razor