Changing recordsource and sort on open of a report

[emtenzer]

I need to run a report with different options. I have two Option Groups on my form with the following code on open of my report:

Private Sub Report_Open(Cancel As Integer)

If Forms!frmPrintCheckRegister!FrameSort.Value = 1 Then
Me.OrderBy = "CheckDate"

End If

If Forms!frmPrintCheckRegister!FrameSort.Value = 2 Then
Me.OrderBy = "CheckNumber"

End If

If Forms!frmPrintCheckRegister!FrameSort.Value = 3 Then
Me.OrderBy = "CategoryName"

End If

End Sub

The problem is I can't add the correct code to also change the recordsource ( the other Option Group) when the report opens.

Any suggestions? Thanks.

 

[dhookom]

I don't like using the OrderBy property since the Sorting and Grouping will take over the actual order in the report. You can use Allen Browne's tip Sorting report records at runtime.

You can also use code in the On Open event of a report to set the record source. Rather than changing the record source, I might change the SQL property of a saved query prior to opening the report.

   Currentdb.QueryDefs("qryReportBase").SQL = "SELECT ...."
   DoCmd.OpenReport ....

Duane
Hook'D on Access
MS Access MVP

 

[emtenzer]

Thanks. I will try your suggestions.