SUM of field in a continuous subform (1 Viewer)

[Bobp3114]

I have a continuous subform (frmCRMPartsRF) on main form (frmCRMEditRF). On the continuous subform I have a txtbox (txtTagRecord)that I wish to total in the from footer (txtTotalSaleAll)' I have tried =SUM([txtTagTotal]) and =SUM(Sum([frmCRMPartsRF!txtTagRecord]) but get an ERROR message. What am I doing wrong?

 

[DHookom]

You can’t sum a control. You can only sum fields and expressions. What is the control source of [txtTagRecord]?

 

[CJ_London]

And where is the control doing the summing? On the sub form or the main form? It makes a difference as to how you create your sum

 

[bob fitz]

I have a continuous subform (frmCRMPartsRF) on main form (frmCRMEditRF). On the continuous subform I have a txtbox (txtTagRecord)that I wish to total in the from footer (txtTotalSaleAll)' I have tried =SUM([txtTagTotal]) and =SUM(Sum([frmCRMPartsRF!txtTagRecord]) but get an ERROR message. What am I doing wrong?

Can you post a copy of the db

 

[Gasman]

I seem to recall I used DSUM() for something similar, years ago.

 

[Bobp3114]

And where is the control doing the summing? On the sub form or the main form? It makes a difference as to how you create your sum

Control is on the form footer

 

[Bobp3114]

The control on the footer tries to sum a textbox that gets its data from other calculated fields. something like... unit sale profit multiplied by quantity considering the sale probability with the result passed to the control I am trying to SUM

 

[CJ_London]

I presume you mean the footer of the subform not the main form. You need to repeat the calculation

=sum(profit*quantity)

If profit is calculated then include that as well

=sum((price-cost)*quantity)

 

[Mark_]

questions,
1) Do you need the sum to be a running total WHILE you are adding and editing records on the subform?
2) Do you calculate the sum AFTER you have finished entering data on your subform and go to process the parent record?
3) Does the sum need to be referenced elsewhere in your application, such as in reports?

If you answer either 2 or 3 with "Yes", then you may want to create a sum query to return your value. That way you can reuse it.

 

[Bobp3114]

questions,
1) Do you need the sum to be a running total WHILE you are adding and editing records on the subform?
2) Do you calculate the sum AFTER you have finished entering data on your subform and go to process the parent record?
3) Does the sum need to be referenced elsewhere in your application, such as in reports?

If you answer either 2 or 3 with "Yes", then you may want to create a sum query to return your value. That way you can reuse it.

1. Yes
2. No
3. No

 

[Ken Sheridan]

If you return a computed column in the subform's RecordSource query, rather having a computed control per row in the subform you can then simply Sum the computed column from the query, rather than having to repeat the expression in the ControlSource of the control in the subform's footer. The following is an example where the net amount per line item in an order details subform is computed in the query. The NetAmount can then be summed in the subform's footer:

SELECT
    OrderDetails.*,
    [OrderDetails].[UnitPrice] * [OrderDetails].[Quantity] AS NetAmount,
    Nz (BackOrders.Quantity, 0) AS BackOrderQuantity
FROM
    (
        Products
        INNER JOIN OrderDetails ON Products.ProductID = OrderDetails.ProductID
    )
    LEFT JOIN BackOrders ON (OrderDetails.OrderNumber = BackOrders.OrderNumber)
    AND (OrderDetails.ProductID = BackOrders.ProductID)
ORDER BY
    Products.Product;

 

[Bobp3114]

If you return a computed column in the subform's RecordSource query, rather having a computed control per row in the subform you can then simply Sum the computed column from the query, rather than having to repeat the expression in the ControlSource of the control in the subform's footer. The following is an example where the net amount per line item in an order details subform is computed in the query. The NetAmount can then be summed in the subform's footer:

SELECT
    OrderDetails.*,
    [OrderDetails].[UnitPrice] * [OrderDetails].[Quantity] AS NetAmount,
    Nz (BackOrders.Quantity, 0) AS BackOrderQuantity
FROM
    (
        Products
        INNER JOIN OrderDetails ON Products.ProductID = OrderDetails.ProductID
    )
    LEFT JOIN BackOrders ON (OrderDetails.OrderNumber = BackOrders.OrderNumber)
    AND (OrderDetails.ProductID = BackOrders.ProductID)
ORDER BY
    Products.Product;

Thanks Ken
Will digest this and get back to you....

 

[Bobp3114]

You can’t sum a control. You can only sum fields and expressions. What is the control source of [txtTagRecord]?

 

[Bobp3114]

= txtQuantity * txtSellAUD * (txtSaleProbability / 100)
txtQuantity is the amount (number of products)
txtSellAUD is the selling price expressed in Aussie dollars
txtSaleProbability is a percentage that varies according to the progress of the sale

 

[RonPaii]

You can put the sum calculation on a control in the sub form, then reference it in the footer of the main form.

 

[Ken Sheridan]

= txtQuantity * txtSellAUD * (txtSaleProbability / 100)
txtQuantity is the amount (number of products)
txtSellAUD is the selling price expressed in Aussie dollars
txtSaleProbability is a percentage that varies according to the progress of the sale

In that case you'd sum the value like this:

=Sum(txtQuantity * txtSellAUD * (txtSaleProbability / 100))

If you want this to appear in the parent form rather than the subform's footer do as RonPaii has described by hiding the control in the subform's footer and referencing it in the ControlSource of a text box in the parent form. Note that you must use the name of the subform control, i.e. the control in the parent form which houses the subform in the expression. This might or might not be the same as its source form object.

 

[DHookom]

= txtQuantity * txtSellAUD * (txtSaleProbability / 100)
txtQuantity is the amount (number of products)
txtSellAUD is the selling price expressed in Aussie dollars
txtSaleProbability is a percentage that varies according to the progress of the sale

One key to resolving your issue involves the control sources of your "txt" boxes. For instance, if you have a text box txtTotal in your detail section with a control source of:
Name: txtTotal
Control Source: =[Cost] * [Qty] * (1-[Discount])
To Sum this in a form header or footer, you could use:
Name: txtSumTotal
Control Source: =Sum([Cost] * [Qty] * (1-[Discount]))

You can't sum the control =Sum(txtTotal)

 

[Bobp3114]

In that case you'd sum the value like this:

=Sum(txtQuantity * txtSellAUD * (txtSaleProbability / 100))

If you want this to appear in the parent form rather than the subform's footer do as RonPaii has described by hiding the control in the subform's footer and referencing it in the ControlSource of a text box in the parent form. Note that you must use the name of the subform control, i.e. the control in the parent form which houses the subform in the expression. This might or might not be the same as its source form object.

have tried as suggested
=Sum(txtQuantity * txtSellAUD * (txtSaleProbability / 100))...still getting an ERROR message
I have checked names etc. I have pasted direct from your page... no luck
What am I doing wrong???

 

[DHookom]

Are txtQuantity, txtSellAUD, and txtSaleProbability controls?

 

[CJ_London]

Simply, there is something you are not telling us, or not understanding what we are telling you. Suggest upload your db with enough data to replicate the issue