Sum on Form and Table

  • Thread starter Thread starter C.R. Houk
  • Start date Start date
C

C.R. Houk

Having a little problem with a simple function.
I want to have the form provide a sum of several fields
and then save that value with the record on the table. I
have tried changing the Control source to read:
=[SumA]+[SumB]+[SumC]
This provides the sum, however it does not save the sum in
the table when moving to the next record. Any help is
appreciated.

C.R. Houk
charles.houk2<remove>@charleston.af.mil
 
No there is not a Primary Key set on this table. A little
background, the table tracks a weekly fitness test.
Therefor there are multiple entries for each individual,
just with different dates. Also, the date could not be a
key as there are over 50 people per date taking the test.
-----Original Message-----
Does this table have any primary key set?
Having a little problem with a simple function.
I want to have the form provide a sum of several fields
and then save that value with the record on the table. I
have tried changing the Control source to read:
=[SumA]+[SumB]+[SumC]
This provides the sum, however it does not save the sum in
the table when moving to the next record. Any help is
appreciated.

C.R. Houk
charles.houk2<remove>@charleston.af.mil
Click to expand...


.
Click to expand...
 
Generally, it is considered poor design to save any data which can be
calculated. There are exceptions such as when history is required (sales tax
or payroll information), or when the calculations are so intensive that they
require considerable effort or time.

If it is necessary to save the data, you can do it by pushing the
calculation into the control bound to the field in the underlying table. Do
this by putting the calculation in each element, like:

Sub SumA_AfterUpdate()

If Len(Me.SumB) >0 And Len(Me.SumC) >0 Then
Me.ControlD =[SumA]+[SumB]+[SumC]
End If

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Downloads
http://www.datastrat.com
http://www.mvps.org/access
 
If it is poor form, would it be better to do the
calculation somehow in the table itself and just have it
displayed on the form? How would I go about doing this?

Thank you for your help to this point. It is much
appreciated.
-----Original Message-----
Generally, it is considered poor design to save any data which can be
calculated. There are exceptions such as when history is required (sales tax
or payroll information), or when the calculations are so intensive that they
require considerable effort or time.

If it is necessary to save the data, you can do it by pushing the
calculation into the control bound to the field in the underlying table. Do
this by putting the calculation in each element, like:

Sub SumA_AfterUpdate()

If Len(Me.SumB) >0 And Len(Me.SumC) >0 Then
Me.ControlD =[SumA]+[SumB]+[SumC]
End If

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Downloads
http://www.datastrat.com
http://www.mvps.org/access


Having a little problem with a simple function.
I want to have the form provide a sum of several fields
and then save that value with the record on the table. I
have tried changing the Control source to read:
=[SumA]+[SumB]+[SumC]
This provides the sum, however it does not save the sum in
the table when moving to the next record. Any help is
appreciated.

C.R. Houk
charles.houk2<remove>@charleston.af.mil
Click to expand...


.
Click to expand...
 
Here is how I did it:
3 fields displayed on the form
A, B, C properties of which refer to columns T.A, T.B,
and T.C in a table.

Now C is the one which is based on calculation of A and B
say
C = A+B

Right click Box A - build event


Private Sub A_AfterUpdate()
[C].Value = [A].Value + .Value
End Sub

Private Sub A_BeforeUpdate(Cancel As Integer)
[C].Value = [A].Value + .Value
End Sub

Right click Box B - build event


Private Sub B_AfterUpdate()
[C].Value = [A].Value + .Value
End Sub

Private Sub B_BeforeUpdate(Cancel As Integer)
[C].Value = [A].Value + .Value
End Sub

Hope it works for you! It did for me.

VK
PS Am new to all this so do test it out proper!
 
If it is poor form, would it be better to do the
calculation somehow in the table itself and just have it
displayed on the form? How would I go about doing this?
Click to expand...

What Arvin is saying is that you should never do calculations in a
Table, and (almost) never store calculated values in a Table AT ALL.

Instead, use a Query. Do the calculations *in the query* and display
them on the form. Simply put the expression in a vacant Field cell in
the query.
 
Back
Top