Sort unbonded combo box data

[Silvio]

The code below will populate an unbounded combo box using the columns
headings of a query. The question I have is: how can I sort the data in the
combo box in ascending order? Right now, it appears that the order to follow
the order on the columns in the query from left to right. For example if the
first column in the query is called Column10, Columns3, Column5 and so on,
my combo box will display

Column10
Column3
Colums5

What I want to see is

Column3
Column5
Column10

Thank you folks.


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

' Get a reference to the current database
Set curDatabase = CurrentDb
' Get a reference to a query named qryUsers
Set qryUsers = curDatabase.QueryDefs("qryUsers")
' Retrieve the name of each column of the quary and
' store each name in the strColumnsNames string
For Each fldColumn In qryUsers.Fields
strColumnsNames = strColumnsNames & fldColumn.Name & ";"
Next

' Set the strColumnsNames string as the data source of the combo box
cboColumnNames1.RowSource = strColumnsNames

 

[MikeJohnB]

The Control Source for the unbound Combo Box is a SQL Query. With the form in
design view, select the combo box and then its properties. Select the Row
Source and click on the three dots ... to the right of that property. A Query
will popup in design mode. Drag the columns into the order you want to see
them in. Close and save the query and adjust the size of the columns if
necessary.

I think that will give you the order you want??????

Let me know.

Regards

Mike B

 

[Silvio]

Mike I need to do this programmatically. The Combo Box list is populated by
the colums headings not the actual records.

 

[tkelley via AccessMonster.com]

I think that--even if you can sort ascending like you want--you're still
going to get the same order as your first example because "Column10" is a
text string and won't be sorted like a number (unless you're using "Column10"
just for an example).

Do you have the flexibility to have your code pop the column names into a
table, then query the table however you need to as the rowsource of your
combo box?

Otherwise you may have to look into building them into an array, finding code
to sort the array, then building your string. (also seems like a lot of work)

Or create a disconnected recordset, then as you loop through append to the
recordset, sort the recordset, then build your string ?? (also seems like a
lot of work)

Or there may be a simpler answer that I don't know of ...

 

[Silvio]

Hi, I used column10 just as example. All my columns do not have numbers.

 

[Dirk Goldgar]

The code below will populate an unbounded combo box using the columns
headings of a query. The question I have is: how can I sort the data in
the
combo box in ascending order? Right now, it appears that the order to
follow
the order on the columns in the query from left to right. For example if
the
first column in the query is called Column10, Columns3, Column5 and so
on,
my combo box will display

Column10
Column3
Colums5

What I want to see is

Column3
Column5
Column10

Thank you folks.


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

' Get a reference to the current database
Set curDatabase = CurrentDb
' Get a reference to a query named qryUsers
Set qryUsers = curDatabase.QueryDefs("qryUsers")
' Retrieve the name of each column of the quary and
' store each name in the strColumnsNames string
For Each fldColumn In qryUsers.Fields
strColumnsNames = strColumnsNames & fldColumn.Name & ";"
Next

' Set the strColumnsNames string as the data source of the combo box
cboColumnNames1.RowSource = strColumnsNames

Having read your posts in this and another thread on the same subject, I
think what you want to do may be accomplished using ADO. You'll need to set
a reference to the ADO (ActiveX Data Objects) library. Then your code can
say:

Dim strColumnsNames As String
Dim Conn As ADODB.Connection
Dim rstColumns As ADODB.Recordset

Set Conn = CurrentProject.Connection

Set rstColumns = Conn.OpenSchema( _
adSchemaColumns, Array(Empty, Empty, "qryUsers", Empty))

Do Until rstColumns.EOF
strColumnsNames = strColumnsNames & rstColumns!COLUMN_NAME & ";"
rstColumns.MoveNext
Loop

rstColumns.Close
Set rstColumns = Nothing
Set Conn = Nothing

' Set the strColumnsNames string as the data source of the combo box
cboColumnNames1.RowSource = strColumnsNames

 

[Clifford Bass]

Hi Silvio,

Really basic question: Is there some reason you cannot just order the
fields in your query to match the order you want in the combo box? If you do
that, then you can just set the Row Source to the query and set the Row
Source Type to Field List. No need for any code if you do that?

Clifford Bass