Calculated field

[Dudley]

I have a sub-form which is linked to the main form on control EnvelopeNumber
, and a sub-sub-form which is linked to the sub-form on control ShareClass. I
want control AggregateNumber in the sub-form to be the total of numbers
entered in the ShareNumber control in the sub-sub-form. I assume that I
should enter code in On Exit in the ShareNumber control, so that the total is
updated whenever the user adds or amends a record, but the code is beyond my
knowledge. Can anyone advise please?

Thanks
Dudley

 

[Marshall Barton]

I have a sub-form which is linked to the main form on control EnvelopeNumber
, and a sub-sub-form which is linked to the sub-form on control ShareClass. I
want control AggregateNumber in the sub-form to be the total of numbers
entered in the ShareNumber control in the sub-sub-form. I assume that I
should enter code in On Exit in the ShareNumber control, so that the total is
updated whenever the user adds or amends a record, but the code is beyond my
knowledge.

I don't think using code is the way to go. The standard way
to do that is to put a (hidden?) text box (named txtTotal)
in the subsubform's header or footer section with an
expression like =Sum([ShareNumber field]) to calculate the
total. Note that [ShareNumber field] must be the name of
the record source field, not the name of a control.

With that in place, your AggregateNumber text box on the
subform can use an expression like:
=[subsubform control name].Form.txtTotal

 

[Dudley]

Thanks very much for your help. That works, although the total only updates
when you move to another record. Is it possible to make it update when the
user exits the ShareNumber field in the sub sub form?

Dudley

I have a sub-form which is linked to the main form on control EnvelopeNumber
, and a sub-sub-form which is linked to the sub-form on control ShareClass. I
want control AggregateNumber in the sub-form to be the total of numbers
entered in the ShareNumber control in the sub-sub-form. I assume that I
should enter code in On Exit in the ShareNumber control, so that the total is
updated whenever the user adds or amends a record, but the code is beyond my
knowledge.

I don't think using code is the way to go. The standard way
to do that is to put a (hidden?) text box (named txtTotal)
in the subsubform's header or footer section with an
expression like =Sum([ShareNumber field]) to calculate the
total. Note that [ShareNumber field] must be the name of
the record source field, not the name of a control.

With that in place, your AggregateNumber text box on the
subform can use an expression like:
=[subsubform control name].Form.txtTotal

 

[Marshall Barton]

The record with the edited ShareNumber field has to be saved
before Access can recalculate the value. If you force the
record with the new value to be saved, then you take away
the ability for users to hit the Esc key to undo any/all
edits to the record.

I think that's a very good reason for things to work the way
they do and why I think you should not try to "make it
update when the user exits the ShareNumber field".

If you insist on doing what you ask, the code in the
ShareNumber field's text box's AfterUpdate event would be:
Me.Dirty = False
--
Marsh
MVP [MS Access]

Thanks very much for your help. That works, although the total only updates
when you move to another record. Is it possible to make it update when the
user exits the ShareNumber field in the sub sub form?

I have a sub-form which is linked to the main form on control EnvelopeNumber
, and a sub-sub-form which is linked to the sub-form on control ShareClass. I
want control AggregateNumber in the sub-form to be the total of numbers
entered in the ShareNumber control in the sub-sub-form. I assume that I
should enter code in On Exit in the ShareNumber control, so that the total is
updated whenever the user adds or amends a record, but the code is beyond my
knowledge.

I don't think using code is the way to go. The standard way
to do that is to put a (hidden?) text box (named txtTotal)
in the subsubform's header or footer section with an
expression like =Sum([ShareNumber field]) to calculate the
total. Note that [ShareNumber field] must be the name of
the record source field, not the name of a control.

With that in place, your AggregateNumber text box on the
subform can use an expression like:
=[subsubform control name].Form.txtTotal

 

[Dudley]

Thanks very much. I would say that I do not agree with you on not saving. It
is fine if you understand how undo works in Access, but I have generally
found that it does somethng I do not want, so I have adopted a rule that undo
is fine in Word, but in Access it is much safer to manually correct any
typos. However the main reason is that saving on data entry means that when
the last shareholder is entered, if the aggregate total is incorrect, the
user is immediately alerted to check that he has made an error in entering
share numbers - which is why I want to make Aggregate Total a calculated
field.

