filtering report based on a form (1 Viewer)

Good afternoon/evening!

I have formatted form based on a query, that allows users to double-click some of the form headers, and access the Quick Filter menus, tailoring (and sorting) the form for their specific needs. I also have a report based on this form - same information in a printer-friendly format. I would like the report to take it's cue from the form, and only display the records that the user selected. I tried to do this in the underlying query, using the form fields as criteria, however in doing so, while it did display the correct results on the report, the criteria "disabled" the quick-filter menu on the form itself, leaving only the standard "text" and "number" filter menus. (My users are very limited in their familiarity/experience/confidence/capabilities of Access (or Excel, for that matter) so I need to make this as user-friendly as possible.)

I'm not super familiar with the reporting feature of Access, and I did try to add click-events on the report itself to no avail, but this is proving to be far more complicated that I think it should be...

Any guidance would be greatly appreciated!

i actually found the solution on a previous post (similar problem - same solution)

Thank you "Sergeant"!



DoCmd.OpenReport "MyReportName", acPreview
If Me.FilterOn Then
With Reports![MyReportName]
.Filter = Me.Filter 'Use the filter from the calling form.
.FilterOn = True
End With
End If

I would also expect this to work:

DoCmd.OpenReport "MyReportName", acPreview, , Me.Filter

thanks, Paul. It does work and it's cleaner. I did just notice however, that it's only preserving the filters from the reports, but not the sorting order (which makes sense, given there's no mention of sorting in the code.) How can I include the sort order from the form on the report?

The problem is that reports don't have the same property. Sorting on a report is done in the Group & Sort area. I've controlled that using a form where the user selects their desired sorting, but I've never tried to use the form's OrderBy property for a report. I'm sure it's possible, but you'd have to translate a string like "Field1, Field2 Desc" to the syntax required by Group & Sort. Things like setting:

Me.GroupLevel(0).ControlSource
Me.GroupLevel(0).SortOrder

but with an undetermined number of sorts. If I get time I'll poke around.

I've looked all morning for the solution and the second that I post the question, I found it by myself - again!

If Me.OrderByOn then
With Reports("rpt_PtNoBeginsWith")
.OrderBy = Me.OrderBy
.OrderByOn = True
End With
End If

Well, you learn something new every day. Thanks for that!

Thank YOU for following up! Cheers!