Problem Totalling Subreport on Main form

[xian2]

Hi All,

I know you have heard this all before from the millions of other people asking the same question, but I have read through many other posts and NeoPa's guide and another guide i found on the internet (http://allenbrowne.com/casu-18.html) but to no avail.

Some background info:

frmTours
Contains 2 subforms (on seperate tabs), one to select the desire Tour Days and the other to select the Additional Costs.

rptInvoice
This contains two subreports in the ClientID header section (tblToursOffered subreport and tblAdditionalCosts subreport). Both of these subreports have a subtotal textbox in the Report Footer section (=Sum([DayPrice]) and =Sum([Price])). They are named 'Subtotal1' and 'Subtotal2' and show the subtotal without problem.

The problem occurs when I try to create a textbox displaying the total in the main form. I get either #Name or a #Error appearing in the box when i run the report. I have tried lots of different control sources for this text box and have placed it in all different sections of rptInvoice (ClientID Header, Detail, ClientID Footer and Page Footer). The code I have tried is as follows (I only tried to display Subtotal1 to start with but would like it to total both):

=IIf([tblToursOffered subreport].[Report].[HasData],Nz([tblToursOffered subreport].[Report].[Subtotal1],0),0)

But get the #Error when I run the report.

I have tried to explain this as best I can but any information you need, let me know.

Many thanks in advance for any help you can offer.

Edd

[ADezii]

Hi All,

I know you have heard this all before from the millions of other people asking the same question, but I have read through many other posts and NeoPa's guide and another guide i found on the internet (http://allenbrowne.com/casu-18.html) but to no avail.

Some background info:

frmTours
Contains 2 subforms (on seperate tabs), one to select the desire Tour Days and the other to select the Additional Costs.

rptInvoice
This contains two subreports in the ClientID header section (tblToursOffered subreport and tblAdditionalCosts subreport). Both of these subreports have a subtotal textbox in the Report Footer section (=Sum([DayPrice]) and =Sum([Price])). They are named 'Subtotal1' and 'Subtotal2' and show the subtotal without problem.

The problem occurs when I try to create a textbox displaying the total in the main form. I get either #Name or a #Error appearing in the box when i run the report. I have tried lots of different control sources for this text box and have placed it in all different sections of rptInvoice (ClientID Header, Detail, ClientID Footer and Page Footer). The code I have tried is as follows (I only tried to display Subtotal1 to start with but would like it to total both):

=IIf([tblToursOffered subreport].[Report].[HasData],Nz([tblToursOffered subreport].[Report].[Subtotal1],0),0)

But get the #Error when I run the report.

I have tried to explain this as best I can but any information you need, let me know.

Many thanks in advance for any help you can offer.

Edd

Your Syntax may be slightly off. Your Text Box is on the Main Form, so you may have to more fully qualify the path:

Me![tblToursOffered subreport].Report.HasData
                                  OR
Reports!<your report name>![tblToursOffered subreport].Report.HasData

[xian2]

Hi,

many thanks for your response.

I tried:

Reports!rptInvoice![tblToursOffered subreport].Report.HasData

But got the value '-1' and was wondering how i complete the expression to get it to equal the value in textbox 'Subtotal1'

Also, how would I get it to total the amount in both subforms?

Subtotal1 + Subtotal2?

many thanks again

Edd

[xian2]

Ah, Got it thanks to a friend.

Simple error, the boxes were named Text12 and Text14 not Subtotal1 and Subtotal2. I thought I had renamed them

Here is the finished code for them:

=[Reports]![rptInvoice]![tblToursOffered subreport].[Report].[Text12]

=[Reports]![rptInvoice]![tblAdditionalCosts subreport].[Report].[Text14]

The only problem now is trying to add the two expressions. Any Ideas?

I have tried the Sum function but probably did it wrong.

Any ideas greatly appreciated as always.

Best

Edd

[ADezii]

Hi,

many thanks for your response.

I tried:

Reports!rptInvoice![tblToursOffered subreport].Report.HasData

But got the value '-1' and was wondering how i complete the expression to get it to equal the value in textbox 'Subtotal1'

Also, how would I get it to total the amount in both subforms?

Subtotal1 + Subtotal2?

many thanks again

Edd

-1 equates to True and indicates that the Sub-Report in fact does have data. To obtain the value in Subtotal1 Text Box:

Reports!rptInvoice![tblToursOffered subreport].Report![Subtotal1]

Use similiar syntax for the other Sub-Report, as in

Reports!rptInvoice![<sub report name>].Report![Subtotal2]

[NeoPa]

...The only problem now is trying to add the two expressions. Any Ideas?

I have tried the Sum function but probably did it wrong.

Any ideas greatly appreciated as always.

Best

Edd

Unless I misunderstand something here, you just need to use the '+' operator.
You have A & B so you're looking for :

=A+B

=[Reports]![rptInvoice]![tblToursOffered subreport].[Report].[Text12]+[Reports]![rptInvoice]![tblAdditionalCosts subreport].[Report].[Text14]

[xian2]

Great, worked an absolute treat,

Thank you

Edd