Sorting QueryDef by Parameter??

[IfButOnly]

I have several forms each with a listbox - users search or click a line in the listbox to move to that record. The listbox can be sorted on any of the columns by clicking on a column header. If the listbox is filtered or sorted then I use the listbox SQL as form recordsource to have everything working together.

Following suggestions in the forum on good design, I have been looking at removing current SQL statements from modules and using stored querydefs and parameters. However, I have been unable to find a decent technique for resorting the listbox without reading the stored querydef, modifying it and recreating it in code - I believe this would give me the same overheads as if I did it all in code.

I thought the answer may be in applying the sort or filter to the form recordsource and then using that for the listbox rowsource, eg:-

Me.OrderBy = "LastName DESC, FirstName"
Me.OrderByOn = True
Me.lstResults.RowSource = Me.RecordSource
Me.lstResults.Requery

but the sort is not applied to the listbox rowsource.

----

I hope the above makes sense... and would appreciate any feedback or suggestions on how I can achieve this using stored querydefs.

many thanks... Peter

 

[llkhoutx]

You filter an Access listbox by adding a SWHERE clause to the RowSource. There's no OrderBy or OrderByOn VBA properties for a listbox. Maybe that's the VB way.