Sorting question on an Listbox

[Toon_24]

Hello,

Can someone help please

I have a query build that contains data from al sales list (Partnr.
Description, price, total_sales, back_order etc.) The query sort order is
ascending on Partnr. Results of the query are visible in list box on a form.

What I would like to do is sorting the list box by buttons on other columns.

So if I click on the “backorder†button the list box sorts in a descending
order of the back_order column
And if I click on the “Total sales button†the list box sorts in a ascending
order of the total_sales column


Thanks in advance

 

[Toon_24]

Sory I forgot to rease the question

what is the code that i have to put into the 2 buttons to get the requested
results

thanks in advance

 

[Rick Brandt]

I have a query build that contains data from al sales list (Partnr.
Description, price, total_sales, back_order etc.) The query sort order
is ascending on Partnr. Results of the query are visible in list box on
a form.

What I would like to do is sorting the list box by buttons on other
columns.

There are several ways to do this depending on how you are currently set
up. Your ListBox RowSource property can either be a saved query or a SQL
Statement string. Which is yours? When you look at the RowSource
property do you see only the name of a query or do you see "SELECT..."?

You could create separate saved queries that each give you different
sorting. Then your code would simply change the name of the query in the
RowSource property...

Me.ListBoxName.RowSource = "NameOfQuery"

If you are using a SQL Statement string then your code could change that.

Me.ListBoxName.RowSource = "SELECT...FROM...ORDER BY..."

If the SQL string is rather long you could set a form level string
variable to a base SQL statement that includes everything except the
ORDER BY part. Then your code just adds the appropriate ORDER BY to the
end of that string...

Me.ListBoxName.RowSource = BaseSQLVariable & " ORDER BY..."

 

[Toon_24]

Thanks Rick for your response,

Your last option sounds intresting because the SQL is long, bud......... how
do i set a form level string variable to a base SQL statement????

 

[Rick Brandt]

Thanks Rick for your response,

Your last option sounds intresting because the SQL is long, bud.........
how do i set a form level string variable to a base SQL statement????

In the form's module *not* inside a function or sub-routine declare and
set the variable as usual...

Dim baseSQL as String
baseSQL = "SELECT * FROM TableName"

The best place to do this is at the top of the module right after...

Option Compare Database
Option Explicit

You can then use that variable anywhere within that module.

 

[Toon_24]

Thanks Rick, see if I can get this to work..