Use filtered subform recordset as recordsource for Report

[baulrich]

I have a Form that contains a subform which is a continuous form. The main form has a couple buttons that I've programmed to allow the user to filter the recordset. I also allow the user to further filter and sort the continuous subform using the built-in Access sort/filters. I want the user to be able to "print the filtered list". How can I change my report so that it is based on the filtered results shown in the subform and not the entire query on which the subform is based?

I've tried:



passing Me.Filter to the Report with "DoCmd.OpenReport stDocName, acViewPreview, Me.Filter"

thanks in advance.....

[pbaldy]

Untested, but try

DoCmd.OpenReport stDocName, acViewPreview, , Me.Filter

[baulrich]

That didn't work either.... passing the Filter as the where condition instead of the FilterName.

I also have tried

DoCmd.OpenReport stDocName, acViewPreview, Me.subformname.form.Filter
and
DoCmd.OpenReport stDocName, acViewPreview, , Me.subformname.form.Filter

In all cases, the report opens fine, but does apply the filter as desired.

[pbaldy]

Can you post a sample db here?

[rzw0wr]

Just a thought.
Sometimes during development a filter gets "stuck" in the report filter property.
Check that to see that it is empty.

Dale

[baulrich]

Hey guys... thanks, but no luck yet. I did check the filter property in the report. It was in fact defined - the filter was set to the exact same value as the Record Source. I cleared that value, saved it and tried all the combinations that I've already tried. Still no luck. My report record source is set to use the same query that my continuous subform uses. Should I instead be using the actual recordset of the continous subform as the record source for my report? Is that even possible?

Paul... thanks for the offer to take a look at the DB. It is a split DB with the BE being referenced via linked UNC path. Would it suffice to just upload the FE file? Or would you need both? I will try to get you something to look at if I don't come up with a solution soon.

thanks...

[pbaldy]

Would need both to be able to test, or at least a representative self-contained sample.

[rzw0wr]

Make a new query from your existing query.
We need the results you are getting from the query for the form.
In the new query, add "*" & Forms!YourMainFormName!TextboxName & "*"
for the fields you are filtering.

Use this query for the report.

Just an idea I had.

Dale

[baulrich]

OK guys... I have uploaded files. Let me know if you have trouble.

Quick instructions on how to get to the problem in question:

Open the FE file,
Click "Bids"
Double-Click the only pending bid in the listbox
Change to the "response reports" (2nd tab)
Click the "print report" button

Then close the report and notice that when the continuous form is filtered and the report is printed again, it still prints the full list.

thanks again in advance.....

[baulrich]

One more thing.... To run, place all files in location c:\temp\ and they should run. The FE file is expecting both BE files to be at that location.

[pbaldy]

This seems to work as advertised:

DoCmd.OpenReport stDocName, acViewPreview, , Me.frm_bid_Edit_solicited_companies_subform_by_Div.Form.Filter

[baulrich]

Thanks... That did the trick. I apologize; I certainly thought that was one combination that I tried.

[pbaldy]

Happy to help.