Thanks
Dudley

The record with the edited ShareNumber field has to be saved
before Access can recalculate the value. If you force the
record with the new value to be saved, then you take away
the ability for users to hit the Esc key to undo any/all
edits to the record.

I think that's a very good reason for things to work the way
they do and why I think you should not try to "make it
update when the user exits the ShareNumber field".

If you insist on doing what you ask, the code in the
ShareNumber field's text box's AfterUpdate event would be:
Me.Dirty = False
--
Marsh
MVP [MS Access]

Thanks very much for your help. That works, although the total only updates
when you move to another record. Is it possible to make it update when the
user exits the ShareNumber field in the sub sub form?

Dudley wrote:
I have a sub-form which is linked to the main form on control EnvelopeNumber
, and a sub-sub-form which is linked to the sub-form on control ShareClass. I
want control AggregateNumber in the sub-form to be the total of numbers
entered in the ShareNumber control in the sub-sub-form. I assume that I
should enter code in On Exit in the ShareNumber control, so that the total is
updated whenever the user adds or amends a record, but the code is beyond my
knowledge.


I don't think using code is the way to go. The standard way
to do that is to put a (hidden?) text box (named txtTotal)
in the subsubform's header or footer section with an
expression like =Sum([ShareNumber field]) to calculate the
total. Note that [ShareNumber field] must be the name of
the record source field, not the name of a control.

With that in place, your AggregateNumber text box on the
subform can use an expression like:
=[subsubform control name].Form.txtTotal

 

[Marshall Barton]

As long as you and your users agree on a strategy (whether
standard or not) I can't argue against your strategy.

Did saving the record make it work the way you wanted?
--
Marsh
MVP [MS Access]

Thanks very much. I would say that I do not agree with you on not saving. It
is fine if you understand how undo works in Access, but I have generally
found that it does somethng I do not want, so I have adopted a rule that undo
is fine in Word, but in Access it is much safer to manually correct any
typos. However the main reason is that saving on data entry means that when
the last shareholder is entered, if the aggregate total is incorrect, the
user is immediately alerted to check that he has made an error in entering
share numbers - which is why I want to make Aggregate Total a calculated
field.

The record with the edited ShareNumber field has to be saved
before Access can recalculate the value. If you force the
record with the new value to be saved, then you take away
the ability for users to hit the Esc key to undo any/all
edits to the record.

I think that's a very good reason for things to work the way
they do and why I think you should not try to "make it
update when the user exits the ShareNumber field".

If you insist on doing what you ask, the code in the
ShareNumber field's text box's AfterUpdate event would be:
Me.Dirty = False

Thanks very much for your help. That works, although the total only updates
when you move to another record. Is it possible to make it update when the
user exits the ShareNumber field in the sub sub form?


:
Dudley wrote:
I have a sub-form which is linked to the main form on control EnvelopeNumber
, and a sub-sub-form which is linked to the sub-form on control ShareClass. I
want control AggregateNumber in the sub-form to be the total of numbers
entered in the ShareNumber control in the sub-sub-form. I assume that I
should enter code in On Exit in the ShareNumber control, so that the total is
updated whenever the user adds or amends a record, but the code is beyond my
knowledge.


I don't think using code is the way to go. The standard way
to do that is to put a (hidden?) text box (named txtTotal)
in the subsubform's header or footer section with an
expression like =Sum([ShareNumber field]) to calculate the
total. Note that [ShareNumber field] must be the name of
the record source field, not the name of a control.

With that in place, your AggregateNumber text box on the
subform can use an expression like:
=[subsubform control name].Form.txtTotal

 

[Dudley]

It worked but I found I had to put the code in the ShareNumber control's
After Update rather than the text box's.

Thanks very much for your help.
Dudley

As long as you and your users agree on a strategy (whether
standard or not) I can't argue against your strategy.

Did saving the record make it work the way you wanted?
--
Marsh
MVP [MS Access]

Thanks very much. I would say that I do not agree with you on not saving. It
is fine if you understand how undo works in Access, but I have generally
found that it does somethng I do not want, so I have adopted a rule that undo
is fine in Word, but in Access it is much safer to manually correct any
typos. However the main reason is that saving on data entry means that when
the last shareholder is entered, if the aggregate total is incorrect, the
user is immediately alerted to check that he has made an error in entering
share numbers - which is why I want to make Aggregate Total a calculated
field.

