Problem Totalling Subreport on Main form

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

 =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

Subscribe Post Reply

2913

ADezii

8,834

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

=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

 Me![tblToursOffered subreport].Report.HasData

                                  OR

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

Apr 14 '07 #2

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

Apr 14 '07 #3

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:

Expand|Select|Wrap|Line Numbers

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

Expand|Select|Wrap|Line Numbers

=[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,834

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

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

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

Expand|Select|Wrap|Line Numbers

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

Apr 14 '07 #5

NeoPa

32,556

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

=A+B

Expand|Select|Wrap|Line Numbers

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

Apr 18 '07 #6

xian2

Great, worked an absolute treat,

Thank you

Edd

Apr 18 '07 #7

Similar topics

Report formating problem

by: David B | last post by:

I have a report with 2 sub reports on it. The report is an invoice and the sub reports are dropping data onto the invoice. This worked fine if generating 1 invoice at a time. I am trying to create...

Microsoft Access / VBA

Referring to a subreport (Error 2455)

by: Kirsty Ryder | last post by:

Hi, I have a Report with a subreport, and I want to assign a value to the control source of a control on the subreport depending on the value given by the user in a separate form. Based on...

Microsoft Access / VBA

Make Subreport Function Like Main Report

by: shaqattack1992-newsgroups | last post by:

I know this is kind of a weird question, but is there anyway to give a subreport control of a main report? I posted my situation earlier about having drawings print out after a group. I have a...

Microsoft Access / VBA

Filtering report and subreport from form

by: | last post by:

hi, i have a form on which a user can choose specific criteria such as dates etc, in order to filter the report that is called from the form. i do this by using the Where section of the...

Microsoft Access / VBA

Referring to a specific column in a subreport control from a textbox on the main report...

by: MLH | last post by:

I have a report and on it, a subreport control Main Report Name: rptInvoiceMain Sub Report Name: rptInvoiceSubReport SubReport Control Name: rptInvoiceSubReportCtl The...

Microsoft Access / VBA

Totalling subreports in main report?

by: Henrootje | last post by:

I have a report concerning a customer. In this report we find four subreports. They are in layout identical but data comes from different tabel. Now on the main report I count the totals of each...

Microsoft Access / VBA

How to change the RowSource in a SubReport

by: Simon | last post by:

Dear reader, The syntax for the VBA code to change the RowSource of a Master Report is: Me.RowSource = "TableOrQueryName"

Microsoft Access / VBA

Report/subreport/subreport repeating problem

by: EManning | last post by:

Using 2003. I've got a main report with a subreport and within that subreport is another subreport, all bound. The detail in the 2nd subreport is repeated multiple times for some reason. The...

Microsoft Access / VBA

How to change the RecordSource for a subReport

by: Simon van Beek | last post by:

Dear reader, How to change the RecordSource for a subReport. For forms the syntaxes is:

Microsoft Access / VBA

Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"

by: taylorcarr | last post by:

A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...

General

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Basic Javascript concepts

by: aa123db | last post by:

Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...

Javascript

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++