Requery to update data

I have a form with a subform. In the subform I placed a text field that sums
a field minus the sum of another field. In the mail form I place a text
field that shows the answer. When I enter data in the subform and move off
of the record the correct answer shows in the text field of the main form. I
would like to have the answer in the main form to update when I move off of a
field. I have used requery on the after update of the field and also tried
it on the before update of the field. Neither one did anyting. Please
explain what I need to do. I do not want to move off the record just move
off the field and have the data updated.
--
Thank you, Jack

Requerying a form updates the data in the underlying table(s) being displayed.
As a calculated field, this shouldn't be stored in the table, so I wouldn't
expect it to help.

Me.Recalc

will cause all calculated fields to be re-calculated, and should do the job
here. I'd put it in the AfterUpdate event of the textbox you're moving off of.


--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via http://www.accessmonster.com

I am not moving off a textbox I am moving off the field in the subform. I am
using access 2007. Tried recalc on the afterupdate on the field with no luck.
--
Thank you, Jack


"Linq Adams via AccessMonster.com" wrote:

Requerying a form updates the data in the underlying table(s) being displayed.
As a calculated field, this shouldn't be stored in the table, so I wouldn't
expect it to help.

Me.Recalc

will cause all calculated fields to be re-calculated, and should do the job
here. I'd put it in the AfterUpdate event of the textbox you're moving off of.


--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via http://www.accessmonster.com

.

On Thu, 26 Nov 2009 12:23:01 -0800, Jack
wrote:

I have a form with a subform. In the subform I placed a text field that sums
a field minus the sum of another field. In the mail form I place a text
field that shows the answer. When I enter data in the subform and move off
of the record the correct answer shows in the text field of the main form. I
would like to have the answer in the main form to update when I move off of a
field.

It appears that you're trying to store the calculated field in the table.

Don't.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or in the control source of a Form or a Report
textbox.
--

John W. Vinson [MVP]

Thank you for your response. I am storing the calculation in a text box but
what I want to do is to update the calculation when I move off of a field
just like it does when I move off the record.
--
Thank you, Jack


"John W. Vinson" wrote:

On Thu, 26 Nov 2009 12:23:01 -0800, Jack
wrote:

I have a form with a subform. In the subform I placed a text field that sums
a field minus the sum of another field. In the mail form I place a text
field that shows the answer. When I enter data in the subform and move off
of the record the correct answer shows in the text field of the main form. I
would like to have the answer in the main form to update when I move off of a
field.

It appears that you're trying to store the calculated field in the table.

Don't.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or in the control source of a Form or a Report
textbox.
--

John W. Vinson [MVP]
.

On Thu, 26 Nov 2009 22:04:01 -0800, Jack
wrote:

Thank you for your response. I am storing the calculation in a text box

Small but important correction: you're not *storing* the calculation, you're
*displaying* the calculation. Data is stored in tables, and only in tables;
forms and form controls are used to display data, either from tables or from
expressions. Don't confuse these two quite different functions!

but
what I want to do is to update the calculation when I move off of a field
just like it does when I move off the record.

Requery the calculated textbox (not the form!) in the AfterUpdate event of the
control upon which the calculation is based.
--

John W. Vinson [MVP]

"Linq Adams via AccessMonster.com" u28780@uwe wrote in
news:9fb78862a8eeb@uwe:

Me.Recalc

will cause all calculated fields to be re-calculated, and should
do the job here.

Or, you can recalc/refresh just the controls that are dependent on
the value you've just edited. I've never used recalc (instead using
Refresh), so don't know how it differs from Refresh, other than
having no effect on non-calculated fields. I would definitely not
recalc (or refresh) the whole form when I was doing so in a context
in which I know exactly which controls need to have their values
updated.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

John W. Vinson wrote in
:

Requery the calculated textbox (not the form!) in the AfterUpdate
event of the control upon which the calculation is based.

Requery applies only the controls with rowsources, no? Refresh or
Recalc should be sufficient for all other controls.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/