Running Sum Question

[That Crazy Hockey Dood]

Good Morning Folks..

I have searched over the last few days to see if what I want to do is
possible but I need the guidance of the MVP's on this one. I have a form
that contains several text boxes. The text boxes are for data entry which
will be saved to the table that is the control source for the data.

I need to know if it is possible to keep a running total on a form as data
is entered.

For example: I have box1, box2, box3 and a boxTotal. Can boxTotal keep
count of numbers entered in box1, box2 and box3 even though it is not yet in
saved to the bound table? If so then can the data in boxTotal be saved to
the table bound to the form?

I know this sounds pretty "hairy" and sort of confusing. As I typed this I
started thinking I may need two different boxes. One to run the sum and one
that would take the data from the running sum to write back to the original
table.

Please let me know if you have any questions about this or if I need to make
something a little clearer then the mud I have given you.

Thanks,
Jim

 

[Douglas J. Steele]

While boxTotal could be written back to the table, it shouldn't be. As
fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."
`
To show the running sum, set the ControlSource property of bobTotal to

=CLng(Nz([box1],0))+CLng(Nz([box2],0))+CLng(Nz([box3],0))

 

[That Crazy Hockey Dood]

Douglas..

I appreciate the assistance on this. It works just the way I want it to on
the form. My intent on the form is to allow for the end user to see a total
only for the purpose of ensuring they have not made a keying error prior to
saving the record. Also, I can agree with John's thoughts as well about
storing that figure being detrimental if a change to a record, on the table,
is ever made. Running a simple query to do the summing is much easier then
using a container that could be "flawed".

I am interested in learning more and wondered if you could give me some
insight on how to use CLng & Nz successfully. I like the help from this
newsgroup but I would love to know this stuff as well. I am still a bit of a
newbie with Access.

Thanks,
Jim

--
If it works then you are doing something right!!

While boxTotal could be written back to the table, it shouldn't be. As
fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."
`
To show the running sum, set the ControlSource property of bobTotal to

=CLng(Nz([box1],0))+CLng(Nz([box2],0))+CLng(Nz([box3],0))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Good Morning Folks..

I have searched over the last few days to see if what I want to do is
possible but I need the guidance of the MVP's on this one. I have a form
that contains several text boxes. The text boxes are for data entry which
will be saved to the table that is the control source for the data.

I need to know if it is possible to keep a running total on a form as data
is entered.

For example: I have box1, box2, box3 and a boxTotal. Can boxTotal keep
count of numbers entered in box1, box2 and box3 even though it is not yet
in
saved to the bound table? If so then can the data in boxTotal be saved to
the table bound to the form?

I know this sounds pretty "hairy" and sort of confusing. As I typed this
I
started thinking I may need two different boxes. One to run the sum and
one
that would take the data from the running sum to write back to the
original
table.

Please let me know if you have any questions about this or if I need to
make
something a little clearer then the mud I have given you.

Thanks,
Jim

 

[Douglas J. Steele]

Pretty much any time a field that's supposed to contain a number might have
a Null in it, you need to use Nz to convert the Null to a 0. If you don't,
doing arithmetic on it probably won't work.

To be honest, I'm not sure why CLng was required in this case. When I did my
initial testing, I used

=Nz([box1],0)+Nz([box2],0)+Nz([box3],0)

When I put 2 in box1 and 5 in box2, I ended up with 25 in boxTotal.
Consequently, I used CLng to ensure that the values were correctly treated
as numeric.`


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas..

I appreciate the assistance on this. It works just the way I want it to
on
the form. My intent on the form is to allow for the end user to see a
total
only for the purpose of ensuring they have not made a keying error prior
to
saving the record. Also, I can agree with John's thoughts as well about
storing that figure being detrimental if a change to a record, on the
table,
is ever made. Running a simple query to do the summing is much easier
then
using a container that could be "flawed".

I am interested in learning more and wondered if you could give me some
insight on how to use CLng & Nz successfully. I like the help from this
newsgroup but I would love to know this stuff as well. I am still a bit
of a
newbie with Access.

Thanks,
Jim

--
If it works then you are doing something right!!

While boxTotal could be written back to the table, it shouldn't be. As
fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable
calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed,
making
the value WRONG."
`
To show the running sum, set the ControlSource property of bobTotal to

=CLng(Nz([box1],0))+CLng(Nz([box2],0))+CLng(Nz([box3],0))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"That Crazy Hockey Dood" <[email protected]>
wrote in message

Good Morning Folks..

I have searched over the last few days to see if what I want to do is
possible but I need the guidance of the MVP's on this one. I have a
form
that contains several text boxes. The text boxes are for data entry
which
will be saved to the table that is the control source for the data.

I need to know if it is possible to keep a running total on a form as
data
is entered.

For example: I have box1, box2, box3 and a boxTotal. Can boxTotal
keep
count of numbers entered in box1, box2 and box3 even though it is not
yet
in
saved to the bound table? If so then can the data in boxTotal be saved
to
the table bound to the form?

I know this sounds pretty "hairy" and sort of confusing. As I typed
this
I
started thinking I may need two different boxes. One to run the sum
and
one
that would take the data from the running sum to write back to the
original
table.

Please let me know if you have any questions about this or if I need to
make
something a little clearer then the mud I have given you.

Thanks,
Jim