Subreport problem in ADP file

[vr1307]

I have everything working great except for a couple of my
reports where I have subreports. My main report and my
subreport have the same recordsource which is a stored
procedure that I am passing variables to. I used this same
functionality on my old Front end db in Access 2000 and used
the date and a team number for linking the 2 reports
together. This worked fine on the standalone db. In the
ADP file, it pulls in the recordset for the first team for
all corresponding teams in the main report. I need the
subreport to show the appropriate recordset for the team
currently showing on the main report. I know you can't
create joins between 2 stored procedure results in SQL so
I'm thinking that this may be the issue. Hopefully someone
can provide some feedback on this.

Thx
Bill

 

[cmmrfrds]

I don't believe you will be able to open the same stored procedure on the same connection you opened it on the first time (currentproject.connection). SQL Server is keeping the recordset in temp storage and holding a cursor on it for subsequent traversal. Probably, if you could create a 2nd connection that would work - not sure. Some alternatives would be to create another stored procedure, or create the SQL dynamically in the OnOpen event of the subreport.

 

[OnTheFly]

I sounds like the Report/SubReport have lost their link. Have you checked the Child/Parent linking of the report?

Just a shot in the dark.

 

[vr1307]

I found that the subreport could not link to the main report using a stored proc. I'm guessing it is just like on the SQL side where you can not merge the result sets of 2 different stored procedures. This worked fine when I switched the reports recordsource to a UDF.