> 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
>
> Rob Parker wrote:
> > 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
> >
> >
> > Silvio wrote:
> >> 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" wrote:
> >>
> >>> 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" <Silvio[ at ]discussions.microsoft.com> wrote in message
> >>> news:5EA3BAA4-1D51-4365-9DF1-FABECBB2A882[ at ]microsoft.com...
> >>>> 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
>
>
>