470,874 Members | 1,820 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,874 developers. It's quick & easy.

Problem Totalling Subreport on Main form

43
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):

Expand|Select|Wrap|Line Numbers
  1. =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
Apr 14 '07 #1
6 2647
ADezii
8,807 Expert 8TB
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):

Expand|Select|Wrap|Line Numbers
  1. =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:
Expand|Select|Wrap|Line Numbers
  1. Me![tblToursOffered subreport].Report.HasData
  2.                                   OR
  3. Reports!<your report name>![tblToursOffered subreport].Report.HasData
Apr 14 '07 #2
xian2
43
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
Apr 14 '07 #3
xian2
43
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:

Expand|Select|Wrap|Line Numbers
  1. =[Reports]![rptInvoice]![tblToursOffered subreport].[Report].[Text12]
Expand|Select|Wrap|Line Numbers
  1. =[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
Apr 14 '07 #4
ADezii
8,807 Expert 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. Reports!rptInvoice![tblToursOffered subreport].Report![Subtotal1]
Use similiar syntax for the other Sub-Report, as in
Expand|Select|Wrap|Line Numbers
  1. Reports!rptInvoice![<sub report name>].Report![Subtotal2]
Apr 14 '07 #5
NeoPa
32,311 Expert Mod 16PB
...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 :
Expand|Select|Wrap|Line Numbers
  1. =A+B
Expand|Select|Wrap|Line Numbers
  1. =[Reports]![rptInvoice]![tblToursOffered subreport].[Report].[Text12]+[Reports]![rptInvoice]![tblAdditionalCosts subreport].[Report].[Text14]
Apr 18 '07 #6
xian2
43
Great, worked an absolute treat,

Thank you

Edd
Apr 18 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by David B | last post: by
1 post views Thread by Kirsty Ryder | last post: by
1 post views Thread by shaqattack1992-newsgroups | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.