passing OrderBy criteria from Form to Report in accde file

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!!

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Brian" <Brian DeleteThis @discussions.microsoft.com> wrote in message
news:3389E99D-9F8D-4F5C-8EF7-77E8569D59DC@microsoft.com...
> 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!!
>