Sum Function

[dr223]

Hallo,

Is there a way of adding a text box on the Form Footer of a subform,
find the sum of a field on the subform (fee_Amount) i.e., =Sum([fee_Amount]) and set the default view to Dataset and be able to view it when you are in the Form view. Or is there a way around it rather than setting the default view property to Continuos Forms.

This is mainly because I have used list boxes on my subform when I use Dataset view, the Total Amount is displayed but the subform form view is so unpleasant.

Thanks

 

[boblarson]

You can use the sum text box on the subform in datasheet view, you would

1. put the text box in the subform footer using =Sum([YourFieldNameHere])

2. Put a text box on the main form and refer to the sufborm control by using:
=[YourSubFormCONTAINERNameHere]![Form]![YourSumTextBoxNameHere]

 

[dr223]

Hi Boblarson,

Tried it but never works, the name of my subform containing the footer is FeesDetails and the Sum Text box is called Total_Amount (which is in the Form Footer of the subform)


=[FeesDetails]![Form]![Total_Amount]

The above code I inserted in the control source of the new text box in the main form. It displays #name? error when I am in the form view.

Secondly, I want the result (Total Amount) to be displayed in the subform at the end of the Fee_Amount field.


Thanks

 

[boblarson]

Hi Boblarson,

Tried it but never works, the name of my subform containing the footer is FeesDetails and the Sum Text box is called Total_Amount (which is in the Form Footer of the subform)


=[FeesDetails]![Form]![Total_Amount]

The above code I inserted in the control source of the new text box in the main form. It displays #name? error when I am in the form view.

Secondly, I want the result (Total Amount) to be displayed in the subform at the end of the Fee_Amount field.


Thanks

If it doesn't work then your subform CONTAINER (not the subform) that houses the subform on the main form is likely NOT called FeesDetails. You need to use the name of the Container control and not the subform name, although it is okay if both the container and the subform are named the same.

Also, you can NOT display it on the subform in datasheet view. It just is not possible. So, you can either move to continuous form view (you can, if you work at it, format it so it LOOKS like a datasheet) or single view in order to display it directly on the subform. If you want datasheet view then you HAVE to do it the way I showed.

 

[dr223]

Boblarson,

Do you mean putting this code in the control source of the new text field on the main form,

=[YourSubFormCONTAINERNameHere]![Form]![YourSumTextBoxNameHere]

secondly will the total amount be displayed on this field, in the main form.

Thanks