Displaying the total from a subform onto a main form. Pleas Help!

[Lucy]

I have entered the following expression as the control source of a textbox
(total cost) on a subform(Invoicesubform):
=sum[stock quantity] * [unit cost]
In the footer of this subform i have made a text box(ordersubtotal) with the
control source being:
=sum [(Total Cost)]
This should show the sub-total of all the total costs. I then want this to
appear on the main form. again i created a textbox and entered the following
expression as the control source:
=Forms![Invoicesubform]![ordersubtotal]
when the main form is shown in form view the sub-total text box shows the
following:
#NAME?
Have I done something wrong? I have based these expressions from the
Northwind Sample Database.
I have tried putting different expressions in using different methods and
the same error message shows!

Lucy

 

[Tom Wickerath]

Hi Lucy,

Try my tutorial on this subject:

Displaying Subtotals and a Grand Total on a Form That Displays a One-to-Many
Relationship
http://www.access.qbuilt.com/html/subtotals.html

There is a downloadable sample file as well.

This should show the sub-total of all the total costs.

Verify that you see a calculated result, similar to that shown in Figure 4
of the article cited above.

I then want this to
appear on the main form. again i created a textbox and entered the following
expression as the control source:
=Forms![Invoicesubform]![ordersubtotal]

This statement needs to look like this:

=[NameOfSubformObject].[Form]![NameOfTextboxInSubform]


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I have entered the following expression as the control source of a textbox
(total cost) on a subform(Invoicesubform):
=sum[stock quantity] * [unit cost]
In the footer of this subform i have made a text box(ordersubtotal) with the
control source being:
=sum [(Total Cost)]
This should show the sub-total of all the total costs. I then want this to
appear on the main form. again i created a textbox and entered the following
expression as the control source:
=Forms![Invoicesubform]![ordersubtotal]
when the main form is shown in form view the sub-total text box shows the
following:
#NAME?
Have I done something wrong? I have based these expressions from the
Northwind Sample Database.
I have tried putting different expressions in using different methods and
the same error message shows!

Lucy

 

[Lucy]

Does my subform and main form have to have a relationship?

My subform is made up from 3 tables: Order, stock and invoice.
My main form is made from the invoice table as well.

Lucy

Hi Lucy,

Try my tutorial on this subject:

Displaying Subtotals and a Grand Total on a Form That Displays a One-to-Many
Relationship
http://www.access.qbuilt.com/html/subtotals.html

There is a downloadable sample file as well.

This should show the sub-total of all the total costs.

Verify that you see a calculated result, similar to that shown in Figure 4
of the article cited above.

I then want this to
appear on the main form. again i created a textbox and entered the following
expression as the control source:
=Forms![Invoicesubform]![ordersubtotal]

This statement needs to look like this:

=[NameOfSubformObject].[Form]![NameOfTextboxInSubform]


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I have entered the following expression as the control source of a textbox
(total cost) on a subform(Invoicesubform):
=sum[stock quantity] * [unit cost]
In the footer of this subform i have made a text box(ordersubtotal) with the
control source being:
=sum [(Total Cost)]
This should show the sub-total of all the total costs. I then want this to
appear on the main form. again i created a textbox and entered the following
expression as the control source:
=Forms![Invoicesubform]![ordersubtotal]
when the main form is shown in form view the sub-total text box shows the
following:
#NAME?
Have I done something wrong? I have based these expressions from the
Northwind Sample Database.
I have tried putting different expressions in using different methods and
the same error message shows!

Lucy

 

[Tom Wickerath]

Hi Lucy,

Forms don’t have relationships, tables do. Have you established
relationships between your tables at the engine level, using Tools >
Relationships? In case you are not sure about how to do this, please refer
to this article:

ACC2000: Defining Relationships Between Tables in a Microsoft Access Database
http://support.microsoft.com/?id=304467

Note: Disregard the "ACC2000" in the title. This article applies equally
well to all versions of Access.

The subform container, a control on your main form that holds your subform,
includes properties for Link Master Field / Link Child Field. You need to
select the subform object, as shown in Figure 5 of my article. These
properties are shown on the Data tab. If these linking fields are not
properly specified, then you will likely end up with a subform that displays
*all* records, not just those records related to the current record displayed
in the main form. Is this what you meant by "Does my subform and main form
have to have a relationship?"


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Lucy]

Hi sorry my mistake. What i meant to ask was that should a table used on the
subform be related to a table used on the main form? appologies if this isnt
really making much sense im not very good at explaining myself.

I have already created all of my relationships i feel are needed.

 

[Tom Wickerath]

Hi Lucy,

No need to apologize for anything....I'm here to try to help. Or is that a
"Korean" sorry? My lovely wife, who was born and raised in South Korea, tells
me that a Korean sorry doesn't mean that the person necessarily feels any
sorrow! These days, when she says "I'm sorry" for something, I always joke
with her "Is that an American sorry or a Korean sorry". <smile>

To answer your question, in general, yes, I would think that the tables
should be related. A classic example from the sample Northwind database comes
to mind, which involves the following tables: Customers, Orders and Order
Details. If you look at the Orders form, information in the top section of
the form is stored in the Orders table. The combo box for selecting a
Customer uses a query based on the Customers table as it's row source. The
Order Details information is shown in the subform.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________