Summing fields on a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thought it would be straightforward, but I'm doing something wrong. I have a
field on a form that I would like to be a summary field for some number
fields on a form. I have tried this expression:
Sum(dimension1+dimension2+dimension3+dimension4+dimension5)
and put it in the 'Got Focus' event and the 'On Exit' event, and both leave
the field blank. I also changed to Control Source to the above expression
and it gives me a number that is MUCH larger than the fields I want to sum--
is it possible that it is summing those fields across ALL records instead of
breaking after it sums the fields just for that particular record? Don't
know, since this large number won't dynamcally change on test records that
were in the database before this code was put in. I just need a summary
field that will dynamically change when the numbers that it is summing change.

Thanks!
 
yes, it's summing the total of those fields in all the records. change your
expression in the ControlSource property to

=FieldOneName + FieldTwoName + FieldThreeName + FieldFourName

substituting the correct field names, of course, for all the fields you want
to include in the total.

you should not need to run any code to requery this calculated control. when
you change any value in any of the controls, the calculation will
automatically update.

note: the above expression will return "nothing" when one of the controls
is null. to return a value even when one or more controls is null, use this
expression instead, as

=Nz(FieldOneName, 0) + Nz(FieldTwoName, 0) + Nz(FieldThreeName, 0) +
Nz(FieldFourName, 0)

caution - the above expression is on more than one line only because of line
wrap; in the ControlSource property, it will all go on one line.

hth
 
Pat said:
Thought it would be straightforward, but I'm doing something wrong.
I have a field on a form that I would like to be a summary field for
some number fields on a form. I have tried this expression:
Sum(dimension1+dimension2+dimension3+dimension4+dimension5)
Click to expand...

There is no need to store these values in a field and several reasons why
you should not.

Better to use a query with a calculated field

TotalValue: dimension1+dimension2+dimension3+dimension4+dimension5

If these are dimensions like length, width, and height there may be a valid
reason for having them all in one record. If not then the values should be
in a separate table related to your main table.

If there is a remote possibility that there might be a dimension6 some day
then there is about a 99.999% chance you are doing it wrong.
 
Hi,

I tried this code and it works great on the form. However, it is not
entering the value into the field in the table. If I use the field name as
the ControlSource, data entered on the form appears on the table, but if the
ControlSource is replaced by the below expression, it no longer does. I'm
probably missing something pretty basic here, but if you're using an
expression as ControlSource, how do you get the data to appear in a field in
that record in the table?

Thanks.
 
Pat said:
Hi,

I tried this code and it works great on the form. However, it is not
entering the value into the field in the table. If I use the field
name as the ControlSource, data entered on the form appears on the
table, but if the ControlSource is replaced by the below expression,
it no longer does. I'm probably missing something pretty basic here,
but if you're using an expression as ControlSource, how do you get
the data to appear in a field in that record in the table?

Thanks.
Click to expand...

It's been mentioned a couple of times in this thread.
Basically:
It is a *bad* idea to store such values in a table.
It is a good idea to use a calculated field in a query for the values and
use that query for all reports and forms.

Me!TotalFieldName = me!field1 + me!field2 + me!field3 + ....
in an event is how it would be done.

NOTE ALSO.
We still don't know enough about what you are doing to say that having
similar named fields is acceptable.

Doing things the way Access and relational databases like will allow you to
get much more done with far fewer problems and far less code than doing it
wrong.
 
no, you're not missing anything. a calculated control is, by definition,
unbound - so the value it displays will not be saved into the form's
underlying table.

you should not save calculated values in a table, unless you have a valid
business reason for doing so. normally, you save raw data - unit price and
quantity, for instance - and calculate/display totals whenever you need
them, in forms, reports, export queries, etc.

having said that, if you *must* save the calculated value in the table
underlying the form, do it as follows:

add a procedure to the form's module, as

Private Sub isTotal()

With Me
.ControlName = Nz(.FieldOneName, 0) + Nz(.FieldTwoName, 0) _
+ Nz(.FieldThreeName, 0) + Nz(.FieldFourName, 0)
End With

End Sub

call the procedure in the AfterUpdate event of each control that is being
used in the math expression (but *not* in the control you're updating!), as

Private Sub ControlName_AfterUpdate()

isTotal

End Sub

each time a value is entered, or edited, in one of those controls, the total
will be updated.
you can use a macro instead, if you prefer. call it from each control's
AfterUpdate event (same as the VBA code), and use the SetValue action in the
macro - you can read up on it in Access Help.

hth
 
Back
Top