Sorting a Combobox RowSource derived from a Value List

[missinglinq]

Does anyone know how to sort the items in a combobox when its RowSource is a Value List? AddItem is only available for free-standing (non-CommandBar) comboboxes in Acc2003 and beyond. I working on a hack to cover this function for earlier versions, and have it working. I'm just looking for a way to re-order the amended list.

Thanks!

Linq

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[sozzer]

Why not just store the entries in a table and use ORDER BY to sort?

 

[missinglinq]

That's the obvious answer, and the one I originally advanced, but the person requesting this has reasons, they say, for not doing it this way!

Thanks for your time!

Linq

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[ZOR]

I had a look round but everything pointed to single field table/query. Dont suppose you can loop list into an array, clear the combo, and replace using additem after sorting array. If you find a magic way it would be good to know how you did it. Regards

 

[missinglinq]

Thanks for responding, Zor! This was just a part of a hack to replace the AddItem function, because ACC2002 and below doesn't have this available for free-standing comboboxes, unlike straight VB, but only for CBOs that are part of the CommandBar! Strange, I know!

Using the array was something I was looking at, but not having any luck at it. Not really sure exactly how Access stores the ValueList as a RowSource. Been playting around with Split() and Join().

Have a great weekend!

Linq

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[Remou]

Here are a few notes for a single column value list.

Private Sub cmdSort_Click()
Me.List0.RowSource = SortValueList(Me.List0.RowSource)
End Sub

Function SortValueList(strValueList)
'Modified from [URL unfurl="true"]http://support.microsoft.com/kb/246067[/URL]
  
  Dim X, Y, Z
  Dim strDict
  Dim strList

  strDict = Split(strValueList, ";")
  
  Z = UBound(strDict)

  ' we need more than one item to warrant sorting
  If Z > 1 Then

    ' perform a a shell sort of the string array
    For X = 0 To (Z - 2)
      For Y = X To (Z - 1)
        If StrComp(strDict(X), strDict(Y), vbTextCompare) > 0 Then
            strItem = strDict(X)
            strDict(X) = strDict(Y)
            strDict(Y) = strItem
        End If
      Next
    Next

    For X = 0 To (Z - 1)
      strList = strList & ";" & strDict(X)
    Next

    SortValueList = Mid(strList, 2)
  End If

End Function

 

[missinglinq]

Very close, Remou! I need to look at it and tweek it a little. It's sorting the list, but returning one less item than it starts out with! I've got a family thing the rest of the day, but I'll look closer tomorrow!

Thanks for your help, and hace a great weekend!

Linq

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[missinglinq]

Spoke too soon!

Chnaged:
For X = 0 To (Z - 1)
strList = strList & ";" & strDict(X)
Next

to this:
For X = 0 To Z
strList = strList & ";" & strDict(X)
Next

and it works like a charm!

Thanks again!

Linq

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!