Sorting a Continuous Form by a column in a combobox

[Danick]

I have a continuous form that has a combo box that allows users to pick a list of contacts that are stored in a Contacts table.

The Control Source is a numerical field called "Contact" that is in the main table.
The Row Source is from the Contacts table where the bound column is column 1, but since I made that column width 0 length, the combo box displays the 2nd row which is the LastName of that contact. So far so good.

I would like the user to sort that column in either ASC or DESC.
So made a command button that toggles the sort. It goes like this

Me.OrderByOn = True
If Me.OrderBy = "[Contact] DESC" Then
Me.OrderBy = "[Contact] ASC"
Else
Me.OrderBy = "[Contact] DESC"
End If

The problem is that this sorts the Contact's ID number in the main table and not by the last name that is being displayed in the combo box.

How can I change my code to sort by the second column of my combobox?

Thanks

 

[mikebaldam]

you can use the me.cboNAMEHERE.Column(2) to reefer to a 2nd column
(although i think the count starts at 0 so 2 would be 3rd... but you can check that easily enough)

 

[Danick]

you can use the me.cboNAMEHERE.Column(2) to reefer to a 2nd column
(although i think the count starts at 0 so 2 would be 3rd... but you can check that easily enough)

Thanks for your help

 

[Lateral]

Hi guys

I know that this is a very old post but I have exactly the same issue and can't figure out how to implement the solution...

Would you mind posting exactly what the revised code looks like?

Thanks very much.

Cheers
Greg

 

[mikebaldam]

then do the same... or provide more info

 

[Lateral]

Sorry,

Here is the code that I am using that sorts the actual SupplierID field.

If Me.OrderBy = "[fStocktakeUpdate_05].[Parts.SupplierID]" Then
Me.OrderBy = "[fStocktakeUpdate_05].[Parts.SupplierID] DESC"
Else
Me.OrderBy = "[fStocktakeUpdate_05].[Parts.SupplierID]"
End If
Me.Requery

I can't figure out what the format of the VBA code needs to be in order to sort on Column 1 which is the actual Supplier Name....

Thanks for your help with this.

Greg

 

[mikebaldam]

Are you just looking to set the display order for a dropdown?

You can do something like this for your dropdowns rowsource:

SELECT Table1.Col1, Table1.Col2 FROM Table1 ORDER BY Table1.Col2;

Or via vba

me.DROPDOWNNAME.rowsource = "SELECT Table1.Col1, Table1.Col2 FROM Table1 ORDER BY Table1.Col2;"

(See attachment for an example)