The OpenReport Action was Canceled problem.

[ToniS]

I have a report with a sub report on it, when I run the report in the .ADP
application the report works fine, when I create an .ADE and run the same
report it does not run, I get the following error: "The OpenReport action
was canceled" After further research I found out that the below code will
only work for .ADP, what would be the work around to have this work for an
..ADE?

strSQL = "SELECT ......FROM AView WHERE ShowId = '" & pubShowID & "'"

DoCmd.OpenReport "ExhReport_SubReportRepGroups", acViewDesign
Set subRptrepGroups = Reports!ExhReport_SubReportRepGroups
subRptrepGroups.RecordSource = strSQL
DoCmd.Close acReport, "ExhReport_SubReportRepGroups", acSaveYes


I basically have a subreport with a select statement on a view that I would
like to have a where clause based on ShowID.

I have tried setting the subreport Recordsource and I get the following
error: "You can't set the RecordSet Property in print preview or after
printing has started"

I am currently using A2003

Thanks
ToniS

 

[Klatuu]

Problem starts here:
DoCmd.OpenReport "ExhReport_SubReportRepGroups", acViewDesign

You can't use design view in an ade or mde.

A solution would be to create a stored query from your SQL and have your
report reference that.

 

[ToniS]

do you mean write a Stored Procedure? If this is true, I have tried that and
ran into problems.. Creating the SP for the subReport part was easy, the part
where I ran into problems was trying to pass a parmater for the SP for the
subreport. How do I go about doing that?

Thanks

 

[Klatuu]

I am not sure. I am a bit out of my element here, but I think you might be
able to do it with a pass through query.

 

[Rick Brandt]

do you mean write a Stored Procedure? If this is true, I have tried
that and ran into problems.. Creating the SP for the subReport part
was easy, the part where I ran into problems was trying to pass a
parmater for the SP for the subreport. How do I go about doing that?

Just change the RecordSource in the Open event of each report. That does
not require that you use design view. You will need a way to pass the
information to the report by pulling it from a form, a public variable or by
using OpenArgs, but other than that the code is similar to what you are
doing now.