passing OrderBy criteria from Form to Report in accde file

[Brian]

I publish my database to my users in accde format. In the db I have a whole
bunch of continuous forms that my users can sort and filter via the
right-click menu. I am able to pass the filter from the Form to the Report
without any problem by using:

DoCmd.OpenReport ReportName, acViewPreview, , ReportFilter, acWindowNormal
On Error Resume Next
DoCmd.SelectObject acReport, ReportName
DoCmd.RunCommand acCmdPrint
DoCmd.Close acReport, ReportName, acSaveNo

When I want to pass the forms OrderBy statement, I used to open the report
using acViewDesign instead of acViewPreview. This worked when the db was
distributed as an accdb file. Now that I distribute as accde file, no luck.
I get a nice long error message.

For alot of reasons, distributing as an accdb is not an option. Does anyone
have any idea how I can programatically change the OrderBy of the report by
passing the OrderBy of the form to it???

Thanks to anyone who reads this, and a special thanks to anyone who responds!!

 

[Allen Browne]

If the report has nothing in its Sorting'n'Grouping, you can use its OrderBy
property. You probably want to pass the string in its OpenArgs, and then
assign in it Report_Open. To pass it, you launch the report from a button on
your form like this:

Dim strWhere As String
Dim strSort As String
If Me.FilterOn Then strWhere = Me.Filter
If Me.OrderByOn then strSort = Me.OrderBy
DoCmd.OpenReport "Report1", acViewPreview, _
WhereCondition:=strWhere, OpenArgs:=strSort

Then in ReportOpen:
If Me.OpenArgs <> vbNullString Then
Me.OrderBy = Me.OpenArgs
Me.OrderByOn = True
End If

If the report is using sorting'n'grouping, that will override the OrderBy,
but you can programmatically change the ControlSource of the GroupLevel.
Example in:
Sorting report records at runtime
at:
http://allenbrowne.com/ser-33.html