Multiple Sorted Reports from One Report
Annoyed at producing several reports with exactly the same contents
but different sort orders????
Want a way to let users choose the field to sort on and just have one report? John to the rescue!!!!!!!!
Imagine a world...where managers didnt exist....
Back to reality ;)
- We have four fields on a report and the user wants to sort by one of those fields and
wants to choose ascending or descending
- The report has one field set for grouping and sorting (doesn't matter which one)
- We have a form called frm_Reports
- We have a button on frm_reports that opens our target report
- Add a combo box to that form that has as its drop down values the names of the fields we want to choose from.Call that combo box cbo_FieldOrder.
- Set the comboboxes default value to the choice the user most wants
- Add a second combo box to the form called cbo_SortOrder and have Ascending and Descending as the 2 values available to choose
- Go into the report and add the following code to the reports code section
Private Sub Report_Open(Cancel As Integer)
'Set the sort by field
Me.GroupLevel(0).ControlSource = forms!frm_Reports!cbo_FieldOrder
'Set the sort by asc or desc
if forms!frm_Reports!cbo_SortOrder = "Ascending" then
Me.GroupLevel(0).SortOrder = False
else
Me.GroupLevel(0).SortOrder = True
end if
End Sub
|
So just to explain. The Me.GroupLevel(0).ControlSource specifies the first group in the report. If you have a field that must be sorted before this
then set that up in the report and change the code to GroupLevel(1). For access 0 = 1. Don't ask.
The Me.GroupLevel(0).SortOrder specifies if that group should be sorted ascending (false) or descending (true)
You could of course extend this code to many more fields and multiple sorts but I'm just giving you the strating point to work from to save a lot of hassle when you only have
to change one report rather than six because your manager wants to add yet another field
| 