"How do I design a single report that dynamically groups the records according to the user's request?


This is what I did, with dynamic sorting in two levels:

1. Create the report with two groups.
2. When the user selects which field(s) to sort by, their selections are saved in global variables, for example:

  NumberOnly = False: NumberTwo = False
  MySort = "": MySort2 = ""
  Select Case Me!optSort
    Case 1
      MySort = "PassName"
    Case 2
      MySort = "EmployeeName"
    Case 3
      MySort = "Department"
    Case 4
      MySort = "PurposeID"
    Case 5
      MySort = "TheatreID"
    Case 6
      NumberOnly = True
  End Select
  Select Case Me!optSort2
    Case 1
      MySort2 = "PassName"
    Case 2
      MySort2 = "EmployeeName"
    Case 3
      MySort2 = "Department"
    Case 4
      MySort2 = "PurposeID"
    Case 5
      MySort2 = "TheatreID"
    Case 6
      NumberTwo = True
  End Select

3. Use this in the report:

Private Sub Report_Open(Cancel As Integer)
  If Len(MySort) <> 0 Then
    Me.GroupLevel(0).ControlSource = MySort
  End If
  If Len(MySort2) <> 0 Then
    Me.GroupLevel(1).ControlSource = MySort2
  End If
  If NumberOnly = True Then
    Me.GroupLevel(1).ControlSource = "NumStart"
  End If
End Sub