Sort data in combo box - unbounded

[Silvio]

I have a code (se below) that will populate a combo box (unbounded) using the
“columns headings†name from a query (qryUsers). The question I have is: how
can I sort (Ascending) the data in the combo box? Right now, it appears that
the sorting order is the same as the columns order (left to right) in the
query. For example, if the first column in the query is called ColumnA, then
follows ColumnC, then ColumnB and so on. With this example, the data in my
combo box shows as:

ColumnA
ColumnC
ColumsB

What I want to see is:

ColumnA
ColumnB
ColumnC

Thank you folks.

------- Code ------ on form open

Dim curDatabase As Object
Dim strColumnsNames As String
Dim qryUsers As Object
Dim fldColumn As Object

Set curDatabase = CurrentDb
Set qryUsers = curDatabase.QueryDefs("qryUsers")
For Each fldColumn In qryUsers.Fields
strColumnsNames = strColumnsNames & fldColumn.Name & ";"
Next
cboColumnNames1.RowSource = strColumnsNames

 

[Rob Parker]

Hi Silvio,

I don't understand why you are using code to populate your unbound
combo-box, when you can simply set its RowSource to qryUsers. Set the
fields in the query in the order in which you want them to appear, and sort
on the field(s) you want to sort by in the query.

HTH,

Rob

 

[Silvio]

Rob, I am using the combo box to sort the data in my form. I don't want to
write a code for each field in my database. I know that if I manually move
the columns around in the query the combo box will sort accordingly however I
am trying to accomplish this programmatically. (See example at the bottom at
http://www.functionx.com/vbaccess/Lesson15c.htm)

 

[Rob Parker]

Hi again Silvio,

The link makes everything clear, and I understand what you're wanting. It
is do-able, but will take a little code. Basically, instead of building the
RowSource sequentially from the field names, you'll need to read them into a
temporary array, sort that array, then build the RowSource string from the
sorted array. I'll play with it when I get time, and post a solution -
unless anyone else does so before then; or you do it yourself from those
basic instructions ;-)

Or, simpler (and what I would do in this situation), arrange the fields in
qryUser in ascending order (left-to-right). That shouldn't cause any
problems elsewhere, since it's not general practice to display raw query
output - that's normally done via either a form or a report, where the
fields in the query can be arranged precisely as you want them; the field
order in the query is irrelevant. The only thing it might upset is if you
have a combobox or listbox based on the query, and are displaying multiple
fields - in that case the display order is tied to the field order in the
query.

Rob

 

[Rob Parker]

Well, it didn't take too long (particularly since I used a quicksort code I
found).

So, here's what your existing Form_Open code will need to be:

Private Sub Form_Open(Cancel As Integer)
Dim curDatabase As Object
Dim strColumnsNames As String
Dim qryUsers As Object
Dim fldColumn As Object
Dim intFieldCount As Integer
Dim i As Integer

Set curDatabase = CurrentDb
Set qryUsers = curDatabase.QueryDefs("qryUsers")

intFieldCount = qryUsers.Fields.Count

' set up array, retrieve the name of each column of the query and
' store each name in the array
ReDim strFields(intFieldCount - 1) As String
For i = 0 To intFieldCount - 1
strFields(i) = qryUsers.Fields(i).Name
Next i

' sort the array
QuickSort strFields, LBound(strFields), UBound(strFields)

' build RowSource for combobox
strColumnsNames = ""
For i = 0 To intFieldCount - 1
strColumnsNames = strColumnsNames & strFields(i) & ";"
Next i

' Set the strColumnsNames string as the data source of the combo box
cboColumnNames.RowSource = strColumnsNames
' Select the name of the first column as the default of the combo box
cboColumnNames = strFields(0)

End Sub

You'll also need two additional subroutines in the code module for your
form, to perform the sort. Just cut/paste from here (watch for line wrap):

Private Sub QuickSort(C() As String, ByVal First As Long, ByVal Last As
Long)
'
' Made by Michael Ciurescu (CVMichael from vbforums.com)
' Original thread: http://www.vbforums.com/showthread.php?t=231925
'
Dim Low As Long, High As Long
Dim MidValue As String

Low = First
High = Last
MidValue = C((First + Last) \ 2)

Do
While C(Low) < MidValue
Low = Low + 1
Wend

While C(High) > MidValue
High = High - 1
Wend

If Low <= High Then
Swap C(Low), C(High)
Low = Low + 1
High = High - 1
End If
Loop While Low <= High

If First < High Then QuickSort C, First, High
If Low < Last Then QuickSort C, Low, Last

End Sub

Private Sub Swap(ByRef A As String, ByRef B As String)
Dim T As String

T = A
A = B
B = T
End Sub

HTH,

Rob

 

[Silvio]

Rob, this is great it works just as expected. Thank you VERY much.