listbox to manipulate order of report categories

[Happy YN]

I have a report which is grouped by categories. I want the default to be that all categories are reported but sometimes the user would want to eliminate some categories or to order them according to his wish (no specific thing to perform an order by on). I have a form with 2 listboxes the first listavailable contains the categories ,the second lstselected contains the ones to go in the report. with buttons I can manipulate which ones go from lstavailable to lst selected and also bump them up or down until the derired categories are ordered to the users wish in listselected. But how can I now force my report to use this second listbox to set the requiremnts for the report until the next time it is changed
Thanks

 

[Happy YN]

Any help on this one?
thanks!

 

[SteveA]

Unfortunately, the DoCmd.OpenReport command doesn't allow you to pass the OrderBy criteria the same way you can pass a filter (to the best of my knowledge).

However, you can place code in the OnOpen event of your report to retrieve these details from your form and sort the data before the report is displayed.

Any order you set this way is added to any sorting and grouping already held within the report. It will however, override any sorting that you may have in the query itself.

The properties you need to set in the OnOpen event of the report are OrderBy and OrderByOn.

If anyone knows a better way, please let me know

HTH
SteveA

[This message has been edited by SteveA (edited 01-30-2002).]

 

[Happy YN]

thanks steve -i posted it in 2 places cos the day was wearing on & I was desperate.
Thanks for your reply, however when the report is made, that form with the listboxes is not always open & its a pity to force the user to open it and set the option every time.
In the end I decided to create a table called reportcatorder and fill it with the users choices from the selected listbox. i could then include the autonumber field in my query feeding the report and order by it. each time the user would change the options it would first delete the entire contents of the table before putting in the new items and their order but if not fiddled with the table would keep its set value . It works marvelously
here is the code I put on the form with the 2 listboxes

Private Sub cmdReportOrder_Click()
DoCmd.RunSQL "DELETE ReportCatOrder.CatOrderID, ReportCatOrder.Category FROM ReportCatOrder;"

Dim ctlSource As Control
Set ctlSource = Me!lstSelected
Dim rs As ADODB.Recordset
Dim intCurrentRow As Integer
Set rs = New ADODB.Recordset
rs.Open "ReportCatOrder", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
For intCurrentRow = 0 To ctlSource.ListCount - 1
rs.AddNew
rs!Category = ctlSource.Column(0, intCurrentRow)

rs.Update
Next intCurrentRow

End Sub
thanks once again

 

[antomack]

Not sure if the following samples deal with exactly what your trying but there is a good example of using a form to filter or order a report in the database for download here
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q175072