Set filter in subreport

[DocBrown]

I have a report that has a group section. In the group header section I have
a subreport where I want to generate cumulative data of the records that have
been grouped. The cumulative data is extracted from a related table that has
a one to many relationship to the data in the main table.

Is there a way to pass a filter from a parent report to the subreport? I get
the error:
Run-time error '2101: The setting you entered isn't valid for this property.

The code is:
Private Sub Report_Open(Cancel As Integer)
Dim FormQuery As Form

Debug.Print "Report_open: " + Me.Name

If intCallCount = 0 Then
Set FormQuery = Forms("Report Query Prompts")
Debug.Print "FormQuery: " + FormQuery.Filter
Me.Filter = FormQuery.Filter
Me.FilterOn = False
intCallCount = intCallCount + 1
End If
End Sub

The Debug.print statements are getting just what I want. The If statement
makes this execute only on the first report open. It doesn't matter what I
put in the Me.Filter assignment. If I put Me.Filter = "" it still fails.

I suspect that at the time the subreport is opening, Access is not allowing
any changes to the form's properties. How can I get the sub report to filter
or get the records I want.

Thanks

 

[Allen Browne]

As you found, trying to pass a filter from the main report to the subreport
is an exercise in frustration.

You may be able to use the LinkMasterFields/LinkChildFields properties of
the subreport control. If the filter involves discrete values (not a range),
and is always applied, there are some creative ways to do this.

If that's not possible, design your interface so the subreport can read the
filter from the same place the main report gets its filter. For example, you
could create a form with text boxes where you enter the starting- and
ending-dates and a command button to open the report. Then the subreport can
reading the same dates from the form.

If that's not possible either, other alternatives are:
- modify the SQL property of the subreport's query before you open report,
e.g.:
CurrentDb.QueryDefs("MySubreportsQuery").SQL = "SELECT ...

- to use a temp table, populated with the data before you OpenReport.

The issue is that the subreport occurs many times on the main report, and so
you need a single source where the filter values are read from.

 

[DocBrown]

Thanks for your input.

Actually, I've also tried to have the subreport get the filter from the same
place as the main report. That didn't work either, same error occured.
Assuming you're the same Allen Browne that posted the Microsoft Access Tips,
I'm using your Search Criteria concept to generate a Flexable search form for
my report. And I combined it with the Multi-Select List box. It works GREAT!