The record with the edited ShareNumber field has to be saved
before Access can recalculate the value. If you force the
record with the new value to be saved, then you take away
the ability for users to hit the Esc key to undo any/all
edits to the record.

I think that's a very good reason for things to work the way
they do and why I think you should not try to "make it
update when the user exits the ShareNumber field".

If you insist on doing what you ask, the code in the
ShareNumber field's text box's AfterUpdate event would be:
Me.Dirty = False


Dudley wrote:
Thanks very much for your help. That works, although the total only updates
when you move to another record. Is it possible to make it update when the
user exits the ShareNumber field in the sub sub form?


:
Dudley wrote:
I have a sub-form which is linked to the main form on control EnvelopeNumber
, and a sub-sub-form which is linked to the sub-form on control ShareClass. I
want control AggregateNumber in the sub-form to be the total of numbers
entered in the ShareNumber control in the sub-sub-form. I assume that I
should enter code in On Exit in the ShareNumber control, so that the total is
updated whenever the user adds or amends a record, but the code is beyond my
knowledge.


I don't think using code is the way to go. The standard way
to do that is to put a (hidden?) text box (named txtTotal)
in the subsubform's header or footer section with an
expression like =Sum([ShareNumber field]) to calculate the
total. Note that [ShareNumber field] must be the name of
the record source field, not the name of a control.

With that in place, your AggregateNumber text box on the
subform can use an expression like:
=[subsubform control name].Form.txtTotal

 

[Dudley]

I have just noticed one problem. It works fine to give the correct figure in
the text box, but I cannot find how to make it update the AggregateNumber
control.

Thanks
Dudley

As long as you and your users agree on a strategy (whether
standard or not) I can't argue against your strategy.

Did saving the record make it work the way you wanted?
--
Marsh
MVP [MS Access]

Thanks very much. I would say that I do not agree with you on not saving. It
is fine if you understand how undo works in Access, but I have generally
found that it does somethng I do not want, so I have adopted a rule that undo
is fine in Word, but in Access it is much safer to manually correct any
typos. However the main reason is that saving on data entry means that when
the last shareholder is entered, if the aggregate total is incorrect, the
user is immediately alerted to check that he has made an error in entering
share numbers - which is why I want to make Aggregate Total a calculated
field.

The record with the edited ShareNumber field has to be saved
before Access can recalculate the value. If you force the
record with the new value to be saved, then you take away
the ability for users to hit the Esc key to undo any/all
edits to the record.

I think that's a very good reason for things to work the way
they do and why I think you should not try to "make it
update when the user exits the ShareNumber field".

If you insist on doing what you ask, the code in the
ShareNumber field's text box's AfterUpdate event would be:
Me.Dirty = False


Dudley wrote:
Thanks very much for your help. That works, although the total only updates
when you move to another record. Is it possible to make it update when the
user exits the ShareNumber field in the sub sub form?


:
Dudley wrote:
I have a sub-form which is linked to the main form on control EnvelopeNumber
, and a sub-sub-form which is linked to the sub-form on control ShareClass. I
want control AggregateNumber in the sub-form to be the total of numbers
entered in the ShareNumber control in the sub-sub-form. I assume that I
should enter code in On Exit in the ShareNumber control, so that the total is
updated whenever the user adds or amends a record, but the code is beyond my
knowledge.


I don't think using code is the way to go. The standard way
to do that is to put a (hidden?) text box (named txtTotal)
in the subsubform's header or footer section with an
expression like =Sum([ShareNumber field]) to calculate the
total. Note that [ShareNumber field] must be the name of
the record source field, not the name of a control.

With that in place, your AggregateNumber text box on the
subform can use an expression like:
=[subsubform control name].Form.txtTotal

 

[Marshall Barton]

I thought the ShareNumber control was the text box I said to
put the save record??

It worked but I found I had to put the code in the ShareNumber control's
After Update rather than the text box's.

As long as you and your users agree on a strategy (whether
standard or not) I can't argue against your strategy.

Did saving the record make it work the way you wanted?

