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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Normally, an intermediate Control in the Form Footer section of the Subform
is used.

Check the "Orders Form" of the sample database "NorthWind"
 
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

Van T. Dinh said:
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)


Garry said:
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
Click to expand...
Click to expand...
 
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.
 
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

Van T. Dinh said:
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)


Garry said:
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
Click to expand...
Click to expand...
 
Back
Top