Change OrderBy of a Query

[mahmudich]

Hi guys!

I’ve got a little task. I have ListBox with ControlSource = myQuery. At some point I need to change OrderBy property of myQuery, in other words I want the ListBox to be sorted by another column just by pressing a button. Is there a nice way of doing that by, let’s say, VBA.

So, the whole thing would look like:

??? <changing OrderBy property of the myQuery> ???
Me![ListBox].Requery


Thank you

 

[dcx693]

Yes, use VBA and place this code in the click event of the command button to assign the rowsource of the listbox like this:
Me.lstBox.RowSource=strSQL

Constructing the strSQL can be simple or complicated depending on how the rowsource is originally assigned and what it is when the command button is clicked. Typically the rowsource is of the form:

SELECT tblInventory.Sold FROM tblInventory;

We'll call that original rowsource "strSource". First, chop of that last semicolon using the Left() function, then add an ORDER BY statement to the rowsource like this:

strSQL=Left(strSource, Len(strSource)-1) & " ORDER BY " & strSortSQL & ";"

where strSortSQL is your chosen sort field (you can add an "ASC" or "DESC" after the field name to specify sort order). Though Access doesn't require a semicolon at the end of SQL strings, most other backend databases do, so it's a good idea to put one there like I did above.

 

[Sharky II]

i don't really understand the above explanation too well - sorry!

i was searching for formus as i need to do the same thing - i have a lsitbox and i'd like to give the user the option of sorting by TradePrice (both ascending and descending) and RetailPrice (again both asc and desc) but this would use up 4 buttons for just two fields.

But i can't think of any other way.

Anyway, can someone break that above post down for me please!

Cheers

Edd

 

[WayneRyan]

Edd,

The ListBox is just fed by a SQL string:

Select Field1, Field2 From SomeTable Order By Field1 Desc

When the ListBox is queried, the rows and columns from the query
populate the rows and columns for the ListBox. There is an exact
correlation between the physical appearance of the query data and
the ListBox contents.

You can dynamically change the SQL:

Me.MyListBox.RowSource = "Select Field2, Field1 From SomeTable Order By Field2 Asc"
Me.MyListBox.Requery

The appearance of the ListBox is physically changed. Any rows previously
selected are cleared. With new column orders (and new row orders), it
really has no relationship with the previous contents of the ListBox.

As a means of manipulating data, the ListBox is rather cumbersome. You can't
change the data values, you have to "invent" new controls for field order
and sorting, etc.

A better means of displaying data would be to use a read-only subform, and
use Access's tools for sorting and niceties such as Conditional Formatting.

Wayne

 

[Sharky II]

Thanks for yet another detailed post Wayne

I would use subforms but for two reasons: a) i have set OnClick events on the listbox items so that forms pop up containing more/full data is available to the user (for that particular product) and b) i think they look ugly!

But i tried a subform in this scenario just now, quickly, and yes using access's built in sorting features is pretty nice indeed. But i need that onclick feature, and the list looks a lot prettier!

I'm gonna go ahead and attemt to throw these buttons in there, and have option buttons for ascending/descending, and just allow the user to sort by price - this is all that has been requested at the moment!

Cheers again Wayne

 

[Sharky II]

I'm gonna go ahead and attemt to throw these buttons in there, and have option buttons for ascending/descending

Not sure if this is actually possible but i'm gonna try and find out!

Cheers

Edd

 

[WayneRyan]

Edd,

I'd still be partial to the form approach. You can use the Form's OnCurrent
event to display the popup forms:

DoCmd.OpenForm "YourPopUp",,,"[KeyField] = " & Me.KeyField

A continuous form would look a lot better than a datasheet. Plus with
Conditional Formatting and Access's built-in functionality it's hard to
beat.

No code to traverse Listboxes!

Wayne