Subreport in group footer

[James & Darla Martin]

Hello there,

I have a report that is grouped by Supplier + Zone. In each group there are
a list of Lots. So the report is something like this:

Supplier 1 - Zone 1
Lot 1
Lot 2
Lot 3


Supplier 1 - Zone 2
Lot 4
Lot 5

etc.

I also have a subreport in the group footer that needs to print one line for
each lot that appeared in that group. I've tried several methods to do this
and none of them are working.

Method 1
I linked the reports based on Supplier and Zone. The problem here is that
both the main report and the subreport are "filtered" to only print certain
lots for that supplier and zone (based on dates, lot number ranges, etc.). I
perform this filtering by setting the RecordSource appropriately in the
Report_Open event. However as soon as I link the report to the subreport via
the child/parent link, Access no longer lets me set the RecordSource in the
subreport.

Method 2
When I left the parent/child link in place and removed the filtering from
the OpenReport event of the subreport Access, of course, didn't filter the
records and printed more records in the subreport than in the main report.

Method 3
I then added LotNumber to the link between parent & child. The problem this
yielded was that now the subreport only printed for the last lot on the main
report.

Method 4
I scrapped the parent/child link altogether and went back to setting the
filter via RecordSource in the OpenReport event of the subreport. I set the
subreport's RecordSource to be the RecordSource of the main report but also
ensured that the Supplier's and Zone's were identical (via
SupplierID=Parenet.SupplierID and Zone=Parent.Zone). The problem here was
that it worked fine for the first group. But after that Access just repeated
the first group's subreport for each subsequent group. Putting a Stop in the
OpenReport event of the subreport confirmed that Access never only opened
the subreport once. I expected Access to open the subreport each time it got
to the group footer (since the subreport is in the group footer), but it
turns out that Access only opens it for the first group footer, although it
prints it for all of them.

Can anyone give me any suggestions?

Thanks in advance!

James

 

[Fons Ponsioen]

Your first approach should work provided you wish infact
to limit the sub report to supplier and zone related data.
What do you expect to see in the subreport, if it is all
data related to the lot items applicable to the supplier
and zone, but all lot data pertaining to the applicable
lots regardless of the supplier and zone, you need to
create two querries for the subreport,
the first would determine what lot(s) are applicable to
what supplier and zone, than the second query would
determine the total data for the applicable lot(s) as
determined by the first query.
Your report would than be based on the second query.
Hope this helps.
Fons

-----Original Message-----
Hello there,

I have a report that is grouped by Supplier + Zone. In each group there are
a list of Lots. So the report is something like this:

Supplier 1 - Zone 1
Lot 1
Lot 2
Lot 3


Supplier 1 - Zone 2
Lot 4
Lot 5

etc.

I also have a subreport in the group footer that needs to print one line for
each lot that appeared in that group. I've tried several methods to do this
and none of them are working.

Method 1
I linked the reports based on Supplier and Zone. The

problem here is that both the main report and the
subreport are "filtered" to only print certain lots for
that supplier and zone (based on dates, lot number ranges,
etc.). I perform this filtering by setting the
RecordSource appropriately in the Report_Open event.
However as soon as I link the report to the subreport via
the child/parent link, Access no longer lets me set the
RecordSource in the subreport.

 

[James & Darla Martin]

Hi there,

Thanks for the response.

What I'm trying to do is basically have one line in the subreport for each
line in the main report. So, if one group in the main report has four lines
(one for each of four lots), then the subreport will also have four lines
(one for each of the same four lots).

So the report will look something like this:

Lot Number Date Cost Price
---------------------------------------------------------
1001 10/31/2003 60.00 100.00
1002 11/01/2003 65.00 104.00
1007 11/08/2003 70.00 110.00
1009 11/09/2003 60.00 102.00


Lot Number Comments
---------------------------------------------------------
1001 jkdj jka kjkjksdajf
1002 ajfds kjsakf jsaj fksdajksdjkasd
1007 jkals asldf laf
1009 j aldsa f asl


Where the first half is the main report and the second half is the
subreport. (There's a reason why the client needs these two sections
separated rather than printed on one line or on alternating lines.) So I
want the exact same lots to show up on the main report and the subreport.
How can I achieve this?

James

 

[Fons Ponsioen]

I may not have it all straight yet:
I think what you want is:
The main report provides date, cost, and price for all
lots pertaining to a supplier and zone,
The second report (subreport) gives you the same listing
of lots excpt it is with descriptive data.
Is this correct?
If so, could you base the subreport on a query with the
same filter (linked) to the main report. Only showing the
Lot numbers and the descriptive data.
I must be missing something yet.
Help me.
Fons

 

[James & Darla Martin]

Hi there,

You're correct. The problem comes with trying to group each report. If the
reports weren't grouped everything would be very easy. But as soon as
grouping is involved I start getting the various problems that I outlined in
my original post.

In particular, the problem I face is this: the subreport for the first group
works fine - it gives me the lots corresponding to each lot on the main
report. But for all subsequent groups the subreport ends up being just the
subreport from the first group over again.

James

 

[Fons Ponsioen]

I have tried some sample and I did not experience any
problem.
If you wish I can email it to you or if you wish you may
email me a sample (see my email above) and I can review it
for you. It is Friday and it will probably Monday before
I get back with you.
Fons