Sub-Report Problem

[bw]

I have a sub-report that prints "exactly" as it should, but then repeats
many, many times.

The stand alone sub-report prints two pages.
The sub-report in the main report prints 4226 pages (duplicates of two
pages, 2113 times).
The reports are NOT linked.
The sub-report is located in the Detail section of the main report.
I select to print this sub-report from a form, and then make the appropriate
controls visible/not visible depending on the type of report I want. It
works great, just too many pages on the sub-report. All other main reports
(as selected from the Form, work fine).

Can someone suggest what may be causing the report to print duplicates of
the two page sub-report so many times?

 

[Marshall Barton]

I have a sub-report that prints "exactly" as it should, but then repeats
many, many times.

The stand alone sub-report prints two pages.
The sub-report in the main report prints 4226 pages (duplicates of two
pages, 2113 times).
The reports are NOT linked.
The sub-report is located in the Detail section of the main report.
I select to print this sub-report from a form, and then make the appropriate
controls visible/not visible depending on the type of report I want. It
works great, just too many pages on the sub-report. All other main reports
(as selected from the Form, work fine).

Because the subreport is in the detail section, it should
repeat as many times as there are records in the main
report's record source table/query.

 

[bw]

Because the subreport is in the detail section, it should
repeat as many times as there are records in the main
report's record source table/query.

That makes sense, now that you mention it.
So I used the value I get from the Form to select a single item from the
query that has unique values, and it works!

However, this seems like kind of a hokey way of doing this. I imagine there
is a more elegant solution to this, but if I move the subreport anywhere
else, it just doesn't work right. For example, other sections/headers where
I move it will only show one column of a two column report, and other
sections/headers don't show anything at all, etc.

Do you have some more advice?

Bernie

 

[Marshall Barton]

That makes sense, now that you mention it.
So I used the value I get from the Form to select a single item from the
query that has unique values, and it works!

However, this seems like kind of a hokey way of doing this. I imagine there
is a more elegant solution to this, but if I move the subreport anywhere
else, it just doesn't work right. For example, other sections/headers where
I move it will only show one column of a two column report, and other
sections/headers don't show anything at all, etc.

I think you're losing me now. Which query did you use the
form taxt box as a criteria? Normally, you only need to
filter the main report and let the Link Master/Child
properties take care of selecting the appropriate subreport
records. But, you said the the subreport "is not linked".
This means that the subreport sould be the same for every
record in the main report. The only way this would make
much sense is if the subreport contains some kind of boiler
plate or independently calculated values.

Given your original qurstion, I guess you are probably
talking about filtering the main report's record source
query. If that's the case, then you should remove the
criteria from the query and open the report using the
OpenReport method's WhereCondition argument (see VBA Help
for details). The code would be something like:

DoCmd.OpenReport "reportname", acViewPreview, _
WhereCondition:= "somefield=" & Me.thetextbox

 

[bw]

I think you're losing me now. Which query did you use the
form taxt box as a criteria? Normally, you only need to
filter the main report and let the Link Master/Child
properties take care of selecting the appropriate subreport
records. But, you said the the subreport "is not linked".
This means that the subreport sould be the same for every
record in the main report. The only way this would make
much sense is if the subreport contains some kind of boiler
plate or independently calculated values.

Given your original qurstion, I guess you are probably
talking about filtering the main report's record source
query. If that's the case, then you should remove the
criteria from the query and open the report using the
OpenReport method's WhereCondition argument (see VBA Help
for details). The code would be something like:

DoCmd.OpenReport "reportname", acViewPreview, _
WhereCondition:= "somefield=" & Me.thetextbox

Thanks Marsh,

The form gives me a value to use in the main report's query...something
like IIf([Forms]![boxModules]![PW]=4,PrintSubReport,PrintMainReport)

The main form uses a different query than the subreport. The main form has
2113 records (as pointed out earlier), and the subreport only has 154
records.

The subreport is using the same fields as the main report, but if I'm
suppose to link the two, I wouldn't know how to do that. The fields
containted in both reports are [Mnemonic] and [Description], but if I link
these fields, I get duplicate records.

It makes sense to me NOT to link, but what do I know...

Thanks again for your help.

 

[Marshall Barton]

I think you're losing me now. Which query did you use the
form taxt box as a criteria? Normally, you only need to
filter the main report and let the Link Master/Child
properties take care of selecting the appropriate subreport
records. But, you said the the subreport "is not linked".
This means that the subreport sould be the same for every
record in the main report. The only way this would make
much sense is if the subreport contains some kind of boiler
plate or independently calculated values.

Given your original qurstion, I guess you are probably
talking about filtering the main report's record source
query. If that's the case, then you should remove the
criteria from the query and open the report using the
OpenReport method's WhereCondition argument (see VBA Help
for details). The code would be something like:

DoCmd.OpenReport "reportname", acViewPreview, _
WhereCondition:= "somefield=" & Me.thetextbox

The form gives me a value to use in the main report's query...something
like IIf([Forms]![boxModules]![PW]=4,PrintSubReport,PrintMainReport)

The main form uses a different query than the subreport. The main form has
2113 records (as pointed out earlier), and the subreport only has 154
records.

The subreport is using the same fields as the main report, but if I'm
suppose to link the two, I wouldn't know how to do that. The fields
containted in both reports are [Mnemonic] and [Description], but if I link
these fields, I get duplicate records.

I guess I just haven't figured out what this report is doing
or how that value is used in the main report's record source
query.

The point of linking the subreport is to narrow the
subreport down from 2113 records to 154 records based on
some information either by a value in the main report
record(s?) or by some calculated value. This avoids having
criteria in the subreport's record source query to do the
filtering. Since you are not using the Link Maaster/Child
properties, how are you filtering the subreport's records?

That expression just doesn't look like something that's used
to filter the data in a query. In any case, if you have a
value in some text box on the form and the query has some
field with a criteria like:
=Forms!theform.sometextbox
then you can remove that criteria from the main report's
query and use the OpenReport approach I outlined earlier. I
thought this was the question we were trying to address.