Thanks very much. I would say that I do not agree with you on not saving. It
is fine if you understand how undo works in Access, but I have generally
found that it does somethng I do not want, so I have adopted a rule that undo
is fine in Word, but in Access it is much safer to manually correct any
typos. However the main reason is that saving on data entry means that when
the last shareholder is entered, if the aggregate total is incorrect, the
user is immediately alerted to check that he has made an error in entering
share numbers - which is why I want to make Aggregate Total a calculated
field.


Marshall Barton" wrote:
The record with the edited ShareNumber field has to be saved
before Access can recalculate the value. If you force the
record with the new value to be saved, then you take away
the ability for users to hit the Esc key to undo any/all
edits to the record.

I think that's a very good reason for things to work the way
they do and why I think you should not try to "make it
update when the user exits the ShareNumber field".

If you insist on doing what you ask, the code in the
ShareNumber field's text box's AfterUpdate event would be:
Me.Dirty = False


Dudley wrote:
Thanks very much for your help. That works, although the total only updates
when you move to another record. Is it possible to make it update when the
user exits the ShareNumber field in the sub sub form?


:
Dudley wrote:
I have a sub-form which is linked to the main form on control EnvelopeNumber
, and a sub-sub-form which is linked to the sub-form on control ShareClass. I
want control AggregateNumber in the sub-form to be the total of numbers
entered in the ShareNumber control in the sub-sub-form. I assume that I
should enter code in On Exit in the ShareNumber control, so that the total is
updated whenever the user adds or amends a record, but the code is beyond my
knowledge.


I don't think using code is the way to go. The standard way
to do that is to put a (hidden?) text box (named txtTotal)
in the subsubform's header or footer section with an
expression like =Sum([ShareNumber field]) to calculate the
total. Note that [ShareNumber field] must be the name of
the record source field, not the name of a control.

With that in place, your AggregateNumber text box on the
subform can use an expression like:
=[subsubform control name].Form.txtTotal

 

[Marshall Barton]

I think you're saying the txtTotal text box on the
subsubform is now displaying the correct value, but the
AggregateNumber text box on the subform, using the
expression =subsubform.Form.txtTotal, is not updating.

If that's what's happening, then I can't say I have ever
seen this problem. I suppose if worse comes to worse, you
can try to requery the AggregateNumber text box:
Me.Dirty = False
DoEvents
Parent.AggregateNumber.Requery
--
Marsh
MVP [MS Access]

I have just noticed one problem. It works fine to give the correct figure in
the text box, but I cannot find how to make it update the AggregateNumber
control.

As long as you and your users agree on a strategy (whether
standard or not) I can't argue against your strategy.

Did saving the record make it work the way you wanted?

Thanks very much. I would say that I do not agree with you on not saving. It
is fine if you understand how undo works in Access, but I have generally
found that it does somethng I do not want, so I have adopted a rule that undo
is fine in Word, but in Access it is much safer to manually correct any
typos. However the main reason is that saving on data entry means that when
the last shareholder is entered, if the aggregate total is incorrect, the
user is immediately alerted to check that he has made an error in entering
share numbers - which is why I want to make Aggregate Total a calculated
field.


Marshall Barton" wrote:
The record with the edited ShareNumber field has to be saved
before Access can recalculate the value. If you force the
record with the new value to be saved, then you take away
the ability for users to hit the Esc key to undo any/all
edits to the record.

I think that's a very good reason for things to work the way
they do and why I think you should not try to "make it
update when the user exits the ShareNumber field".

If you insist on doing what you ask, the code in the
ShareNumber field's text box's AfterUpdate event would be:
Me.Dirty = False


Dudley wrote:
Thanks very much for your help. That works, although the total only updates
when you move to another record. Is it possible to make it update when the
user exits the ShareNumber field in the sub sub form?


:
Dudley wrote:
I have a sub-form which is linked to the main form on control EnvelopeNumber
, and a sub-sub-form which is linked to the sub-form on control ShareClass. I
want control AggregateNumber in the sub-form to be the total of numbers
entered in the ShareNumber control in the sub-sub-form. I assume that I
should enter code in On Exit in the ShareNumber control, so that the total is
updated whenever the user adds or amends a record, but the code is beyond my
knowledge.


