Parameterizing subreports

Hi all,

I have a main report with multiple instances of a particular subreport in
it, with unique control names of course. Is there a way to determine the
corresponding subreport control in the main report while executing the
Report_Open procedure of the subreport? I would like to parameterize the
Filter (or Record Source) property of the subreports by putting the
subreport-specific parameter value, for instance, in the Tag property of
each subreport control and then refer to that value in the Report_Open
procedure of the subreports. The main report is just a container for the
subreports, so there will not be a sensible way to use the link child/master
fields. I could make it work by creating a separate report for each
subreport control, but because the subreports are so many and they differ
only in record filter it would be nice to be able to use a single
parameterizable report.

I am using Access '97.

Thank you for any help.

Regards,
Jack

Do you realize that a main report doesn't have to be bound to a record
source in order to use Link Master/Child on a subreport control?

--
Duane Hookom
MS Access MVP


"Jack" wrote in message
...
Hi all,

I have a main report with multiple instances of a particular subreport in
it, with unique control names of course. Is there a way to determine the
corresponding subreport control in the main report while executing the
Report_Open procedure of the subreport? I would like to parameterize the
Filter (or Record Source) property of the subreports by putting the
subreport-specific parameter value, for instance, in the Tag property of
each subreport control and then refer to that value in the Report_Open
procedure of the subreports. The main report is just a container for the
subreports, so there will not be a sensible way to use the link
child/master
fields. I could make it work by creating a separate report for each
subreport control, but because the subreports are so many and they differ
only in record filter it would be nice to be able to use a single
parameterizable report.

I am using Access '97.

Thank you for any help.

Regards,
Jack

Jack wrote:
I have a main report with multiple instances of a particular subreport in
it, with unique control names of course. Is there a way to determine the
corresponding subreport control in the main report while executing the
Report_Open procedure of the subreport? I would like to parameterize the
Filter (or Record Source) property of the subreports by putting the
subreport-specific parameter value, for instance, in the Tag property of
each subreport control and then refer to that value in the Report_Open
procedure of the subreports. The main report is just a container for the
subreports, so there will not be a sensible way to use the link child/master
fields. I could make it work by creating a separate report for each
subreport control, but because the subreports are so many and they differ
only in record filter it would be nice to be able to use a single
parameterizable report.


I don't know of a way to determine the subreport control
from inside the subreport.

OTOH, you should be able to use the Link Master/Child
properties to filter the subreport. Just add a hidden text
box with an expression like =123 (or whatever you were going
to put in a Tag property) and set the subreport control's
LinkMaster property to the text box.

--
Marsh
MVP [MS Access]

"Marshall Barton" kirjoitti viestissä
...
Jack wrote:
I have a main report with multiple instances of a particular subreport in
it, with unique control names of course. Is there a way to determine the
corresponding subreport control in the main report while executing the
Report_Open procedure of the subreport? I would like to parameterize the
Filter (or Record Source) property of the subreports by putting the
subreport-specific parameter value, for instance, in the Tag property of
each subreport control and then refer to that value in the Report_Open
procedure of the subreports. The main report is just a container for the
subreports, so there will not be a sensible way to use the link
child/master
fields. I could make it work by creating a separate report for each
subreport control, but because the subreports are so many and they differ
only in record filter it would be nice to be able to use a single
parameterizable report.


I don't know of a way to determine the subreport control
from inside the subreport.

OTOH, you should be able to use the Link Master/Child
properties to filter the subreport. Just add a hidden text
box with an expression like =123 (or whatever you were going
to put in a Tag property) and set the subreport control's
LinkMaster property to the text box.

--
Marsh
MVP [MS Access]

Thank you for your reply. I am new to LinkMaster/LinkChild properties and I
did not realize that they can be used with an unbound text box. I think your
advice will be working I just was not able to implement it. Where do I
actually write the string =123? In the Control Source property of the text
box? If so, shall I write it ="=123" or just =123? I named the text box as
Filter1. Do I write Filter1 or [Filter1] in the LinkMaster property of the
subreport control? Do I write something in the LinkChild property of the
subreport control? Do I have to put something somewhere else? The control in
the subreport to be linked is named Account, and its Control Source is Sum
(Sum is a field in the record source query).

I am sorry to bother you with these boring details but I tried all
combinations I could think of but did not find a functional one. And the
Subreport Field Linker always gave me the error "Can't build a link between
unbound forms" when I clicked the button in the data tab of the subreport
control properties dialog.

Jack

"Jack" kirjoitti viestissä
...
"Marshall Barton" kirjoitti viestissä
...
Jack wrote:
I have a main report with multiple instances of a particular subreport
in
it, with unique control names of course. Is there a way to determine
the
corresponding subreport control in the main report while executing the
Report_Open procedure of the subreport? I would like to parameterize
the
Filter (or Record Source) property of the subreports by putting the
subreport-specific parameter value, for instance, in the Tag property
of
each subreport control and then refer to that value in the Report_Open
procedure of the subreports. The main report is just a container for
the
subreports, so there will not be a sensible way to use the link
child/master
fields. I could make it work by creating a separate report for each
subreport control, but because the subreports are so many and they
differ
only in record filter it would be nice to be able to use a single
parameterizable report.


