subreport based on a sp?

[evilmousse]

I wanna base a subreport on a stored procedure.
The subreport's recordsource is &quot;EXEC <spname>&quot;.
the subreport opens fine and displays when it's
open on it's own, but yields &quot;incorrect syntax
near the keyword EXEC&quot; when opening the master
report.

I'm guessing it's in the timing of the reports
openings, or i've got to do something manually
about the linkchildfields and linkmasterfields
properties. Still, there's precious little debugging
ability in access, and i'm not finding anything
helpful websearching. Any help would be grand.

-g

 

[natrluvr]

I am only a technical user with limited programming, however I run a lot of subreports off main reports using their own stored procedure or view and I have never had to put EXEC in the recordsource. I just have the name of the subreports stored procedure/view.

Sarah

 

[evilmousse]

ahh, but I need parameters ^_^
sorry i didn't mention that.

-g

 

[evilmousse]

I investigated more, and have more to tell about
the problem.

The main report has 2 subreports, and it's detail
section has code that references one textbox in
each subrpt's footer. both subreports can
shrink (to invis) or grow.

subrpt recordsource = &quot;EXEC <spname>&quot;
fails to work when called by the main rpt.
invalid keyword near EXEC. however, when
the subrpt is opened on it's own, it works.

subrpt recordsource = &quot;SELECT blahblah&quot; works
fine when the main rpt opens it, but is not
what I need.

subrpt recordsource = <spname>
makes the subrpt display ALL (not just
linked to master field) records when
within the main report.

I've not successfully set a filter on these
subreports either. I tried using the
above recordsource method followed by
a filter setting, and no good.

using an ADO query to get a recordset and
then setting it as subrpt recordset hasn't
worked for me either.

There's some other mix and max settings I've
tried out, some of which outright crash
access every time (repeatable bug?), but nothing
has worked so far.


-g

 

[gazza11]

Hi - I am also trying to change a CR8.5 subreport database during runtime. I have looked everywhere and this thread is about the closest I have come to seeing anything that helps.
I am using a CR with a main report and one sub report.
In VB6 my code is:

Set cn = new ADODB.Connection
Set rsADO as New ADODB.Recordset

strConnect = &quot;Provider etc&quot;
cn.Open strConnect
SQL = &quot;Select * from qryOperations&quot;
rsADpen SQL,cn, adOpenDynamic, adLockBatchOptimistic
DisplayReport.Database.setrecordsource rsADO

etc
If I remove the subreport from the report it all works OK as I expected but how do I point the subreport to its required database/recordset.

Please help.

 

[evilmousse]

I'm posting this reply only to bring this message to the forefront again.. I continue to have a dire need to accomplish this on several reports.

All have parameterized sp's running the main report,
usually from and to dates. setting the subreport
recordsource as &quot;exec <spname> <param>&quot; will work
when the subreport is opened on it's own, yet
yield a &quot;incorrect syntax near the word EXEC&quot; when
opened by the main report.

even if you don't have a solution, I could use
suggestions for angles of attack as well.
So far, filtering has failed.
Does anyone have any information on manually
simulating the results that using the link
master/child fields gets?

-g

 

[evilmousse]

I've finally found a way to base a subreport
off a parameterized stored procedure, and
I've made a FAQ for anyone else who
needs an answer.

faq703-3709

happy coding!

-g