Sorting columns in a listbox using VBA

[anb001]

I have a listbox (lstResult) which rowsource is q query (qryResult). In the listbox are 3 columns.

On the same form is a combobox, where the entries are "Column1", "Column2" and "Column3", and a checkbox (chkSort).

What I'm looking for is some code where I can sort the columns in the listbox, using the combobbox and the checkbox.

If I e.g. choose "Column1" in the combobox, then the listbox should be sorted after Column1. And so on when chosing "column2" and "column3" in the combobox. Columns should be sorted ascending by default. If the checkbox (chkSort) has the value 'True', the selected column should be sorted descending in stead.

I believe that a Select .... Case statement must be used, but I'm not quite sure about how the code should be:

-----------------------------------------------------
CODE:
Private Sub cboSort_AfterUpdate()

Select Case Me.cboSort

Case 1
Me.lstResult. ?????????
If chkSort = "True" Then
Something with sorting ascending/descending.

Case 2


Case 3


End Select


End Sub
----------------------------------------------------

I hope someone can assist.

Thanks.

/Anders

 

[pbaldy]

You have to set the Row Source property. You can either reset the entire SQL, or build on the existing query. This type of thing:

Me.lstResult.RowSource = "SELECT * FROM qryResult ORDER BY " & Me.cboSort

I'll let you play with adding the ascending/descending bit.

 

[MStef]

Hello anb001!
Look at "DemoLBSortA2000.mdb" (attachment, zip).
Open Form and try. Look at VBA.

 

[anb001]

Pbaldy,

Thanks for poiting me in the right direction. I have come a bit further, and I just need some assistance with incorporating a combobox value into the SQL argument.

This is what I have so far (I have jut included 'Case 1', as the others will be similar):

CODE:
Private Sub cboListOrder_AfterUpdate()

Select Case Me.optManWoman.Value

Case 1
If Me.chkSortDesc = False Then
Me.lstResults.RowSource = "SELECT * FROM QRY_searchAll ORDER BY qryUnionAll.Name"
Else
Me.lstResults.RowSource = "SELECT * FROM QRY_searchAll ORDER BY qryUnionAll.Name DESC"
End If

Case 2

Case 3

End Select

End Sub
CODE END

The part where it says "ORDER BY qryUnionAll.Name", I need the 'Name' part, to be the value of the combobox 'cboListorder.

It works fine if I just insert one of the entries from the combo (Name, Date og Parish) directly, as above, however I can't figure out to how to use the value from the combo box.

/Anders

 

[pbaldy]

Did you notice how I concatenated the value from a combo into the SQL string? That's what you want to do (presuming the bound column of the combo is the field name).

 

[anb001]

Pbaldy, Thanks again. I just noticed that I had put the " sign after [& me.cboSort] in stead of before. That part is working now. However, I still have some problems with sorting descending. I thought that below might work, however it doesn't. Appreciate if you could assist. "SELECT * FROM QRY_searchAll ORDER BY '" & Me.cboSort & "' DESC" Thanks. /Anders

 

[anb001]

Mstef, Thank you for the link. However as I'm far away from civilization, I can't download the file. However, when I get the chance I will take a loo. /Anders

 

[pbaldy]

You wouldn't want the single quotes surrounding the field name in the ORDER BY clause. Those would only be appropriate around a text value in a WHERE clause.

 

[anb001]

Pbaldy,

All in order now. Thank you for the assistance.

/Anders