Update table with future date calculated in form

[Jacquiemal]

I have a form that feeds a table with data. One of the fields in the form
(ApprovalPeriod2) calculates a date one year ahead of one that is entered in
another field (ApprovalPeriod1). Both of those fields are text boxes because
the expression
(=DateSerial(Year([ApprovalPeriod1]),Month([ApprovalPeriod1])+12,Day([ApprovalPeriod1]))
only works with a date that is manually entered rather than selected from a
pop-up calendar. My problem is the calculation only seems to work
automatically (optimal for users) with the expression as the Control Source.
This means the resulting date in the ApprovalPeriod2 text box does not
populate the corresponding field in the underlying table. Can someone help,
please? Thanks!

 

[ntc]

you state: problem is the calculation only seems to work
automatically (optimal for users) with the expression as the Control Source.

*** since you want the 2nd text box to be sourced with the table it can not
be controlled by an expression....your issue is that the expression does not
seem to be triggered. perhaps one puts it in the data in field 1 but then
does not tab out and thus the 'AfterUpdate' event does not trigger. people
uses mouses alot and not tab and so getting an event to fire/trigger can be
an issue of predicting the human experience... you could try 'Before Update'
or put in a button they must press or whatever...but you have to find a
trigger for that expression to fire....

 

[John W. Vinson]

I have a form that feeds a table with data. One of the fields in the form
(ApprovalPeriod2) calculates a date one year ahead of one that is entered in
another field (ApprovalPeriod1). Both of those fields are text boxes because
the expression
(=DateSerial(Year([ApprovalPeriod1]),Month([ApprovalPeriod1])+12,Day([ApprovalPeriod1]))
only works with a date that is manually entered rather than selected from a
pop-up calendar. My problem is the calculation only seems to work
automatically (optimal for users) with the expression as the Control Source.
This means the resulting date in the ApprovalPeriod2 text box does not
populate the corresponding field in the underlying table. Can someone help,
please? Thanks!

The calculated field probably SHOULDN'T populate any table field.

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.

I'd just *display* the ApprovalPeriod2 using

=DateAdd("yyyy", 1, [ApprovalPeriod1])

or, if you prefer, DateAdd("m", 12, [ApprovalPeriod1]).

The only reason to store the value is if this date is just a suggestion and
it's OK to allow it to be edited.

 

[Linq Adams via AccessMonster.com]

As ntc has said, you can't have an expression in the Control Source of
ApprovalPeriod2 and have it bound (saved) to your table. You can also use the
DateAdd() function for your calculation, instead of the song and dance using
DateSerial().

Remove the expression from ApprovalPeriod2. Then, use this code, replacing

YourCalendarName

with your actual calendar name. It'll fill n both fields at once.

Private Sub YourCalendarName_AfterUpdate()
Me.ApprovalPeriod1 = Me.YourCalendarName
Me.ApprovalPeriod2 = DateAdd("yyyy", 1, Me.YourCalendarName)
End Sub

If you want to allow for the possibility of the user manually entering
ApprovalPeriod1 if they want to, also add this code:

Private Sub ApprovalPeriod1_AfterUpdate()
Me.ApprovalPeriod2 = DateAdd("yyyy", 1, Me.ApprovalPeriod1)
End Sub

 

[Jacquiemal]

thanks for your view, John. Unfortunately, all the information entered into
this form MUST be stored in the underlying table for future reference. The
future date is part of a process that must be documented and referred to for
status checks, so not storing it is not an option.

I have a form that feeds a table with data. One of the fields in the form
(ApprovalPeriod2) calculates a date one year ahead of one that is entered in
another field (ApprovalPeriod1). Both of those fields are text boxes because
the expression
(=DateSerial(Year([ApprovalPeriod1]),Month([ApprovalPeriod1])+12,Day([ApprovalPeriod1]))
only works with a date that is manually entered rather than selected from a
pop-up calendar. My problem is the calculation only seems to work
automatically (optimal for users) with the expression as the Control Source.
This means the resulting date in the ApprovalPeriod2 text box does not
populate the corresponding field in the underlying table. Can someone help,
please? Thanks!

The calculated field probably SHOULDN'T populate any table field.

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.

I'd just *display* the ApprovalPeriod2 using

=DateAdd("yyyy", 1, [ApprovalPeriod1])

or, if you prefer, DateAdd("m", 12, [ApprovalPeriod1]).

The only reason to store the value is if this date is just a suggestion and
it's OK to allow it to be edited.

 

[Jacquiemal]

thanks

you state: problem is the calculation only seems to work
automatically (optimal for users) with the expression as the Control Source.

*** since you want the 2nd text box to be sourced with the table it can not
be controlled by an expression....your issue is that the expression does not
seem to be triggered. perhaps one puts it in the data in field 1 but then
does not tab out and thus the 'AfterUpdate' event does not trigger. people
uses mouses alot and not tab and so getting an event to fire/trigger can be
an issue of predicting the human experience... you could try 'Before Update'
or put in a button they must press or whatever...but you have to find a
trigger for that expression to fire....

I have a form that feeds a table with data. One of the fields in the form
(ApprovalPeriod2) calculates a date one year ahead of one that is entered in
another field (ApprovalPeriod1). Both of those fields are text boxes because
the expression
(=DateSerial(Year([ApprovalPeriod1]),Month([ApprovalPeriod1])+12,Day([ApprovalPeriod1]))
only works with a date that is manually entered rather than selected from a
pop-up calendar. My problem is the calculation only seems to work
automatically (optimal for users) with the expression as the Control Source.
This means the resulting date in the ApprovalPeriod2 text box does not
populate the corresponding field in the underlying table. Can someone help,
please? Thanks!

 

[Jacquiemal]

Linq, thank you!! This works PERFECTLY!