How to filter data in a subreport

[RSunday]

I have a main report displaying records. I open the report from a form.

Each record has a number of "child" records. I display those in a a subreport.

Now I want to to have a radio-button on my form to allow the user to filter
some records.

But I have trouble applying the filter to my subreport.

Is there a way to do that?

 

[Allen Browne]

The simplest way to filter a subreport is to use the Master/Child Link
Fields (properties of the subreport control.)

For example, say Form1 has a on option group named Frame1, and if option
value 2 is chosen, you want to filter the subreport to only the records
where AmountDue is zero. You put a text box on the main report, and give it
these properties:
Control Source =-1
Name txtTrue

Then put a text box in the subreport, with properties:
Control Source =IIf([Forms].[Form1].[Frame1]=2, [AmountDue]=0, -1)
Name txtFilterFlag

Now let's assume the LinkMasterFields/LinkChildFields already have the ID
field in them. You add these 2 new text box names to the properties, like
this:
Link Master Fields [ID], [txtTrue]
Link Child Fields [ID], [txtFilterFlag]
The text box on the main report is always -1, which in Access is the value
for True. If the frame on the form is set to any value other than 2,
txtFilterFlag also returns True for everything, so it's not filtered. If the
frame is value 2, then the expression will be true only in the records when
the amount due is filtered, so they are the only ones that will show up, and
so the subreport is filtered.

Another way to approach this is to set up the subreport's query to its
criteria read the frame on the control and return only true records.

Finally, if neither of those approaches work, you can programmatically
write the SQL property of the QueryDef that the subreport reads from, before
opening the report.

 

[RSunday]

Thank you for the suggestions.

I have tried to set the filter properties on my subreport from VBA - but I
have trouble adressing the report properties correctly - I have tried to do
it in the "on open" event on the subreport - but that doesn't work - then I
tried it from the "on open" of the main report - but here I also have trouble.

Any comments to this approach?

The simplest way to filter a subreport is to use the Master/Child Link
Fields (properties of the subreport control.)

For example, say Form1 has a on option group named Frame1, and if option
value 2 is chosen, you want to filter the subreport to only the records
where AmountDue is zero. You put a text box on the main report, and give it
these properties:
Control Source =-1
Name txtTrue

Then put a text box in the subreport, with properties:
Control Source =IIf([Forms].[Form1].[Frame1]=2, [AmountDue]=0, -1)
Name txtFilterFlag

Now let's assume the LinkMasterFields/LinkChildFields already have the ID
field in them. You add these 2 new text box names to the properties, like
this:
Link Master Fields [ID], [txtTrue]
Link Child Fields [ID], [txtFilterFlag]
The text box on the main report is always -1, which in Access is the value
for True. If the frame on the form is set to any value other than 2,
txtFilterFlag also returns True for everything, so it's not filtered. If the
frame is value 2, then the expression will be true only in the records when
the amount due is filtered, so they are the only ones that will show up, and
so the subreport is filtered.

Another way to approach this is to set up the subreport's query to its
criteria read the frame on the control and return only true records.

Finally, if neither of those approaches work, you can programmatically
write the SQL property of the QueryDef that the subreport reads from, before
opening the report.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

I have a main report displaying records. I open the report from a form.

Each record has a number of "child" records. I display those in a a
subreport.

Now I want to to have a radio-button on my form to allow the user to
filter
some records.

But I have trouble applying the filter to my subreport.

Is there a way to do that?

 

[Allen Browne]

Yep: I agree with your conclusions: that's an exercise in frustration.

Hence the suggested alternatives.

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

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

Thank you for the suggestions.

I have tried to set the filter properties on my subreport from VBA - but I
have trouble adressing the report properties correctly - I have tried to
do
it in the "on open" event on the subreport - but that doesn't work - then
I
tried it from the "on open" of the main report - but here I also have
trouble.

Any comments to this approach?

The simplest way to filter a subreport is to use the Master/Child Link
Fields (properties of the subreport control.)

