Forms - update a field from input in another field

[Guest]

I am using access 2000
I have a form with three date fields set up as text boxes. The first field
("Issued") is the issue date which I input. the second field ("TakeUp") is 3
years later and the third field ("Lapsed") is 7 years minus 1 day later.
What I am trying to do is having entered the first field, to automatically
calculate the 2nd and 3rd text boxes and update the underlying table.
Is there a way I can do this ?

Thank you in advance for your help.

Sean Bishop

 

[Jeff Boyce]

Sean

First, a suggestion... while you may wish to display the future dates, it is
generally not a good idea to store those in your database. Besides being
redundant, you risk data integrity when you create "calculated" values that
you would then have to keep synchronized.

To display in your form, you can add code to the AfterUpdate event of your
first textbox, something like:

Me!txtTakeUp = DateAdd("yyyy",3,Me!txtIssued)
Me!txtLapsed = DateAdd("yyyy",3,Me!txtIssued) - 1
(your syntax may vary)

 

[Douglas J. Steele]

If the two fields are always derivable from the first field, there's no
reason to store them: in fact, it's a bad idea to.

Instead, create a query with two computed fields in it that give you the
TakeUp and Lapsed dates and use that query wherever you would otherwise have
used the table.

 

[Guest]

Dear Jeff & Doug

Thank you for your replies.
I have taken your advice and deleted the two fields from my table.
I must also thank you for showing me how to set up an event.

You have both said that saving derived fields in a table is "Not A Good
Thing To Do" but you have not said why.
Assuming that derived and calculated fields are the same things is there a
reason for this as I can envisage lots of times when I would put these fields
in tables thinking it a good thing to do.

Sean Bishop

 

[Douglas J. Steele]

As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."