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.
Any ideas how can I get around this and still get a nice report display where the main form information is not repeated?
Julie
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.Filtering in a report with hidden fields
-
- Lounger
- Posts: 47
- Joined: 24 Oct 2011, 19:24
Filtering in a report with hidden fields
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filtering in a report with hidden fields
How does the report relate to the form and subform?
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 47
- Joined: 24 Oct 2011, 19:24
Re: Filtering in a report with hidden fields
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.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filtering in a report with hidden fields
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.
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.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 47
- Joined: 24 Oct 2011, 19:24
Re: Filtering in a report with hidden fields
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.
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filtering in a report with hidden fields
So the code from your first post is code behind the report? (I had assumed that it was code from the form or subform)
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 47
- Joined: 24 Oct 2011, 19:24
Re: Filtering in a report with hidden fields
Yes. It's the code 'on click' when a filter button on the report is clicked.
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filtering in a report with hidden fields
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.
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.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 47
- Joined: 24 Oct 2011, 19:24
Re: Filtering in a report with hidden fields
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
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
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filtering in a report with hidden fields
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.
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.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filtering in a report with hidden fields
I fear that the only suggestion I can come up with is to set Hide Duplicates to No again. Sorry!
Best wishes,
Hans
Hans