Change GROUPING programmatically in reports

[Dennis]

Here's the deal: In Access 2003, I have a report. I need to be able to change
the "minor sort" programmatically via VBA (if I have to do that before I open
the report, that's okay.) This would involve making changes to the SORTING
AND GROUPING portion of the report. I would not need to SAVE those changes.
the minor sort needs to change based on a set of radio buttons. The
associated subtotals would need to be made visible/invisible based on that
grouping.

And assistance that someone could offer would sure be appreciated.

TIA!

 

[KARL DEWEY]

One way is to add a calculated field in your query feeding the report.
Have an IIF statement testing your radio buttons like this --
My_Sort: IIF([Forms]![YourForm]![Button1] = -1,
[YourDataTable].[FieldToSort], "0")

Leave off the quotes if it is a numerical field.

 

[Marshall Barton]

Here's the deal: In Access 2003, I have a report. I need to be able to change
the "minor sort" programmatically via VBA (if I have to do that before I open
the report, that's okay.) This would involve making changes to the SORTING
AND GROUPING portion of the report. I would not need to SAVE those changes.
the minor sort needs to change based on a set of radio buttons. The
associated subtotals would need to be made visible/invisible based on that
grouping.

Another way is to modify existing entries in the Sorting and
Grouping list in the report's Open event.

If Forms!theform.theradiobutton Then
Me.GroupLevel(N).ControlSource = "this field"
Else
Me.GroupLevel(N).ControlSource = "that field"
End If

Check the GroupLevel topic in VBA Help.

 

[Dennis]

WOOHOOO!!!

Thanks Marshall! You did it again. that was exactly what I was looking for!!!