Microsoft Access Office VB VBA Help and Examples
Real wood furniture for any home at

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
      Me.GroupLevel(0).SortOrder = True
   end if
End Sub
  • Take it for a spin

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

The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.