Subforms work but SubReports don't

[nschroeder]

Sorry, but this will take a bit of explaining.

I'm developing a Pre-Underwriting QC Checklist for our loan processors. It includes 66 questions that are divided into 15 sections. The main form, Audits, has one record per loan, and 15 subforms (Section1, Section2, etc.) on a tab control, since they don't fit on a regular page. Each Section form has a data filter, SNum=n (where n=1-15) so that only the applicable questions will appear on each Section subform. The Audits form and Sections subforms work perfectly.

I then proceeded to create a Pre-Underwriting Audits report, which should just be a printed version of the above. I created the report and dropped in the 15 subforms as they were. When I open the report, each section contains all 66 questions instead of the filtered values (wasted 56 pages of paper to find that out). I couldn't find a resolution, so I started over by creating a copy of the report, deleting out the subforms, and converting the Section1 form to a report. I dropped that in to the report as a subreport, but when I run it, it gives the message, "... can't find the field 'SNum' referred to in your expression. You may have misspelled ... renamed, or deleted.". The strange thing is that when I click Ok, it opens the report and the correct, filtered values appear in Section1. I confirmed that the error message is related to the filter, because if I delete "SNum=1" from the filter in the Section1 report, the message goes away, but then of course, it includes all the questions.

I don't care if I use subforms or subreports in the report, but with neither working, I'm stuck. Any help would be appreciated.

I've attached the db. To see the problem, open the Audits form and select record 1 (the reports are tied to the current record on the Audits form by the AuditKey field). Then open reports Pre-Underwriting Audit and Copy of Pre-Underwriting Audit to see the two scenarios .

 

[JHB]

Keep dirt and cinnamon for themselves, which means, use forms with forms and reports, along with reports, because they are designed to act differently.
Try it now, database is attached.

 

[nschroeder]

Thank you for the reply. However, the problem remains. Your "SubReport" appears to be just a copy of my "Section1" report, but with the "SNum=1" filter removed. When I add it back, the error message returns. When I open Section1 (or SubReport) by it self with the filter, it works just fine. Are filters not allowed in subreports?

 

[JHB]

Is the below not what you want, (else I've misunderstand you).

 

[nschroeder]

It's not exactly what I wanted, but I think I could make some adjustments to make it work. Am I right that you can't use filters on subforms?

 

[JHB]

It's not exactly what I wanted, but I think I could make some adjustments to make it work. Am I right that you can't use filters on subforms?

Then show what you want - printscreen.
Oh yes you can use filters on subforms/subreports - but forms act different when they are printed as reports.
When a form is printed all records are printed not only what is filtered.

 

[nschroeder]

If you could please explain why adding "SNum = 1" as a filter to your SubReport (or my Section1 report -- they are both the same) generates an error, that would get me on my way. That was my original problem I wanted resolved.

 

[JHB]

That was my original problem I wanted resolved.
Okay.

 

[nschroeder]

Does someone please have an answer to how to make filters work on a subreport?