Sorting a ListBox (1 Viewer)

Martin Beney

Registered User.
Local time
Today, 15:22
Joined
Mar 22, 2007
Messages
27
Hi,
Here's an interesting problem. First though I can find many other ways to solve this problem, its just that I can't get THIS way to work which is odd.

I am using a QueryDef and setting a parameter to set the order by clause and then setting the the ListBox's record set to the querydef.openrecordset.

The result is a populated listbox. But it is not sorted.

I also tried setting the recordset's sort clause. Once again it did nothing.

So has anybody any ideas on how to set the sorting order for a listbox that is populated using a record set?

Martin
 

Minty

AWF VIP
Local time
Today, 09:52
Joined
Jul 26, 2013
Messages
10,353
Any chance of seeing your code.
If you open the query in the query window is it ordered correctly?
 

Martin Beney

Registered User.
Local time
Today, 15:22
Joined
Mar 22, 2007
Messages
27
For what its worth the code is:-

Private Sub Sort_By_List_Click()

Dim wQueryDef As DAO.QueryDef
Dim wRecordSet As DAO.Recordset

Set wQueryDef = CodeDb.QueryDefs("aQuery")
' wQueryDef.Parameters("sortByClause") = Me.Sort_By_List
Set wRecordSet = wQueryDef.OpenRecordset
wRecordSet.Sort = Me.Sort_By_List
Set Me.List_Box.Recordset = wRecordSet

End Sub

Me.Sort_By_List has two columns, a text version of what the sort is and the actual Order By Clause field.

The commented line also does not work.

The source querydef works fine and you can change the sort field without any problems

Martin
 

Minty

AWF VIP
Local time
Today, 09:52
Joined
Jul 26, 2013
Messages
10,353
When you say the parameters clause doesn't work, surely that is why it's not sorted?

What is the SQL of the query?

In fact that code makes no sense - you are trying to set the sort order by your query def then applying it to the recordset as well?

Have a read here for some possible assistance https://access-programmers.co.uk/forums/showthread.php?t=253681
 

Martin Beney

Registered User.
Local time
Today, 15:22
Joined
Mar 22, 2007
Messages
27
When I say the parameters clause does not work I should have explained it fully.

I have tried making the query a parameter query and then used the commented line to set the parameter. The recordset's sort was not used in this case.

When that failed I removed the parameter clause from the query and set the comment for the parameter setting. Then added the .sort for the recordset.

In neither case did the data get sorted.

*****************************
Your reference DID work. It seems that the recordset.sort clause is only evaluated when you open the records.

I may have missed that in searching documentation but I don't think so.

So many thanks

Martin
 

Minty

AWF VIP
Local time
Today, 09:52
Joined
Jul 26, 2013
Messages
10,353
In fairness it is a little confusing.
I'm still a little baffled as to why an Order By ... in the source query wouldn't sort the results.
 

Users who are viewing this thread

Top Bottom