Calculating a subtotal - Should be easy?

[Mark-BES]

Hi all,
I have a subform within my form for quotations (see attached).

A user will manually type in "Repair Cost" and "Accessory Cost". The subtotal field is calculated using this expression =Nz([Repair Cost])+Nz([Acc Cost]) in the Control Source box
This is all Ok.

I want to subtotal these on the main form then work out the VAT and final total.

My problem is, No matter what expression I seem to use on the main forms subtotal, it will not work.

Here is an example of what I have tried:

=Sum([SubTotal])
=Sum[Forms]![TblQuotation subform].[SubTotal]
=Sum[TblQuotation subform].[Form]![SubTotal]

Nothing seems to work. Any pointers would be greatly received. Many thanks.

 

[RuralGuy]

Maybe this will help: Refer to Form and Subform properties and controls

 

[Mark-BES]

Thanks RG, that link will be useful for future reference.

I am still struggling with this very basic problem. I have cut out all the unnecessary bits to the underpinning problem.

I can perform equations when the control is bound to a field, but have never yet had to calculate a control to an unbound control. Its as if my expression does not recognise my "SubTotal" control.

Please see new attachment for details.

Any help is really appreciated. I have browsed help files and search but to no avail.

 

[RuralGuy]

Let's give it a shot!

In the Control Source of the TextBox on the MainForm put the following:

=[TblQuotation subform].[Form]![Total]

And let me know what happens.

 

[Mark-BES]

Sorry, I might have confused you in my last post.
The link and expression you sent me has helped me with the final part. Thats great thanks!

It is the addition of all the "SubTotals" that is not currently working. see attached. This scr shot is of the subform in form view. I will later of course use it in datasheet view but need the exprression to add these subtotals together.

Hope this helps. Sorry for the round-about of a description. Its been a long day!

 

[RuralGuy]

In my first project I let Access do the totaling for me as you are attempting and found the results unacceptable. The interface ran like it was well...uhh interpreted! As a result I took a different approach and have never looked back. When I have a need for a SubForm to supply a value to a parent form I use a SubRoutine I name UpdateParent. In the AfterUpdate event of any control that would affect the value I call the UpdateParent code. The code looks something like:

Private Sub UpdateParent()
'-- Update the Parent Form txtBox for this subForm
Dim curValue As Currency

curValue = 0            '-- Default to $0.00
'-- Sum the DD_Amount for this subform
With Me.RecordsetClone
    If .RecordCount Then  'If records exist
        .MoveFirst        'Resets position
        Do While Not .EOF
            curValue = curValue + !HHR_Total
            .MoveNext
        Loop
    End If
End With
     
'-- Show the Sum on the Parent Form
Me.Parent.txtHHRTotal = curValue

End Sub

I copied this code from one of my projects but you get the idea. The code is fast and the update seems instantanious. The Access priority for calculated controls is pretty low but eventually Access gets around to updating the calculated controls. With this scheme I'm in charge of the timing and so far I've not been disappointed.

 

[Mark-BES]

Is it really as complicated as that? Is this how access is designed?
Eg:

To calculate a figure from a bound control:
=sum[subtotal]

To calculate a figure from a calculated control:
Code:
Private Sub UpdateParent()
'-- Update the Parent Form txtBox for this subForm
Dim curValue As Currency

curValue = 0 '-- Default to $0.00
'-- Sum the DD_Amount for this subform
With Me.RecordsetClone
If .RecordCount Then 'If records exist
.MoveFirst 'Resets position
Do While Not .EOF
curValue = curValue + !HHR_Total
.MoveNext
Loop
End If
End With

'-- Show the Sum on the Parent Form
Me.Parent.txtHHRTotal = curValue

End Sub

forgive my limited knowledge of access, i'm not yet SQL proficient. Even so, seems a long way round.

 

[Mark-BES]

Cracked it!!!

=Sum(Nz([Repair Cost])+Nz([Acc Cost]))

I did not enter brackets to the expression

Thanks all for your input. As allways a great forum!!!!