Ho to change Sorting and Grouping through code?

[SteveStew44]

Hello WA!

Hope all have been well. How can I change report Sorting and Grouping through code? I tried:

Dim rpt As Report
Dim strReportName As String
strReportName = "ReportName"

DoCmd.OpenReport strReportName, acViewDesign
Set rpt = Reports(strReportName)
rpt.OrderByOn = True
rpt.OrderBy = "Variable1, Variable2"
DoCmd.Close acReport, rpt.name, acSaveYes

DoCmd.OpenReport strReportName, acViewPreview

But this did not work. I assume because it is on the Open command for the report. I think I might have to place some Event Procedure in the Group Header - On Format or On Print? However, I can not find the right syntax to do that.

Thanks in advance for any guidance!

Steve

 

[SteveStew44]

Hi WA!

I found online a simple answer that worked:

Private Sub Report_Open(Cancel As Integer)
If (Forms!frmSalesTaxReports!Check72 = True) Then
Me.GroupLevel(0).ControlSource = "Description"
Else
Me.GroupLevel(0).ControlSource = "TaxRate"
End If
End Sub

Hope that helps anyone looking for the same.

Cheers,
Steve