store value from the field in the Form in the field of the Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I need to link the field from my Form to the field in the Table. It is not linked now because I changed the control source of the field and added DateAdd expression to the field, and now the field in the Form is not linked to the underlying field in the Table

In the From
Field Name: 5500 Du
Control Source: =DateAdd("m",7,[PYE]) - (changed from the original control source: 5500 Due
Basically the DateAdd expression adds 7 months to the date entered in the PYE field in the Form, which is what I need but the new value is not stored in the Table: Plan Filing, Field: 5500 Du

The table where data should be stored
Table Name: Plan Filin
Field Name: 5500 Du

I was trying to add the following in the VB

Private Sub Ctl5500_Due_AfterUpdate(
Me.DateAdd("m", 7, [PYE]) = Me.Ctl5500_Du

...but this is not working...

....can you help...I am lost...

BIG THANKS

Natalia
 
Natalia said:
Hello,

I need to link the field from my Form to the field in the Table. It is not linked now because I changed the control source of the field and added DateAdd expression to the field, and now the field in the Form is not linked to the underlying field in the Table.

In the From:
Field Name: 5500 Due
Control Source: =DateAdd("m",7,[PYE]) - (changed from the original control source: 5500 Due)
Basically the DateAdd expression adds 7 months to the date entered in the PYE field in the Form, which is what I need but the new value is not stored in the Table: Plan Filing, Field: 5500 Due

The table where data should be stored:
Table Name: Plan Filing
Field Name: 5500 Due

I was trying to add the following in the VB

Private Sub Ctl5500_Due_AfterUpdate()
Me.DateAdd("m", 7, [PYE]) = Me.Ctl5500_Due
Click to expand...


If the value of [5500 Due] is always 7 months after PYE,
then it should not be stored in the table (what if the PYE
date is changed?). What you already have is the proper way
to display the [5500 Due] date.

If the value of [5500 Due] may be modified by the user,
then you need to determine when it sould be set to 7 months
after PYE and when it should not be set automatically.
 
Hello Marsh

Good point, but what if the value is fixed and does not change. Any other recommendation how to store a calculated field

Natalia
 
Natalia said:
Good point, but what if the value is fixed and does not change. Any other recommendation how to store a calculated field?
Click to expand...


If it never changes, then there is no good reason to store
it in the table. Whenever you want to use the calculated
value in a query (or form/repor), just use the DateAdd
expression you already have.

As others have already pointed out in your other thread,
there are many good reasons to not save any value derived
from another field in the same record. The only negative to
this is that you may have the DateAdd expression duplicated
in more than one place, but this is easily overcome by
creating a function to do the calculation.

If you're going to insist on violating the fundamental rules
of realtional database normalization, then remove the
expression from the text box and use the code that Graham
posted.
 
Back
Top