Sorting question on an Listbox

  • Thread starter Thread starter Toon_24
  • Start date Start date
T

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
 
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
 
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.
Click to expand...

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..."
 
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????
 
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????
Click to expand...

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.
 
Back
Top