Base Report Off User Filtered Form

[rmulder]

I have a form that shows some records and is based off a query. My users would then further filter the form using "Sort and Filter" block on the "Home" Tab. I have a report that is based off the original query but when the user does their own filtering, my report I made doesn't reflect it. Any idea on how to translate the user's filtering into the record source property of the report? Thanks everyone!

 

[JamesMcS]

Thinking out loud here, but how about me.filter? As an example, I think you can set the value of a label to show the filters that users have selected, so maybe you could store it as a global variable and set the report filter to that.

 

[rmulder]

sorry. my vba knowledge is slim to none. i've been using macros a lot which i hear isn't good, not sure why. but i have done a few basic things in the code builder. sounds plausible but i would need some more specific instructions. Thanks James!

 

[JamesMcS]

I think the way to go would be to put a "Print Report" button on the form, and in the on click event, declare your variable at the start ("Dim Report_Filter as String"), then:

Report_Filter=me.filter
docmd.openreport ("Report_Name", acviewnormal,," & Report_Filter & ")

I'm not 100% sure of the syntax, but have a look at the openreport method in VBA help, it should sort you out. Where I've written & Report_Filter & is the "Wherecondition" parameter. Hope that helps a bit!

 

[vbaInet]

Open your report in design view in code, then set the record source of your report to the record source of your form. Then open the report in normal or preview mode. Both the report and form must have the same fields originally.