Actually, since I'm gathering Cumulative data for a group of records, the
subreport is placed in the GroupHeader area (It could also be placed in the
GroupFooter also. When the LinkMaster and LinkChild fields are set, the
subreport only gets the data for one of the records in the group.

After I posted this question, another approach occurred to me. Maybe you
have a solution for this:

The Table for the data in the subreport and the main report are linked with
a field EventID. The group is summing the data for the desired main records.
I just need to get the EventID for the records in the group and put them in a
query:

SELECT ZipData.ZipCode, ZipData.Count
FROM Events INNER JOIN ZipData ON Events.EventID = ZipData.EventID
WHERE ((ZipData.EventID) In ([what]))
ORDER BY ZipData.ZipCode;

I need to gather the EventID for the group and substitute them into the
[what] part of the query as a set of numbers, i.e, (1,5). I have found that I
CAN set the RecordSource on the subreport's open.
I also need to set it each time the subreport opens for the appropriate group.

Is this even possible?

 

[Allen Browne]

Is there some way to include the group's key in that query?
Is the group tied to the EventID somehow?

If it is, then instead of the IN () approach, you could just add the group
ID value, and use it in the LinkChildFields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your input.

Actually, I've also tried to have the subreport get the filter from the
same
place as the main report. That didn't work either, same error occured.
Assuming you're the same Allen Browne that posted the Microsoft Access
Tips,
I'm using your Search Criteria concept to generate a Flexable search form
for
my report. And I combined it with the Multi-Select List box. It works
GREAT!

Actually, since I'm gathering Cumulative data for a group of records, the
subreport is placed in the GroupHeader area (It could also be placed in
the
GroupFooter also. When the LinkMaster and LinkChild fields are set, the
subreport only gets the data for one of the records in the group.

After I posted this question, another approach occurred to me. Maybe you
have a solution for this:

The Table for the data in the subreport and the main report are linked
with
a field EventID. The group is summing the data for the desired main
records.
I just need to get the EventID for the records in the group and put them
in a
query:

SELECT ZipData.ZipCode, ZipData.Count
FROM Events INNER JOIN ZipData ON Events.EventID = ZipData.EventID
WHERE ((ZipData.EventID) In ([what]))
ORDER BY ZipData.ZipCode;

I need to gather the EventID for the group and substitute them into the
[what] part of the query as a set of numbers, i.e, (1,5). I have found
that I
CAN set the RecordSource on the subreport's open.
I also need to set it each time the subreport opens for the appropriate
group.

Is this even possible?

As you found, trying to pass a filter from the main report to the
subreport
is an exercise in frustration.

You may be able to use the LinkMasterFields/LinkChildFields properties of
the subreport control. If the filter involves discrete values (not a
range),
and is always applied, there are some creative ways to do this.

If that's not possible, design your interface so the subreport can read
the
filter from the same place the main report gets its filter. For example,
you
could create a form with text boxes where you enter the starting- and
ending-dates and a command button to open the report. Then the subreport
can
reading the same dates from the form.

If that's not possible either, other alternatives are:
- modify the SQL property of the subreport's query before you open
report,
e.g.:
CurrentDb.QueryDefs("MySubreportsQuery").SQL = "SELECT ...

- to use a temp table, populated with the data before you OpenReport.

The issue is that the subreport occurs many times on the main report, and
so
you need a single source where the filter values are read from.

 

[DocBrown]

EventID is the primary key for the main table. The report is grouped on
another field, ProgramArea. ProgramArea can currently be assigned one of 5
values. The report filters can be set to display a subset of the records for
a multple ProgramArea. For example, if the table has 10 records with
ProgramArea set to "BioSITE", the report filter may only display 5 of those
records. The EventIDs in this case may be like 1,5,6,7,9. Another group of
Records might be displayed for ProgramArea="Visual Arts", etc

The Related table ZipCode, has a one to many relation to the records based
on EventID.

I've tried to include the group's key but the subreport either displays the
data for only one record if I include EventID in the LinkChild/Master fields
or it will display ALL the records in the table for the selected ProgramArea
if I don't.

The nearest I can figure, I need to have the report build the subreport
based only on the records that are included in the group. I don't seem to be
able to do that. I am using The filter method because of the pitfalls
mentioned in the Flexible Search Criteria Tip.
The IN () approach seems to get me the closest but I have also found that
the recordsource for the subreport can only be modified at the very first
open event.

Somehow, I think I need to generate a list of the EventIDs that are to be
included in the cumulative data for the ZipCode.

Thanks for all your suggestions, I hope you have some more. *grin*
John

 

[Allen Browne]

I'm not completely clear whether merely filtering the subreport to a
particular ProgramArea will solve the issue. If so, that's just a matter of
basing the subreport on a query that uses Criteria under ProgramArea of:
[Forms].[Form1].[txtProgramArea]

If you can't do that, it is actually possible to write a VBA function that
returns True if the EventID passed in matches a value in a multi-select list
box.

The third alternative is to write the SQL property of the QueryDef the
subreport is based on.

And the final suggestion is to populate a temp table with the relevant
records, and use that as the source of the subreport.

 

[DocBrown]

Allen,

I solved my problem! I put the following code in the subreports Open Event:

--------------------------
Private Sub Report_Open(Cancel As Integer)
Static intStart As Long
Static strArea As String
Dim FormQuery As Form
Dim RS As Recordset
Dim strItems As String
Dim strWhere As String
Dim strQuery As String
Dim lngLen As Long
'Debug.Print "Report_open: " + Me.Name

Set FormQuery = Forms("Report Query Prompts")
Set RS = FormQuery.Recordset
strItems = ""
If intStart = 0 Then
intStart = 1

With RS
.MoveFirst
Do While Not .EOF
strItems = strItems & Str(!EventID) & ","
.MoveNext
Loop
End With

'Remove trailing comma. Add field name, operator and brackets.
lngLen = Len(strItems) - 1
If lngLen > 0 Then
strWhere = "WHERE ((Events.ProgramArea) in ([Reports]![Programs
Activities Cumulative]![ProgramArea_Box])) " _
"AND ((ZipData.EventID) In (" & Left$(strItems, lngLen) & ")) "

