Suming Sub Form Values on a Main Form

[Guest]

I have tables [Assy], [Parts], [Components], [ConnectAssytoParts]
[ConnectComptoAssy], [Type], and [Color].

Assy consists of columns
AssyKeyID, Assembly#, Type, Color,
-I have the fields Type and Color reference tables [Type] and [Color] by
using the lookup wizard. The bound field is 1 which = TypeID, the number of
columns =2, and the Column weiths = 0â€;1â€

Parts:
PartKeyID, Part#, weight/foot,

Components:
CompKeyID, Component#, weight,

ConnectAssytoParts:
CA2PID, Assy, PartKeyID, Length
-Like the table [Assy]

ConnectAssytoComp:
CA2CID, Assy Componet (KeyID)

I have a forms [AssyParts], [AssyComp], and [AssyMain]
In the forms [AssyParts] and [AssyComp]: you select an assy number and
assign a Part number to it. Both are continuous forms. At the bottom of the
forms (on the form Footers) are unbound text boxes ([AssyPartWtT] and
[AssyCompWtT]) with a control that sums the total weight of each Assy number.

Using my main form you can also assign parts and components to assemblies by
selecting parts/comps from a dropdown combo box in each respective Subform.
This builds a list of what is used to make each assembly.
My problem is I can not get the sum of the Components or Parts to show up on
the main form. I would like to have one unbound text box [AssyWtT] on the
body of the AssyMain form that gives the total weight of each assy and one
unbound text box in the form footer that totals the weight of each assy
[TotalWt].

Here are the controls for each unbound text box

[AssyPartWtT]:
=Sum([Length]*[WeightPerFoot])

[AssyCompWtT]
=Sum(Nz([CompWeight],0))

[AssyWtT]
=Sum(Nz(Forms!AssyComp!AssyCompWtT+Forms!AssyParts!AssyPartsWtT,0))

[TotalWt]
=Sum(Nz([AssyWtT],0))

Both [AssyWtT] and [TotalWt] are showing #Error in the text box.

What should these controls actually be so they work the way I want them to?

I've changed the Query builder for the Main fomr Record Source and got some
different resuelst that I still don't like somebody please help!!

 

[Guest]

Have a look at the Northwind sample data base orders form and sub form. It
has an excellent example of how to do this.

 

[Guest]

I found the Northwind sample but it does not really help me with what I'm
trying to do. It does have form with a Subform where the main form has a
text box that totals the values from the subform. Lets pretend that your the
manager of the company that uses the Northwind database and you want to see
on a form the total sails generated from one company. Now Lets take the
Northwind example one step further and sum all the order totals at the bottom
of the form footer! Then take that one step further and add another subform
also with a total field on it have a text box tat adds all those values up
and a final text box that adds both of those text boxes up. Now filter it so
that you see totals for records that start with "A" or "B". Northwind does
not do this.

Have a look at the Northwind sample data base orders form and sub form. It
has an excellent example of how to do this.

I have tables [Assy], [Parts], [Components], [ConnectAssytoParts]
[ConnectComptoAssy], [Type], and [Color].

Assy consists of columns
AssyKeyID, Assembly#, Type, Color,
-I have the fields Type and Color reference tables [Type] and [Color] by
using the lookup wizard. The bound field is 1 which = TypeID, the number of
columns =2, and the Column weiths = 0â€;1â€

Parts:
PartKeyID, Part#, weight/foot,

Components:
CompKeyID, Component#, weight,

ConnectAssytoParts:
CA2PID, Assy, PartKeyID, Length
-Like the table [Assy]

ConnectAssytoComp:
CA2CID, Assy Componet (KeyID)

I have a forms [AssyParts], [AssyComp], and [AssyMain]
In the forms [AssyParts] and [AssyComp]: you select an assy number and
assign a Part number to it. Both are continuous forms. At the bottom of the
forms (on the form Footers) are unbound text boxes ([AssyPartWtT] and
[AssyCompWtT]) with a control that sums the total weight of each Assy number.

Using my main form you can also assign parts and components to assemblies by
selecting parts/comps from a dropdown combo box in each respective Subform.
This builds a list of what is used to make each assembly.
My problem is I can not get the sum of the Components or Parts to show up on
the main form. I would like to have one unbound text box [AssyWtT] on the
body of the AssyMain form that gives the total weight of each assy and one
unbound text box in the form footer that totals the weight of each assy
[TotalWt].

Here are the controls for each unbound text box

[AssyPartWtT]:
=Sum([Length]*[WeightPerFoot])

[AssyCompWtT]
=Sum(Nz([CompWeight],0))

[AssyWtT]
=Sum(Nz(Forms!AssyComp!AssyCompWtT+Forms!AssyParts!AssyPartsWtT,0))

[TotalWt]
=Sum(Nz([AssyWtT],0))

Both [AssyWtT] and [TotalWt] are showing #Error in the text box.

What should these controls actually be so they work the way I want them to?

I've changed the Query builder for the Main fomr Record Source and got some
different resuelst that I still don't like somebody please help!!