Can't total main report and subreport if using passthrough queries?

[Greengecko]

Is there a problem totalling a subreport and main report if you have used a passthrough query (executing a SQL stored procedure)?

I have a main report listing budget details for partners and total 'PartnerTotal', followed by a subreport summarising budgets by department with a total 'StaffDeptTotal'in the subreport footer.

Both reports use pass through queries executing SQL stored procedures. Both reports run, and the subreport shows all the detail as required.

BUT, I have tried a couple of ways to total the subreport 'StaffDeptTotal' with 'PartnerTotal'.

E.g. in a section footer I have the 'PartnerTotal' (being =Sum([PartnerBudget])), followed by the subreport, followed by a text box where control source =[rptSubReport].[Report]![StaffDeptTotal]+[PartnerTotal]

RESULT - on running the report is parameter box asking for rptSubReport and then a #name error if you continue.

I get the same result if I simplify the control source to be =[rptSubReport].[Report]![StaffDeptTotal].

(I have converted the subreport to use Access queries and used the above method with a simple main report using access query and it works fine).

Any ideas?

Thanks in advance.

 

[JoeMiller]

Make sure that you are using the name of the subreport OBJECT and not the name of the subreport itself. That's the proper way to reference it. Also, ensure that the name of the textbox that this code is in is not named StaffDeptTotal or PartnerTotal.

HTH Joe Miller
joe.miller@flotech.net

 

[Greengecko]

You star!

I was using the report name not the object name. Can't work out why I've never had this problem before.

Thanks very much

James