storing calulated field from form in the table field

[Guest]

Hello

I felt like I had to rephrase the subject of my question in my desperation for answer

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

Natali


Print | Copy URL of this post



.. .


Manage Your Profil

 

[Ted Allen]

Hi Natalia,

I would reset the control source of your control to be
[5500 Due]. Then, add code to the AfterUpdate event of
the PYE field as follows:

Me![5500 Due] = DateAdd("m",7,Me![PYE])

HTH

-Ted Allen

-----Original Message-----
Hello,

I felt like I had to rephrase the subject of my question in my desperation for answer.

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

...but this is not working....

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

BIG THANKS.

Natalia


Print | Copy URL of this post



.. .


Manage Your Profile


.

 

[Graham R Seach]

Natalia,

It's pretty easy for you to do that calculation, right? That's the first of
the very compelling reasons why storing calculated values is not considered
good practice. The second is that doing so creates a maintenance nightmare.
The third reason is that the Normalization Police with track you down and
kill you in a very slow, painful and rather messy manner. Just kidding, but
I strongly recommend that you don't store this value, because you can easily
calculate it on-the-fly any time you need it.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Hello,

I felt like I had to rephrase the subject of my question in my desperation for answer.

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

..but this is not working....

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

BIG THANKS.

Natalia


Print | Copy URL of this post



. .


Manage Your Profile

 

[Ted Allen]

P.s. You may want to take a close look at whether you
need to store this field at all. Normally data that can
be calculated from another field would not be stored in
the database. Instead, it would just be calculated at
run time in your queries, forms and reports using your
formula in an unbound control.

-Ted Allen

-----Original Message-----
Hello,

I felt like I had to rephrase the subject of my question in my desperation for answer.

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

...but this is not working....

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

BIG THANKS.

Natalia


Print | Copy URL of this post



.. .


Manage Your Profile


.

 

[Guest]

TED!!!!..
I appreciate your help so much...thank you is not enough!
I will try to enter the expression and see how it works
Yours
Natali

 

[Guest]

Graham

This was humorous, thank you for your advice. I will think about it. What if I enter the DateAdd expression into the Default Value (if possible) in the Table: Plan Filing, Field: 5500 Due? I need to have the 5500 Due date set once and forever. It is fixed once set. And I will also remove the 5500 Due field from the Form, becuse I don't need it there really but more on reports. What do you think? Is there any other way I can set the 5500 Due date based on PYE data

Again, thank you for your response...great to be part of the community

Yours

Natali

 

[Guest]

You and Graham are probably right that I could create a report with a query, but I am creating also a Form with a subform and I need the field 5500 Due date shown on that Form as a check. Not sure what I should do..another query?....too many questions.

Thanks. Natalia

 

[Graham R Seach]

Natalia,

If you're only ever storing this value ONCE, then I would set the control's
ControlSource to the name of the field into which you want to store its
value. Then if [5500 Due] is calculated on the basis of [PYE]'s value, I'd
put the following into the form's Current event:
If IsNull(Me![5500 Due]) Then
'Only make the change if [5500 Due] has no value
If Not IsNull(Me!PYE) Then
'Only make the change if [PYE] has a value
Me![5500 Due] = DateAdd("m", 7, Me!PYE)
End If
End If

This will automatically check and (if necessary) calculate and store the due
date for every record you navigate to.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Graham,

This was humorous, thank you for your advice. I will think about it. What

if I enter the DateAdd expression into the Default Value (if possible) in
the Table: Plan Filing, Field: 5500 Due? I need to have the 5500 Due date
set once and forever. It is fixed once set. And I will also remove the 5500
Due field from the Form, becuse I don't need it there really but more on
reports. What do you think? Is there any other way I can set the 5500 Due
date based on PYE data?

 

[Tim Ferguson]

5500 Due date shown on that Form as a check. Not sure what I should
do..another query?

Either would do: you can base the form on a query, or you can use the
controlsource of the control on the form to do the calculation. Either way,
you are better off than storing an extra column in the table:

- storing a column wastes extra disk space

- which takes much longer to read than the calculation does

- storing a column means that you have to remember to update
it whenever you change the other value

- calculating it means that it is always in step with the
other value

Hope that helps


Tim F

 

[Guest]

Ted

I tried to post my email online but for some reason is not there...maybe I used up all my chances..

I entered the code you told me and it is working fine, however I know that I am violating some normalization rules..

Here is me next "quest"

This is what I entered into the AfterUpdate event procedure

Me![5500 Due] = DateAdd("m", 7, Me![PYE]
Me![PBGC Due #2] = DateAdd("m", 10, Me![PYE] + 15
Me![SAR] = DateAdd("m", 2, Me![5500 Due]

But I need to enter one more thing and this one is even more complicated - for me at least - than the first one...
The idea is similar to the above. Here however, first access must check if in the Table: Plans, Field: Plan Participants, the number of participants is = >500. If it is, then the PBGC Due #1 will be 2 months after PYE, and if not, I need the number to be 99/99, to indicate that really there is no PBGC Due #1
I tried to enter some expression but it does not work. I am not sure if this can work overall as I entered the following just under the DateAdd expression, in the same Form, in the AfterUpdate even in the Field: PYE
So the entire expression looks like that

Me![5500 Due] = DateAdd("m", 7, Me![PYE]
Me![PBGC Due #2] = DateAdd("m", 10, Me![PYE] + 15
Me![SAR] = DateAdd("m", 2, Me![5500 Due]

If Plans.[Plan_Participants] >= 500 The
Me![PBGC Due #1] = DateAdd("m", 2, Me![PYE]
Els
Me![PBGC Due #1] = 999
End I

Can you help again

Sincerely

Natali