For example, say Form1 has a on option group named Frame1, and if option
value 2 is chosen, you want to filter the subreport to only the records
where AmountDue is zero. You put a text box on the main report, and give
it
these properties:
Control Source =-1
Name txtTrue

Then put a text box in the subreport, with properties:
Control Source =IIf([Forms].[Form1].[Frame1]=2, [AmountDue]=0, -1)
Name txtFilterFlag

Now let's assume the LinkMasterFields/LinkChildFields already have the ID
field in them. You add these 2 new text box names to the properties, like
this:
Link Master Fields [ID], [txtTrue]
Link Child Fields [ID], [txtFilterFlag]
The text box on the main report is always -1, which in Access is the
value
for True. If the frame on the form is set to any value other than 2,
txtFilterFlag also returns True for everything, so it's not filtered. If
the
frame is value 2, then the expression will be true only in the records
when
the amount due is filtered, so they are the only ones that will show up,
and
so the subreport is filtered.

Another way to approach this is to set up the subreport's query to its
criteria read the frame on the control and return only true records.

Finally, if neither of those approaches work, you can programmatically
write the SQL property of the QueryDef that the subreport reads from,
before
opening the report.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

I have a main report displaying records. I open the report from a form.

Each record has a number of "child" records. I display those in a a
subreport.

Now I want to to have a radio-button on my form to allow the user to
filter
some records.

But I have trouble applying the filter to my subreport.

Is there a way to do that?

 

[RSunday]

Now I got it to work - based on the calculated fields in the record sources.

Somehow I had added two text boxes - with conflicting names of some kind -
and I was unable to get to it and delete it. I tried to "tab" through the
elements of the report - but couldn't get to the control with the conflicting
name...

But I then took my backup and made the changes again in the reports - and
now it works!!!

Now I tried to add extra parent-child fields. First as text boxes - but
seemed to have trouble to link a text box to another text box.

So now I just added two calculated fields to the Record Sources of the main
and sub report. I even show the values of these calculated fields on my
reports and can see that they are as I want them to be. But when I add them
to my Link Child Fields and Link Master Fields - then I dont get any data in
my sub report.

So I believe I may have a data type issue - somehow my two calculated fields
are not of the same type or something.

Any suggestions?

Yep: I agree with your conclusions: that's an exercise in frustration.

Hence the suggested alternatives.

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

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

Thank you for the suggestions.

I have tried to set the filter properties on my subreport from VBA - but I
have trouble adressing the report properties correctly - I have tried to
do
it in the "on open" event on the subreport - but that doesn't work - then
I
tried it from the "on open" of the main report - but here I also have
trouble.

Any comments to this approach?

:

The simplest way to filter a subreport is to use the Master/Child Link
Fields (properties of the subreport control.)

For example, say Form1 has a on option group named Frame1, and if option
value 2 is chosen, you want to filter the subreport to only the records
where AmountDue is zero. You put a text box on the main report, and give
it
these properties:
Control Source =-1
Name txtTrue

Then put a text box in the subreport, with properties:
Control Source =IIf([Forms].[Form1].[Frame1]=2, [AmountDue]=0, -1)
Name txtFilterFlag

Now let's assume the LinkMasterFields/LinkChildFields already have the ID
field in them. You add these 2 new text box names to the properties, like
this:
Link Master Fields [ID], [txtTrue]
Link Child Fields [ID], [txtFilterFlag]
The text box on the main report is always -1, which in Access is the
value
for True. If the frame on the form is set to any value other than 2,
txtFilterFlag also returns True for everything, so it's not filtered. If
the
frame is value 2, then the expression will be true only in the records
when
the amount due is filtered, so they are the only ones that will show up,
and
so the subreport is filtered.

Another way to approach this is to set up the subreport's query to its
criteria read the frame on the control and return only true records.

Finally, if neither of those approaches work, you can programmatically
write the SQL property of the QueryDef that the subreport reads from,
before
opening the report.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

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


I have a main report displaying records. I open the report from a form.

Each record has a number of "child" records. I display those in a a
subreport.

Now I want to to have a radio-button on my form to allow the user to
filter
some records.

But I have trouble applying the filter to my subreport.

Is there a way to do that?