Filtering in a report with hidden fields

[rantaljo]

I have a report (see design view screenshot) that pulls up all inspections and inspection findings from the respective tables. The user can filter the report by various fields in either the main form (inspections) or the subform (inspection findings) using simple VBA to change the focus to the field (see VBA screen shot).So far so good.

Report_Display_View.pdf

VBA_screen_shot.JPG

Now I decide I want to hide duplicated information in the main form (inspections) when there are multiple items in the subform (if there are multiple findings for a given inspection, I only want to see the information about the inspection once). So, I selected the fields and changed the hide property to "yes" for these. Now the filters won't work because the VBA can't "see" the field(s). You can see from the first three filter commands in the VBA that I tried different things to get it to 'see' the row.

Any ideas how can I get around this and still get a nice report display where the main form information is not repeated?

Julie

[HansV]

How does the report relate to the form and subform?

[rantaljo]

Here's a pdf of the query design that runs the report. The data from the main form is stored in the Inspections table, and the data from the subform is stored in the InspectionsFindingsCA table.

[HansV]

Thank you. I'm afraid I fail to see the big picture.
How does filtering the main form and/or subform affect the report? Filtering a form doesn't automatically filter the query - I don't see criteria in the query that refer to the form/subform.

[rantaljo]

The report is being fitered -- not the form. Sorry for the confusion. The forms have nothing to do with the report, only that they are the source of the original data, which comes to the report via the query.

[HansV]

So the code from your first post is code behind the report? (I had assumed that it was code from the form or subform)

[rantaljo]

Yes. It's the code 'on click' when a filter button on the report is clicked.

[HansV]

OK, I'll have to investigate this. I seldom use reports this way, so it's unfamiliar territory for me.

I can create a test setup myself, but if you could attach a stripped down and zipped copy of your database, that would be helpful.

[rantaljo]

Attached is a mocked up version that should contain the relevant tables, query and report. Thanks so much for looking at it and thinking about it.

So again, the problem is that when I change the format property of the report to 'Hide Duplicates' to "Yes" (as it is in this version; see inspection on 7/3 to see multiple findings) so that I don't get common information repeated, the filters in the header stop working.

Julie

[HansV]

I see the problem now, and I understand why your report design is an attractive way to let the user filter the data.
I haven't been able to find a workaround yet, and I suspect there isn't one, but I'll look at it some more.

[HansV]

I fear that the only suggestion I can come up with is to set Hide Duplicates to No again. Sorry!