Have Fields Automatically Calculate

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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"NickW" wrote in message
...
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.

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

Arvin Meyer [MVP] wrote:
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.

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

"John Spencer" wrote:

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

Arvin Meyer [MVP] wrote:
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.

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

NickW wrote:
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

"John Spencer" wrote:

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

Arvin Meyer [MVP] wrote:
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.

John Spencer wrote:
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

NickW wrote:
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

"John Spencer" wrote:

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

Arvin Meyer [MVP] wrote:
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.

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!!

"Arvin Meyer [MVP]" wrote:

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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"NickW" wrote in message
...
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.

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

"Stockwell43" wrote in message
news
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!!

"Arvin Meyer [MVP]" wrote:

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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"NickW" wrote in message
...
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.

Works great!!!

Thank you Arvin, your was most appreciate!!!

"Arvin Meyer [MVP]" wrote:

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

"Stockwell43" wrote in message
news
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!!

"Arvin Meyer [MVP]" wrote:

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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"NickW" wrote in message
...
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.

By the way, it's a very useful piece you created and quite helpful.

Thanks!

"Arvin Meyer [MVP]" wrote:

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

"Stockwell43" wrote in message
news
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!!

"Arvin Meyer [MVP]" wrote:

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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"NickW" wrote in message
...
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.