Another Calculation Issue

[owizard]

Hi,

I have a form with the following fields.

Count1, count2, count3, Counted_Stock, Counted_Value, Ave_Cost

Counted_Stock =Nz([Count1])+Nz([Count2])+Nz([Count3])

Counted_Value =[Counted_Stock]*[Ave_Cost]

I need to calculate the aggregate counted_Value for all the records in the table. However, the following formulas don't seem to work.

Total_Value =Sum([Counted_Value])
Total_Value =Sum([Counted_Stock]*[Ave_Cost])

Any info will be deeply appreciated. Thanks.

 

[LittleSmudge]

First point:
You don't have a form with any fields at all FIELDS are in Tables.

So are you talking about Fields in a Table or are you talking about CONTROLS on a Form - it makes a lot of difference to the answer.

Second point:
If you do mean Fields - you should NOT be storing Counted_Stock and Counted_Value in fields in the table. They are 'calculated values' and therefore should be CALCULATED - NOT stored.

Third point:
Where are you using
Total_Value =Sum([Counted_Value])
Total_Value =Sum([Counted_Stock]*[Ave_Cost])

The 'right' solution will depend on what you are doing.
Is Total_Value a variable in code ?
Is Total_Value a control on the form ?
If it is a control - Which section of the form is it in ?







G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[owizard]

Sorry for the loose language.MY DBMS knowledge is quite rustic at the moment.

Total_Value is a control in the form. It is in the detail section.

Counted_Stock, Counted_Value are calculated values. They are not stored in the table.

Thanks.

 

[LittleSmudge]

From some of what you've said I'd assume that the form is in Continuous View.
But I'm not convinced - because other things suggest single record view.

Which ?

And do you want the totals to update as soon as and of the values change - or just when you tell it to change ?





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[LittleSmudge]

Try this

For each of the control where the user can add data you put an [Event Procedure] in the After_Update box

Private Sub Count1_AfterUpdate()  ' for Example

' This line saves the change to the underlying table
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 
' This line called the workhorse proc.
Call UpdateTotals

End Sub

Repeat for the other controls
.. ..


And then have this proc that they all use

Private Sub UpdateTables()

Dim rst As ADODB.REcordSet
Set rst = New ADODB.RecordSet
rst.ActiveConnection = CurrentProject.Connection

rst.Open "SELECT Sum(([Count1] + [Count2] + [Count3]) * [Ave_Cost]) " _
       & "AS GTotal FROM tblTableName"
If rst.EOF Then
    Total_Value = 0
Else
    Total_Value  = rst!GTotal
End If
rst.Close
Set rst = Nothing
End Sub

'ope-that-'elps.


G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[owizard]

Hi,

Thanks. I tried the first procedure in the After_update box. However, I am a getting a "sub not defined" error whenever I enter values.

Do you think that the name of the second procedure should be "UpdateTotals" instead of "UpdateTables"?

Thanks a lot.

 

[LittleSmudge]

Yes - pardon the typo.

G

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.