Have Fields Automatically Calculate

  • Thread starter Thread starter NickW
  • Start date Start date
N

NickW

I would like to have a field add numbers from two other fields. so
Total= subtotal1+subtotal2
I want this done at the table level. I was able to acheive this at the
form, but then it wasn't writing the value to the table.
 
Since a query, form, or report can re-calculate this at any time, there is
no reason to store the calculated value. It is also a violation of database
normalization rules.
 
Plus Access does not support this at the table level. If you really feel you
HAVE to do this, you will have to do it at the data entry level of a form.

If someone ever modifies the component parts using a query or direct entry
into a table (or a query) the total will be inaccurate. So Arvin Meyer's
suggestion of recalculating the total when needed is the correct way to handle
this.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Okay. Well, i was able to get the data to show in a text box by having the
control source be the sum of two fields, but then how do I write this to the
table? When I went into the table, there was no data in the product field
 
Again, I stress that you should not record the total in the table at all.

But, since you seem determined to do so,

REMOVE the formula from the textbox.
Set the textbox control source to the name of the field you want to store the
amount.

On the two controls that contain the information you are adding together, use
the after update event and add code that looks something like the following.

Private Sub tAmount1_AfterUpdate()
Me.someTotalControl = Me.tAmount1 + Me.tAmount2
End Sub

Private Sub tAmount2_AfterUpdate()
Me.someTotalControl = Me.tAmount1 + Me.tAmount2
End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
John said:
Again, I stress that you should not record the total in the table at all.

But, since you seem determined to do so,

REMOVE the formula from the textbox.
Set the textbox control source to the name of the field you want to
store the amount.

On the two controls that contain the information you are adding
together, use the after update event and add code that looks something
like the following.

Private Sub tAmount1_AfterUpdate()
Me.someTotalControl = Me.tAmount1 + Me.tAmount2
End Sub

Private Sub tAmount2_AfterUpdate()
Me.someTotalControl = Me.tAmount1 + Me.tAmount2
End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...

OP will probably be back later asking why the stored values aren't
accurate :-)

gls858
 
Hi Arvin,

I was wondering if you could help me with a situation. I downloaded your
EmailSenate2k database and notice if I selction an email address and click
the email button it works fine but if I deselect that same email address so
nothing is selected I get an error. Is there a fix so if the user selects an
email address and decides that was not the right one and deselects they won't
get the error message? I'd like to use this in my database but afraid of
users getting the error and messing around with the code.

Thanks!!
 
I see your problem. My code assumes that there will always be someone to
send the email to. Adding an If ... Then statement will fix that:

Private Sub lstMailTo_Click()
Dim varItem As Variant
Dim strList As String

With Me!lstMailTo
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ";"
Next varItem
If Len(strList) > 1 Then
strList = Left$(strList, Len(strList) - 1)
End If
Me!txtSelected = strList
End If
End With
End Sub

This is what was added:

If Len(strList) > 1 Then

End If
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
 
This total then needs to be put into a report page which will be a contract.
Can a report add fields together the same as a form? That would solve this
issue
 
Yes, you can calculate the sum of the two numbers in a report, the same
way you can on a form.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
NickW said:
This total then needs to be put into a report page which will be a
contract.
Can a report add fields together the same as a form? That would solve
this
issue
Click to expand...

Nick,

Calculations are close to instantaneous in a query, or on either a form or
report. The only time calculations are ever justifiably saved to a table is
when a history is required (for instance the selling price of a car) when
the individual elements are not saved (yours are); or also if the
calculations are so processor intensive, that saving the results would save
time.
 
Back
Top