How do I calculate a sum from a subform and display on a parent fo

[Guest]

I have a form called frmSales based on a table called tblSales. The main
form has a subform called subfSale_details based on a table called
tblSale_Details. The two tables are linked by Sale_No. tblSale_Details
contains the details of the Sale - Item_No, Cost, Qty & subfSale displays
these records & a computed text box called TotCost=Cost*Qty. I want to
compute the total cost for the sale on the main form, Sum(TotCost). I have
tried basing the main form text box on a query & get "#Name?" in the control,
and on an expression =Sum([subfSale_Details].Form!TotCost)and get "#Error" in
the control. There has to be a way to do this, but I have not found it yet.
Any help would be greatly appreciated.

Thanks, Garry

 

[Van T. Dinh]

Normally, an intermediate Control in the Form Footer section of the Subform
is used.

Check the "Orders Form" of the sample database "NorthWind"

 

[Guest]

Created an intermediate control in the footer section of the subform & set it
to =Sum(TotCost). Name is SubtotalCost. Set control on main form to
=[subfSale_Details].Form!SubtotalCost. As it looksike was done in the
Northwind sample. Still get "#Error" in the control on the main form. I am
gettin very frustrated with Access!!!

Garry

Normally, an intermediate Control in the Form Footer section of the Subform
is used.

Check the "Orders Form" of the sample database "NorthWind"

--
HTH
Van T. Dinh
MVP (Access)

I have a form called frmSales based on a table called tblSales. The main
form has a subform called subfSale_details based on a table called
tblSale_Details. The two tables are linked by Sale_No. tblSale_Details
contains the details of the Sale - Item_No, Cost, Qty & subfSale displays
these records & a computed text box called TotCost=Cost*Qty. I want to
compute the total cost for the sale on the main form, Sum(TotCost). I have
tried basing the main form text box on a query & get "#Name?" in the control,
and on an expression =Sum([subfSale_Details].Form!TotCost)and get "#Error" in
the control. There has to be a way to do this, but I have not found it yet.
Any help would be greatly appreciated.

Thanks, Garry

 

[Van T. Dinh]

Remember the Field [ExtendedPrice] is a Field in the RecordSource of the
Subform in NorthWind. In your case, you use the TotCost which (if I read
correctly) a Control on the Subform.

Try exactly as per the sample database "NorthWind". If it works for
Microsoft, it should work for you.

 

[Guest]

Van
I tried sever things this morning and it is now working. Your last
response was the key. The control in the footer of the sub form was summing
a computed field Sum(TotCost), where TotCost is =Qty*Cost -- (Qty & Cost
are fields in the underlying table). I changed the control to be
Sum(Qty*Cost) and works fine. Don't understand why it didn't work the other
way, but at least it's working. I am fairly new to Access and it can be very
intimidating.
Thanks for your help! -- Garry

Remember the Field [ExtendedPrice] is a Field in the RecordSource of the
Subform in NorthWind. In your case, you use the TotCost which (if I read
correctly) a Control on the Subform.

Try exactly as per the sample database "NorthWind". If it works for
Microsoft, it should work for you.

--
HTH
Van T. Dinh
MVP (Access)

Created an intermediate control in the footer section of the subform & set it
to =Sum(TotCost). Name is SubtotalCost. Set control on main form to
=[subfSale_Details].Form!SubtotalCost. As it looksike was done in the
Northwind sample. Still get "#Error" in the control on the main form. I am
gettin very frustrated with Access!!!

Garry