Using Forms with ORDER BY Clause

[ywenz]

Hi, I want to design a forum where the user can select a field they want to order the search result by. So far, I've tried doing:

ORDER BY [forms]![Form1]![Combo1];

Where Combo1 is a drop down list of bunch of valid field names. However, this doesn't seem to do anything. The query gets executed from the form without error but it isn't ordered by the field I selected. Any help would be appreciated.

 

[Rolliee]

The way to do this is to open the properties of the form. Click on the rowsource and then doubleclick on the three dots. I believe that is called an ellipsis. You will be asked if you wish a query view and the answer is yes.

Click on the View of the query and see the SELECT .....

It ends with a ";"

Just before that ";" place the following code;

Order By somefield = [forms]![Form1]![Combo1]

rollie@bwsys.net

 

[ywenz]

Hi, I tried what you said but I doesn't work. I think you might have skipped over a couple of steps. So the Row Source Type for my Combo box should be a query right? But would I be selecting in my query? I already have a query that I want to use. I just need the forum so the user can pick which field he/she wants to ORDER BY.

ORDER BY [forms]![Form1]![Combo1]; is in the existing query I want to useWhat is "somefield" ?? Is that in the code? or is it a place holder for an actual field in my table? When I have that in my Query, a popup box asks for value of "somefield".

Thanks

 

[Rolliee]

In the design mode of the RowSource of your combo box - in the View SQL. Do you get there? It will be like this

SELECT * FROM Table1;

Between the '1' and the ";" put ORDER BY XXXXXXXXX

where XXXXXXXXXX is the name of the field or fields you wish to order by


rollie@bwsys.net

 

[ywenz]

But the field I want to order by is going to vary, based on what the user selects in the Combo Box, so is that were I would put [forms]![Form1]![Combo1] ?? So it'd be like

SELECT * FROM Table1
ORDER BY [forms]![Form1]![Combo1];

Thanks

 

[Rolliee]

Then you need to build a query string based on the combo and plug it into the DataSource of the form. That may be circular but try it.

Otherwise send me a small ZIPPED version of your mdb and I will send it back with changes.

rollie@bwsys.net