strQuery = "SELECT ZipData.EventID, ZipData.ZipCode, ZipData.Count "
strQuery = strQuery + "FROM Events INNER JOIN ZipData ON
Events.EventID = ZipData.EventID "
strQuery = strQuery + strWhere + "ORDER BY ZipData.ZipCode;"
Me.RecordSource = strQuery
End If
End If
End Sub
-------------------------------
This gathers the Event IDs for the records that are to be formatted in the
report. The query form's recordset contains exactly those records that are
displayed on the form. The ProgramArea_box contains the value of the group
field for the report. For the Grand total in the report footer, I want to sum
ALL the filtered records. So I use this as the WHERE string in another
subreport:

strWhere = "WHERE " & _
"((ZipData.EventID) In (" & Left$(strItems, lngLen) & ")) "

I couldn't use the main report's recordset because at the subreport's first
open, the recordset of the report hasn't been set yet.

I tried to combine the two query strings into one using a IIF() in the WHERE
string of the query on the order of:

WHERE ((Events.ProgramArea) in (IIF ( footer, (<Quoted List of
ProgramAreas>),![ProgramArea_Box]) AND .....

but I could get the syntax and substitutions to work.

I'm not completely clear whether merely filtering the subreport to a
particular ProgramArea will solve the issue. If so, that's just a matter of
basing the subreport on a query that uses Criteria under ProgramArea of:
[Forms].[Form1].[txtProgramArea]

If you can't do that, it is actually possible to write a VBA function that
returns True if the EventID passed in matches a value in a multi-select list
box.

The third alternative is to write the SQL property of the QueryDef the
subreport is based on.

And the final suggestion is to populate a temp table with the relevant
records, and use that as the source of the subreport.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

EventID is the primary key for the main table. The report is grouped on
another field, ProgramArea. ProgramArea can currently be assigned one of 5
values. The report filters can be set to display a subset of the records
for
a multple ProgramArea. For example, if the table has 10 records with
ProgramArea set to "BioSITE", the report filter may only display 5 of
those
records. The EventIDs in this case may be like 1,5,6,7,9. Another group of
Records might be displayed for ProgramArea="Visual Arts", etc

The Related table ZipCode, has a one to many relation to the records based
on EventID.

I've tried to include the group's key but the subreport either displays
the
data for only one record if I include EventID in the LinkChild/Master
fields
or it will display ALL the records in the table for the selected
ProgramArea
if I don't.

The nearest I can figure, I need to have the report build the subreport
based only on the records that are included in the group. I don't seem to
be
able to do that. I am using The filter method because of the pitfalls
mentioned in the Flexible Search Criteria Tip.
The IN () approach seems to get me the closest but I have also found that
the recordsource for the subreport can only be modified at the very first
open event.

Somehow, I think I need to generate a list of the EventIDs that are to be
included in the cumulative data for the ZipCode.

Thanks for all your suggestions, I hope you have some more. *grin*
John

 

[Allen Browne]

I solved my problem! I put the following code in the subreports Open
Event:

[snip]

Excellent!