I don't know of a way to determine the subreport control
from inside the subreport.

OTOH, you should be able to use the Link Master/Child
properties to filter the subreport. Just add a hidden text
box with an expression like =123 (or whatever you were going
to put in a Tag property) and set the subreport control's
LinkMaster property to the text box.

--
Marsh
MVP [MS Access]

Thank you for your reply. I am new to LinkMaster/LinkChild properties and
I
did not realize that they can be used with an unbound text box. I think
your
advice will be working I just was not able to implement it. Where do I
actually write the string =123? In the Control Source property of the text
box? If so, shall I write it ="=123" or just =123? I named the text box as
Filter1. Do I write Filter1 or [Filter1] in the LinkMaster property of the
subreport control? Do I write something in the LinkChild property of the
subreport control? Do I have to put something somewhere else? The control
in
the subreport to be linked is named Account, and its Control Source is Sum
(Sum is a field in the record source query).

I am sorry to bother you with these boring details but I tried all
combinations I could think of but did not find a functional one. And the
Subreport Field Linker always gave me the error "Can't build a link
between
unbound forms" when I clicked the button in the data tab of the subreport
control properties dialog.

Jack

Okay, I had a type mismatch with the text box Control Source value and the
record source query field. That's why all my records were filtered away in
my subreport. After I fixed that it seems to be working as you intentioned,
but still there is a problem: in reality my filter is of the form
[Account]=x And [Account]=y. Can that be done using the LinkMaster/Child
properties? How?

Thank you for your help and sorry about my earlier message, it was my
mistake.

Jack

Jack wrote:

"Jack" kirjoitti
"Marshall Barton" kirjoitti
Jack wrote:
I have a main report with multiple instances of a particular subreport
in
it, with unique control names of course. Is there a way to determine
the
corresponding subreport control in the main report while executing the
Report_Open procedure of the subreport? I would like to parameterize
the
Filter (or Record Source) property of the subreports by putting the
subreport-specific parameter value, for instance, in the Tag property
of
each subreport control and then refer to that value in the Report_Open
procedure of the subreports. The main report is just a container for
the
subreports, so there will not be a sensible way to use the link
child/master
fields. I could make it work by creating a separate report for each
subreport control, but because the subreports are so many and they
differ
only in record filter it would be nice to be able to use a single
parameterizable report.


I don't know of a way to determine the subreport control
from inside the subreport.

OTOH, you should be able to use the Link Master/Child
properties to filter the subreport. Just add a hidden text
box with an expression like =123 (or whatever you were going
to put in a Tag property) and set the subreport control's
LinkMaster property to the text box.


Thank you for your reply. I am new to LinkMaster/LinkChild properties and
I
did not realize that they can be used with an unbound text box. I think
your
advice will be working I just was not able to implement it. Where do I
actually write the string =123? In the Control Source property of the text
box? If so, shall I write it ="=123" or just =123? I named the text box as
Filter1. Do I write Filter1 or [Filter1] in the LinkMaster property of the
subreport control? Do I write something in the LinkChild property of the
subreport control? Do I have to put something somewhere else? The control
in
the subreport to be linked is named Account, and its Control Source is Sum
(Sum is a field in the record source query).

I am sorry to bother you with these boring details but I tried all
combinations I could think of but did not find a functional one. And the
Subreport Field Linker always gave me the error "Can't build a link
between
unbound forms" when I clicked the button in the data tab of the subreport
control properties dialog.


Okay, I had a type mismatch with the text box Control Source value and the
record source query field. That's why all my records were filtered away in
my subreport. After I fixed that it seems to be working as you intentioned,
but still there is a problem: in reality my filter is of the form
[Account]=x And [Account]=y. Can that be done using the LinkMaster/Child
properties? How?

Thank you for your help and sorry about my earlier message, it was my
mistake.


Ahhh, Reality is getting in the way again. How
inconvenient. ;-)

The Link Master/Child properties can not be used for a
range. OTOH, I don't see where you've explained how the
unbound main report "knows" what range to use for which
subreport.

--
Marsh
MVP [MS Access]

"Marshall Barton" kirjoitti viestissä
...
Jack wrote:

"Jack" kirjoitti
"Marshall Barton" kirjoitti
Jack wrote:
I have a main report with multiple instances of a particular
subreport
in
it, with unique control names of course. Is there a way to determine
the
corresponding subreport control in the main report while executing
the
Report_Open procedure of the subreport? I would like to parameterize
the
Filter (or Record Source) property of the subreports by putting the
subreport-specific parameter value, for instance, in the Tag
property
of
each subreport control and then refer to that value in the
Report_Open
procedure of the subreports. The main report is just a container for
the
subreports, so there will not be a sensible way to use the link
child/master
fields. I could make it work by creating a separate report for each
subreport control, but because the subreports are so many and they
differ
only in record filter it would be nice to be able to use a single
parameterizable report.


I don't know of a way to determine the subreport control
from inside the subreport.

