Updating fields, Adding Fields?

[Guest]

I have fields bags1, bags2, bags3, and Total bags on a form. I can make a
text box to calculate the total of 3 fields by using
"=Nz([bags1])+Nz([bags2])+Nz([bags3])" in the control source. How do I take
the calculated number from the text box to automatically place the number in
field "Total bags" without changing the control source of "Total bags"

Any help will be greatly appreciated!!

 

[Guest]

Can't quite envision your form, but it sounds like the text box you describe
that calculates the total is a different field than Total Bags. If so, then
there are a number of ways to update the Total Bags field with whatever total
was calculated by the text box. You just need to attach the following to
some event on the form (e.g., On Change ?) that is triggered after the
calculation has been done.

Me.Total Bags = Me.(name of the textbox)

Or , if there are no other logical events that occur, just put the above
into the OnClick event of a command button.

 

[John W. Vinson]

I have fields bags1, bags2, bags3, and Total bags on a form. I can make a
text box to calculate the total of 3 fields by using
"=Nz([bags1])+Nz([bags2])+Nz([bags3])" in the control source. How do I take
the calculated number from the text box to automatically place the number in
field "Total bags" without changing the control source of "Total bags"

Any help will be greatly appreciated!!

You're making two serious design errors: repeating fields in your table and
storing calculated data.

If you have three bags... someday you'll have four. Count on it. What will you
do then? Redesign your table, all your forms, all your reports, all your
code!? Surely not!

Instead, use Access as it is designed - as a relational database. If there are
three bags in a Shipment (or Order, or Sale, or whatever this table's entity
might be) use *two* tables in a one to many relationship. The second table
would have a link to the Shipment (...) table's Primary Key and a BagCount
field; for three bags you'ld enter three *records* using a Subform.

For the total, just calculate it on demand by putting a textbox in the subform
Footer

=Sum([BagCount])

Don't store it anywhere - if you change a BagCount value the stored sum WILL
BE WRONG with no easy way to detect it. Just recalculate it as needed.

John W. Vinson [MVP]