Sub Report and Stored Procedure problem

[Guest]

I have a subreport that when I run it by itself it prints the correct
information.
When I run the main report that has this sub report, I get the following
error.
"Run-time error 2191, You can't set the RecordSet Property in print previwe
or after printing has started"

Below is what I have in the sub report open event

Private Sub Report_Open(Cancel As Integer)

Dim objCmd As New ADODB.Command
Dim tmpESID As Variant

Set objCmd = New ADODB.Command

objCmd.ActiveConnection = Application.CurrentProject.Connection
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "GetRepGroups"

' Stored Procedure Parms
objCmd.Parameters("@optionNumber").Value = "2"

' for testing purposes will set to a variable once I get it working
objCmd.Parameters("@InputID").Value = "{12345678-ABCD-9999-ABCD-
123456789012}"

Set Me.Recordset = objCmd.Execute


Thanks
ToniS

 

[Tom van Stiphout]

On Fri, 26 Oct 2007 09:51:05 -0700, ToniS

That's because the subreport's Report_Open runs after the parent
report's Report_Open.
I would probably eliminate this code altogether, and rather
declaratively set the RecourdSource and InputParameters properties.
Also, use the LinkMasterFields and LinkChildFields properties of the
subreport if there is related information.

Note that if you are using A2007 Runtime there currently are several
bugs in this area, which I have posted about before.

-Tom.

 

[Guest]

I am using A2003. I originally had a select statement with a view as part of
the statement and was using the linkMaster/Child fields and it worked. I
would like to use Stored procedures because they are more efficient. Because
of your explaination below, I am under the impression a subreport can not use
a stored procedure? is that correct?

 

[Sylvain Lafontaine]

First, if it works correctly with a Select statement, I don't see why you
want to change it. Unless you have a very complicated select statement that
really runs too slowly or something that cannot be expressed as a single
select statement, you won't see that much of a difference between a Select
statement and a SP.

Second, I was under the impression that I explained to you in a previous
thread how to use a parameterized stored procedure as the record source of a
subreport. You can try other methods than that one but to my knowledge,
there is only one way to use a SP as the record source of a subreport.

 

[Guest]

I have several reports that use this view (using a view with a combination of
a select statement because it is too complicated to do in one select
statement) I was trying to avoid using a view because views are more
overhead then a SP. Basically I can get the sub report to work correctly
using the SP, but as soon as I run the main report that calls the Subreport I
get the message "You can't set the RecordSet Property in print preview or
after printing has started" It makes sense to me why it was not working
because the order the open events for the report and sub report occurr....

I finally gave up on trying to use a SP, I am just going to use the views,
they work and it does not appear to be too much of an overhead...

Thanks again for your help
ToniS