OTOH, you should be able to use the Link Master/Child
properties to filter the subreport. Just add a hidden text
box with an expression like =123 (or whatever you were going
to put in a Tag property) and set the subreport control's
LinkMaster property to the text box.


Thank you for your reply. I am new to LinkMaster/LinkChild properties
and
I
did not realize that they can be used with an unbound text box. I think
your
advice will be working I just was not able to implement it. Where do I
actually write the string =123? In the Control Source property of the
text
box? If so, shall I write it ="=123" or just =123? I named the text box
as
Filter1. Do I write Filter1 or [Filter1] in the LinkMaster property of
the
subreport control? Do I write something in the LinkChild property of
the
subreport control? Do I have to put something somewhere else? The
control
in
the subreport to be linked is named Account, and its Control Source is
Sum
(Sum is a field in the record source query).

I am sorry to bother you with these boring details but I tried all
combinations I could think of but did not find a functional one. And
the
Subreport Field Linker always gave me the error "Can't build a link
between
unbound forms" when I clicked the button in the data tab of the
subreport
control properties dialog.


Okay, I had a type mismatch with the text box Control Source value and
the
record source query field. That's why all my records were filtered away
in
my subreport. After I fixed that it seems to be working as you
intentioned,
but still there is a problem: in reality my filter is of the form
[Account]=x And [Account]=y. Can that be done using the
LinkMaster/Child
properties? How?

Thank you for your help and sorry about my earlier message, it was my
mistake.


Ahhh, Reality is getting in the way again. How
inconvenient. ;-)

The Link Master/Child properties can not be used for a
range. OTOH, I don't see where you've explained how the
unbound main report "knows" what range to use for which
subreport.

--
Marsh
MVP [MS Access]

I planned to hardcode the range in the main report, ie there would have been
as many subreport controls (and text boxes if we had used that mechanism) in
the details section of the main report as there are subreports inside the
main report. This can be done because the subreport is quite general and
useful in many different main reports, while the main reports are more
specific and thus could contain hardcoded information.

I also tried another way: I created a new table in the database and bound
that table with the main report. In that table I inserted one record for
each subreport. Now there was only one subreport control in the details
section of the main report which was instantiated as many times as there
were records in that record source table. In this model the subreport filter
range was specified by a couple of fields in that source table, but the
problem was not solved: the filter property of the subreport was set only
for the first record and then it remained the same for the rest of the
records even though for them there were different range bounds specified in
the source table. With this model there was also another drawback: the main
report also has to calculate subtotals every now and then between the
subreports and that was difficult to handle when the subreports were
considered as "records" of the main report.

If there are no better solutions I could do so that I hardcode the name of
the main report inside the subreports (that would require a different
subreport template for each main report, though). If I did this is there a
way for the subreport to enumerate all subreport controls in that particular
main report and this way find its own control among them? In that case I
could utilize my initial Tag property idiom, which enables a flexible way to
parameterize the subreport in the form of a packed string.

Jack

Jack wrote:

I planned to hardcode the range in the main report, ie there would have been
as many subreport controls (and text boxes if we had used that mechanism) in
the details section of the main report as there are subreports inside the
main report. This can be done because the subreport is quite general and
useful in many different main reports, while the main reports are more
specific and thus could contain hardcoded information.

I also tried another way: I created a new table in the database and bound
that table with the main report. In that table I inserted one record for
each subreport. Now there was only one subreport control in the details
section of the main report which was instantiated as many times as there
were records in that record source table. In this model the subreport filter
range was specified by a couple of fields in that source table, but the
problem was not solved: the filter property of the subreport was set only
for the first record and then it remained the same for the rest of the
records even though for them there were different range bounds specified in
the source table. With this model there was also another drawback: the main
report also has to calculate subtotals every now and then between the
subreports and that was difficult to handle when the subreports were
considered as "records" of the main report.

If there are no better solutions I could do so that I hardcode the name of
the main report inside the subreports (that would require a different
subreport template for each main report, though). If I did this is there a
way for the subreport to enumerate all subreport controls in that particular
main report and this way find its own control among them? In that case I
could utilize my initial Tag property idiom, which enables a flexible way to
parameterize the subreport in the form of a packed string.


That looks like a fine objective, I just don't see a way to
link to a range using Link Master/Child properties.

If the subreport's record source query includes the range
criteria as a reference to a hidden form's text boxes, you
can not use the main report to set the form's text box
values. The main report's Open event is too soon to get the
first detail's values from it's record source and any other
event is too late to affect the first instance of the
subreport. Kind of a catch 22 :-(

The only other thought I have is to write the subreport's
record source query to use a non-equi join to the main
report's table so you can include a range id value in the
subreport. Then this value can be used in the Link
Master/Child properties.

--
Marsh
MVP [MS Access]

The only other thought I have is to write the subreport's
record source query to use a non-equi join to the main
report's table so you can include a range id value in the
subreport. Then this value can be used in the Link
Master/Child properties.


It took a while until I understood this solution ;-) but this really seems
to solve my problem. Thank you very much for your help!

Jack