Sorting a multi-column list box - Access 2003 (1 Viewer)

[krazykasper]

I have a Multi-Column List Box named List10 that is populated via a query (qry_OptimizeIt3). I’m looking to create three Buttons, one above each column that will re-sort the list in ascending order based on the specific column. For example:

FIRSTNAME LASTNAME AGE
John Doe 24
Bill Jones 43
Judy Smith 21
Sally Johnson 38


I thought I could have each button change the RowSource order of the List Box using simple VBA code; something like:

Private Sub SortOrder1_Click()
List10.RowSource = CurrentDb.QueryDefs(qry_OptimizeIt3a).SQL & " ORDER BY " & strFIRSTNAME
End Sub

However, this gives me the error message “Item not found in this collection” and points to the line List10.RowSource = CurrentDb.QueryDefs(qry_OptimizeIt3a).SQL & " ORDER BY " & strFIRSTNAME

I appreciate any input you can provide.


Krazy (Bill) Kasper

 

[pbaldy]

Try enclosing the query name in quotes.

 

[krazykasper]

Thank you all very much for your help.
The code is now working well.
The solution was quite simple.
I created a separate query for each "sort" button and used the following code (with a different query for each button):

Private Sub SortOrder1_Click()
List10.RowSource = CurrentDb.QueryDefs("qry_OptimizeIt3a").SQL
End Sub


Krazy (Bill) Kasper

 

[DCrake]

Your code seems to be a bit inflated. All you r need is the following


Me.List.Rowsource = "QryName"

Having a different query for each sort can be quite cumbersome, especially if you want to use Ascending and Descending sorts. An easier and more managable way is to have one query, then depending on which column heading is clicked you use the QueryDef collection to identify the query and change the sort order in the sql statement and save the revised query.

Dim dbsCurrent As Database
Dim qryNew As QueryDef
Dim SqlFields As String
dim SqlSort as String

Set dbsCurrent = CurrentDb
Set qryFields = dbsCurrent.QueryDefs("Employee List")
SqlFields  = "SELECT * FROM Employees Order By "

Select Case ColumnNo
   Case 1 : SqlSort = "Surname"
   Case 2 : SqlSort = "Forename"
   Case 3 : SqlSort = "DOB"
   etc..
End Select


qryNew.Sql = SqlFields & SqlSort

 

[krazykasper]

Thanks for your input DCrake. I tried using the code you suggested but I get the Run-time error "Object variable or With block variable not set" and it points to the line
qryNew.SQL = SqlFields & SqlSort

Can you help me debug it?

Here's the actual code:

Private Sub SortOrder_Click()
Dim dbsCurrent As Database
Dim qryNew As QueryDef
Dim SqlFields As String
Dim SqlSort As String
Set dbsCurrent = CurrentDb
Set qryFields = dbsCurrent.QueryDefs("qry_OptimizeIt3")
SqlFields = "SELECT * FROM dbptimizeIt1 Order By "
Select Case ColumnNo
Case 1: SqlSort = "LeaseMasterContractId"
Case 2: SqlSort = "SoldToCustomerName"
Case 3: SqlSort = "OrderRepName"
End Select
qryNew.SQL = SqlFields & SqlSort
End Sub

Krazy (Bill) Kasper

 

[WayneRyan]

Bill,

You referenced --> qryNew

Without instantiating it with a Set command.

Wayne