how to change a sorting in a combo box

[rsfnoam]

Hello there
i have a field that is a combo box.I need to display the list in two different ways depending on the user's choice(that is how the user is searching along the list).
the combo's name: mailinglist
the combo box(mailinglist) is based on a table: tbl_mailinglist
It displays two columns from the table:1-centercod 2-centername
by default it is sorted by column 1(centercod)
I created a button on the form that when the user presses that button it will change the combo box's sorting display and the user can choose from the list that is now sorted by the center name and put the user's selection in another field in the form..
can someone help please? if there is a sample that will be great
thanx alot in advance
Noam

 

[pbaldy]

Set the combo's source:

Me.ComboName.RowSource = "SELECT...ORDER BY..."

 

[John Big Booty]

You would need some code like the following to change the Row Source of your Combo Box;

Select Case Me.FrameName

Case = 1
Me.mailinglist.RowSource = SELECT [tbl_mailinglist].[centercod], [tbl_mailinglist].[centername] FROM [tbl_mailinglist] ORDER BY [centercod];

Case = 2
Me.mailinglist.RowSource = SELECT [tbl_mailinglist].[centercod], [tbl_mailinglist].[centername] FROM [tbl_mailinglist] ORDER BY [centername];

End Select

This code could go behind the On Click event of and Option Group for example.

 

[John Big Booty]

Beaten by a clear margin this morning Paul

 

[vbaInet]

Beaten by a clear margin this morning Paul

Paul must have had his coffee

By the way, if it's a straightforward swap, one can do:

Me.mailinglist.RowSource = "SELECT [centercod], [centername] " & _
                           "FROM [tbl_mailinglist] " & _
                           "ORDER BY " & Choose(Me.[COLOR=Red]FrameName[/COLOR], "[centercod]", "[centername]") & ";"
Me.mailinglist.Requery

 

[rsfnoam]

Hi paul and everyone
Thanx alot for the advice.
Hope it will work..I'll see on Sunday next week when the users will re-use that application

Noam

 

[vbaInet]

Hi paul and everyone
Thanx alot for the advice.
Hope it will work..I'll see on Sunday next week when the users will re-use that application

Noam

Hmm... I thought you would have tested it first before rolling it out

 

[rsfnoam]

Hi paul and others
Just as I thought.. did work but now i got a problem with a user. (darn users I got)
well the problem is that the user does not like the button that Icreated to choose between the two sorts..she wants to change the sort from within the combo by pressing a key or somt'n.:-(
is that possible to change a sorting withing a combo?please if there is a way or sample that I can see how to do that I'll thank very very much in advance
VBAInet - it's not a direct swap but it's a user's decision or I am wrong..
Thanx alot in advance
Noam

 

[John Big Booty]

Have a look at the Key Down event.

 

[vbaInet]

VBAInet - it's not a direct swap but it's a user's decision or I am wrong..

You are sorting by two fields, the Choose() function at the bottom takes care of that. Read up on it to find out more.