ADP: Parameterized Recordsources for Subreports on a Report

[Rick]

Access 2002
SQL Server 2000
ADP Project

I have a report that uses a stored procedure (no parameters) as the
recordsource. The report contains a subreport in a GroupFooter, which is
where it must be. The subreport's recordsource is a stored procedure that
takes one parameter.

When I run the report the subreport yields the same records each time, which
is the records related to the first record of the report, rather than giving
me a unique recordset for each new primary key in the report.

How do I set up the recordsources and property settings so that I get the
subreport to display a separate set of records for each record in the
report? (And yes, my report contains a field with the primary key needed by
the subreport to get the proper values.)

* I have tried the MSKB, but it only speaks of forms/subforms and the fix
there doesn't work for reports.
* I have read the Access Developers Handbook/Enterprise and it doesn't
address this, although it relates
similar bugs/shortcomings.
* I have tried using the Recordsource and InputParameters properties (both
on the property sheet and in
code) and neither work.

 

[Vadim Rapp]

R> How do I set up the recordsources and property
R> settings so that I get the subreport to display a
R> separate set of records for each record in the
R> report?

assuming the parent report is report1, subreport is report2, s.p. parameter is
@parm1, linked to the field ParentId on the report1, specify the following
Input Parameters property for Report2:

@parm1=reports!report1!ParentId


Vadim

 

[Rick]

Vadim,

It doesn't work. I continue to get the same records in the subreport
(Report2) for each record in the report (Report1).

I am now thinking that data shaping may be the solution on this, although I
have never done it before and am now reading up on it. If anyone thinks this
is the solution I would welcome any assistance in this area.

Rick

 

[Vadim Rapp]

R> It doesn't work. I continue to get the same records in the
R> subreport (Report2) for each record in the report (Report1).

can't be. I modeled it before posting, and it worked. Must be something you
don't notice. If you want, send me your adp (remove nospam in the email), I'll
check.


Vadim

 

[Steve Jorgensen]

There is an undocumented trick that may work for you here. On the subreport,
use just the stored procedure name as the recordsource, and don't put anything
in the report parameters. Add a control to the section of the main report that
contains the subreport, and give the control the exact same name as the stored
procedure parameter (not including the @ sign).

Let us know if this works.

 

[Rick]

Steve,

Thanks for the idea. Unfortunately it didn't work. Here is what I did. Let
me know if I missed something:

The subreport gets data from the stored procedure
"sprocRSCurrentDonationsTest", so I set the recordsource to that. No input
parameter. I then added a textbox to the GroupFooter (since that is where
the subreport sits) and named it inFamily (the sproc parameter is
@inFamily). Didn't work.

I did try some variations, like assigning the control source to the new
textbox to the FamilyID field and leaving it null. Didn't work.

I am able to get it to work by converting the subreport's stored procedure
to a view, removing the parameter, and then using the Link child/master
properties on the subreport to establish a link. The report's recordsource
is still a sproc. I would rather have a sproc for the subreport as well to
maintain my discipline that all data is accessed via stored procedures, so
if there are any other ideas anyone can think of please let me know.

Rick

 

[Rick]

I tried sending to your email address (I did remove the nospam part) and it
came back undeliverable. Send an email to me at
(e-mail address removed) and I'll send you the file. (take out
the nospam).

Rick

 

[Steve Jorgensen]

Steve,

Thanks for the idea. Unfortunately it didn't work. Here is what I did. Let
me know if I missed something:

The subreport gets data from the stored procedure
"sprocRSCurrentDonationsTest", so I set the recordsource to that. No input
parameter. I then added a textbox to the GroupFooter (since that is where
the subreport sits) and named it inFamily (the sproc parameter is
@inFamily). Didn't work.

I don't see anything wrong with what you described. I've done the same thing
with forms/subforms and it worked. If I get around to it, I'll try with a
report and see if it works for me and post back.