How do I pass form filter and sort order to report

[Guest]

I have a continuous sub-form in a main form. The user can sort and filter on
this sub-form. For technical reasons I use the same data source for a report
that the user prints when he needs to print the form from a print button on
the main form. The sort and filter settings the user set the sub-form to need
to be transferred to the report. Any ideas?

 

[Allen Browne]

You can easily pass the form's filter as the WhereCondition for OpenReport:

Dim strWhere As String
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

Passing the OrderBy is a bit more involved, since the contents of the
report's Sorting'n'Grouping overrides its OrderBy. But you could use the
Open event of the report to set the ControlSource of its GroupLevel(s),
based on the OrderBy property of your form (if the form is open and
OrderByOn is true.)

Here's the basic idea:
Sorting report records at runtime
at:
http://allenbrowne.com/ser-33.html

 

[Guest]

Thanks Allen. Sometimes one gets so involved looking for the complicated that
the simple answer right before your eyes gets missed! Appreciate your help.