I don't think using code is the way to go. The standard way
to do that is to put a (hidden?) text box (named txtTotal)
in the subsubform's header or footer section with an
expression like =Sum([ShareNumber field]) to calculate the
total. Note that [ShareNumber field] must be the name of
the record source field, not the name of a control.

With that in place, your AggregateNumber text box on the
subform can use an expression like:
=[subsubform control name].Form.txtTotal

 

[Dudley]

Sorry I have not made myself clear. The AggregateNumber text box on the sub
form is updating, but I also want to update the AggregateNumber field in the
table underlying the sub form, and this is what I cannot see how to do.

Thanks
Dudley

I think you're saying the txtTotal text box on the
subsubform is now displaying the correct value, but the
AggregateNumber text box on the subform, using the
expression =subsubform.Form.txtTotal, is not updating.

If that's what's happening, then I can't say I have ever
seen this problem. I suppose if worse comes to worse, you
can try to requery the AggregateNumber text box:
Me.Dirty = False
DoEvents
Parent.AggregateNumber.Requery
--
Marsh
MVP [MS Access]

I have just noticed one problem. It works fine to give the correct figure in
the text box, but I cannot find how to make it update the AggregateNumber
control.

As long as you and your users agree on a strategy (whether
standard or not) I can't argue against your strategy.

Did saving the record make it work the way you wanted?


Dudley wrote:
Thanks very much. I would say that I do not agree with you on not saving. It
is fine if you understand how undo works in Access, but I have generally
found that it does somethng I do not want, so I have adopted a rule that undo
is fine in Word, but in Access it is much safer to manually correct any
typos. However the main reason is that saving on data entry means that when
the last shareholder is entered, if the aggregate total is incorrect, the
user is immediately alerted to check that he has made an error in entering
share numbers - which is why I want to make Aggregate Total a calculated
field.


Marshall Barton" wrote:
The record with the edited ShareNumber field has to be saved
before Access can recalculate the value. If you force the
record with the new value to be saved, then you take away
the ability for users to hit the Esc key to undo any/all
edits to the record.

I think that's a very good reason for things to work the way
they do and why I think you should not try to "make it
update when the user exits the ShareNumber field".

If you insist on doing what you ask, the code in the
ShareNumber field's text box's AfterUpdate event would be:
Me.Dirty = False


Dudley wrote:
Thanks very much for your help. That works, although the total only updates
when you move to another record. Is it possible to make it update when the
user exits the ShareNumber field in the sub sub form?


:
Dudley wrote:
I have a sub-form which is linked to the main form on control EnvelopeNumber
, and a sub-sub-form which is linked to the sub-form on control ShareClass. I
want control AggregateNumber in the sub-form to be the total of numbers
entered in the ShareNumber control in the sub-sub-form. I assume that I
should enter code in On Exit in the ShareNumber control, so that the total is
updated whenever the user adds or amends a record, but the code is beyond my
knowledge.


I don't think using code is the way to go. The standard way
to do that is to put a (hidden?) text box (named txtTotal)
in the subsubform's header or footer section with an
expression like =Sum([ShareNumber field]) to calculate the
total. Note that [ShareNumber field] must be the name of
the record source field, not the name of a control.

With that in place, your AggregateNumber text box on the
subform can use an expression like:
=[subsubform control name].Form.txtTotal

 

[Marshall Barton]

Sorry I have not made myself clear. The AggregateNumber text box on the sub
form is updating, but I also want to update the AggregateNumber field in the
table underlying the sub form, and this is what I cannot see how to do.

Because storing a caclulated value such as that would be a
violation of the Rules of Relational Database Normalization,
you would have to bend over backwards to put it in a table.

I strongly recommend against it (because a total goes out of
sync whenever any one value is modified). If you insist on
doing it, you will have to use a bunch of messy code to
calulate the total and set the subform's record source
field.

 

[Dudley]

Thanks. I will follow your advice.

Dudley

Because storing a caclulated value such as that would be a
violation of the Rules of Relational Database Normalization,
you would have to bend over backwards to put it in a table.

I strongly recommend against it (because a total goes out of
sync whenever any one value is modified). If you insist on
doing it, you will have to use a bunch of messy code to
calulate the total and set the subform's record source
field.