Changing report source query at runtime

[martin DH]

Martin, you can forget most of the code in my earlier post (#13). I was misreading .SortOrder for .SortEnabled :(

The first block should do you fine though, and is a little tidier than what you had before.

Thanks, NeoPa, for your responses. I agree with your tidier notion and changed the code to below which works as intended.

Private Sub Report_Open(Cancel As Integer)
'changes sort order on report by changing control source for sorting/grouping
 
  Select Case Forms!frmAgedNotices.fraSort
  Case 1
    With Me.GroupLevel(1)
      .ControlSource = "Age"
      .SortOrder = True
    End With
    With Me.GroupLevel(2)
      .ControlSource = "SourceID"
      .SortOrder = False
    End With
  Case 2
    With Me.GroupLevel(1)
      .ControlSource = "SourceID"
      .SortOrder = False
    End With
    With Me.GroupLevel(2)
      .ControlSource = "Age"
      .SortOrder = True
    End With
  End Select
 
End Sub

I don't understand where Me.GroupLevel(0 ) went. Possibly it's in use for a higher level sort order. Possibly it doesn't actually matter that the first one is unused when working from code.

As far as you question about Me.GroupLevel(0 ), and this may not be the best way to go about this, but I have that group set up in the Sorting/Grouping properties for the report. Basically, no matter what option the user selects, I want the report to group by X in GroupLevel(0) (where I have labels and text boxes in the header), and then group by the user's selection. Does that make sense?

Thanks,
martin

[NeoPa]

...
As far as you question about Me.GroupLevel(0 ), and this may not be the best way to go about this, but I have that group set up in the Sorting/Grouping properties for the report. Basically, no matter what option the user selects, I want the report to group by X in GroupLevel(0) (where I have labels and text boxes in the header), and then group by the user's selection. Does that make sense?

Thanks,
martin

It certainly does Martin. That's what I was hoping you'd say. It's the only scenario I could think of which indicated you had it fully sorted and there weren't further problems waiting to bite.

Nice going :)