Update new record with previous record field

[wolfiewoman]

I have a field called ReviewDue which gets populated from InitialDate + 730
on the first record. I want to be able to put in when the review was
completed (RevDateComp) and start a new record with ReviewDue having the date
from RevDateComp + 730. I need to have a review done every two years. I
want to have it ReviewDue autopopulate after a RevDateComp field is completed
on the previous record. Could anyone assist?

 

[Jeff Boyce]

By storing the value of [InitialDate] + 730, you guarantee that you'll have
to do something to maintain that stored value. For example, what happens if
the initial date was entered incorrectly? Which value (the new initial date
or the existing [ReviewDue] is correct?!

Instead, if you will ALWAYS be adding 730 to [InitialDate], just use a
query. You are always guaranteed of having current data.

Note: DateAdd() function can handle your "plus two years" calculation.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[wolfiewoman]

Once a date is put in InitialDate, it will never change. That is part of a
different table in the form. My review dates are in a subform. I always
want the two yrs added to the InitialDate in the ReviewDue so I know two yrs
from now to do a review. Once I have completed a review, I will go to the
review form and add that in the RevDateComp of that same record. Once that
is completed in that record, I want a new record to have a date 2 yrs from
the RevDateComp in the ReviewDue field. How do I do that?

By storing the value of [InitialDate] + 730, you guarantee that you'll have
to do something to maintain that stored value. For example, what happens if
the initial date was entered incorrectly? Which value (the new initial date
or the existing [ReviewDue] is correct?!

Instead, if you will ALWAYS be adding 730 to [InitialDate], just use a
query. You are always guaranteed of having current data.

Note: DateAdd() function can handle your "plus two years" calculation.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a field called ReviewDue which gets populated from InitialDate + 730
on the first record. I want to be able to put in when the review was
completed (RevDateComp) and start a new record with ReviewDue having the
date
from RevDateComp + 730. I need to have a review done every two years. I
want to have it ReviewDue autopopulate after a RevDateComp field is
completed
on the previous record. Could anyone assist?

 

[Jeff Boyce]

see comments in-line below...

Once a date is put in InitialDate, it will never change.

I'm glad to hear that you/your users NEVER make a data entry error that
needs subsequent correction. Most folks try to take into account the
possibility of needing to correct errors (e.g., keying error, data entered
against incorrect record, etc.).

That is part of a
different table in the form.

?Your form is based on more than one table? How does that work?

My review dates are in a subform.

The usual design of main form/subform handles one-to-many relationships.
From your comment and use of a subform, I'm assuming that you can have
multiple "review dates" for a given (main form) record.

By the way, how does Access know which 'table' the main form record relates
to (see previous statement/comment)?

I always
want the two yrs added to the InitialDate in the ReviewDue so I know two
yrs
from now to do a review.

Once again, it is possible to know when to do the review because an Access
query can tell you when YourDate plus 2 years is getting close. This
doesn't require storing the ReviewDate.

Once I have completed a review, I will go to the
review form and add that in the RevDateComp of that same record.

We're not there ... we can't see your tables, so "of the same record" isn't
quite clear. If you are saying that you record the date that you conduct
the review, does it HAVE to be on the ReviewDate? (I'm guessing not, so I'm
guessing you'll want a way to say "this was reviewed TODAY")

Once that
is completed in that record, I want a new record to have a date 2 yrs from
the RevDateComp in the ReviewDue field. How do I do that?

More complications ... that ReviewDue date was originally based on your
original record. Now you're saying you want to base the "next" one on the
actual date reviewed. Once again, if you use a query to derive the
most-recent date reviewed (or the original start date, if there are no
reviews), you can simply use the query to calculate 2 years from then. No
need to store it.

Why not just store the original date, and the reviewed-on-dates?


Regards

Jeff Boyce
Microsoft Office/Access MVP

By storing the value of [InitialDate] + 730, you guarantee that you'll
have
to do something to maintain that stored value. For example, what happens
if
the initial date was entered incorrectly? Which value (the new initial
date
or the existing [ReviewDue] is correct?!

Instead, if you will ALWAYS be adding 730 to [InitialDate], just use a
query. You are always guaranteed of having current data.

Note: DateAdd() function can handle your "plus two years" calculation.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a field called ReviewDue which gets populated from InitialDate +
730
on the first record. I want to be able to put in when the review was
completed (RevDateComp) and start a new record with ReviewDue having
the
date
from RevDateComp + 730. I need to have a review done every two years.
I
want to have it ReviewDue autopopulate after a RevDateComp field is
completed
on the previous record. Could anyone assist?

 

[wolfiewoman]

see responses and questions in-line below....

see comments in-line below...



I'm glad to hear that you/your users NEVER make a data entry error that
needs subsequent correction. Most folks try to take into account the
possibility of needing to correct errors (e.g., keying error, data entered
against incorrect record, etc.).

Yes they may make mistakes. I'm not saying everyone is perfect. What I
mean is that after the date is input with correct information it will never
need updated. I have it setup to do an afterupdate to add 2 yrs to the
review due in the subform.

?Your form is based on more than one table? How does that work?

It is based off a query that links three tables.

The usual design of main form/subform handles one-to-many relationships.
From your comment and use of a subform, I'm assuming that you can have
multiple "review dates" for a given (main form) record.

By the way, how does Access know which 'table' the main form record relates
to (see previous statement/comment)?

It actually relates to the main table with a course ID link.

Once again, it is possible to know when to do the review because an Access
query can tell you when YourDate plus 2 years is getting close. This
doesn't require storing the ReviewDate.

The people I am creating this database for would like to have it listed
everytime they pull up the form to look at the course information. They like
to see it.

We're not there ... we can't see your tables, so "of the same record" isn't
quite clear. If you are saying that you record the date that you conduct
the review, does it HAVE to be on the ReviewDate? (I'm guessing not, so I'm
guessing you'll want a way to say "this was reviewed TODAY")

The record does not get reviewed in the database. The course physical
material is reviewed and then reported back to the librian when it was
completed. Yes there will be multiple reviews for one course. It needs to
be done every two years.

More complications ... that ReviewDue date was originally based on your
original record. Now you're saying you want to base the "next" one on the
actual date reviewed. Once again, if you use a query to derive the
most-recent date reviewed (or the original start date, if there are no
reviews), you can simply use the query to calculate 2 years from then. No
need to store it.

Why not just store the original date, and the reviewed-on-dates?

My subform for reviews is reviewID, courseID (linked to main table),
reviewdue, reviewCompleted, reviewcomments. They want to see each record of
the review in the subform when all the dates were done. They also like to do
comperisions of when the review was due and when it was actually completed.

Is it possible to do this without the user inputing the data to figure out
when the 2 yrs will actually be?

Thank you.

wolfiewoman

Regards

Jeff Boyce
Microsoft Office/Access MVP

By storing the value of [InitialDate] + 730, you guarantee that you'll
have
to do something to maintain that stored value. For example, what happens
if
the initial date was entered incorrectly? Which value (the new initial
date
or the existing [ReviewDue] is correct?!

Instead, if you will ALWAYS be adding 730 to [InitialDate], just use a
query. You are always guaranteed of having current data.

Note: DateAdd() function can handle your "plus two years" calculation.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a field called ReviewDue which gets populated from InitialDate +
730
on the first record. I want to be able to put in when the review was
completed (RevDateComp) and start a new record with ReviewDue having
the
date
from RevDateComp + 730. I need to have a review done every two years.
I
want to have it ReviewDue autopopulate after a RevDateComp field is
completed
on the previous record. Could anyone assist?

 

[Jeff Boyce]

I'm not sure how to help, so perhaps one of the other newsgroup readers
who've done it your way can offer ideas.

At the risk of beating a dead horse, I'll point out once more that there is
rarely a need to store a calculated date like this (and from your
description, your situation is not one of those rare ocassions).

What you are asking to be able to do (if I understand, record the starting
date, record the date of actual review, and know when two years are up from
which ever of those two is the most recent) can be done using a query to do
the calculation, and a form to display the results.

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

see responses and questions in-line below....

see comments in-line below...



I'm glad to hear that you/your users NEVER make a data entry error that
needs subsequent correction. Most folks try to take into account the
possibility of needing to correct errors (e.g., keying error, data
entered
against incorrect record, etc.).

Yes they may make mistakes. I'm not saying everyone is perfect. What I
mean is that after the date is input with correct information it will
never
need updated. I have it setup to do an afterupdate to add 2 yrs to the
review due in the subform.

?Your form is based on more than one table? How does that work?

It is based off a query that links three tables.

The usual design of main form/subform handles one-to-many relationships.
From your comment and use of a subform, I'm assuming that you can have
multiple "review dates" for a given (main form) record.

By the way, how does Access know which 'table' the main form record
relates
to (see previous statement/comment)?

It actually relates to the main table with a course ID link.

Once again, it is possible to know when to do the review because an
Access
query can tell you when YourDate plus 2 years is getting close. This
doesn't require storing the ReviewDate.

The people I am creating this database for would like to have it listed
everytime they pull up the form to look at the course information. They
like
to see it.

We're not there ... we can't see your tables, so "of the same record"
isn't
quite clear. If you are saying that you record the date that you conduct
the review, does it HAVE to be on the ReviewDate? (I'm guessing not, so
I'm
guessing you'll want a way to say "this was reviewed TODAY")

The record does not get reviewed in the database. The course physical
material is reviewed and then reported back to the librian when it was
completed. Yes there will be multiple reviews for one course. It needs
to
be done every two years.

More complications ... that ReviewDue date was originally based on your
original record. Now you're saying you want to base the "next" one on
the
actual date reviewed. Once again, if you use a query to derive the
most-recent date reviewed (or the original start date, if there are no
reviews), you can simply use the query to calculate 2 years from then.
No
need to store it.

Why not just store the original date, and the reviewed-on-dates?

My subform for reviews is reviewID, courseID (linked to main table),
reviewdue, reviewCompleted, reviewcomments. They want to see each record
of
the review in the subform when all the dates were done. They also like to
do
comperisions of when the review was due and when it was actually
completed.

Is it possible to do this without the user inputing the data to figure out
when the 2 yrs will actually be?

Thank you.

wolfiewoman

Regards

Jeff Boyce
Microsoft Office/Access MVP

:

By storing the value of [InitialDate] + 730, you guarantee that you'll
have
to do something to maintain that stored value. For example, what
happens
if
the initial date was entered incorrectly? Which value (the new
initial
date
or the existing [ReviewDue] is correct?!

Instead, if you will ALWAYS be adding 730 to [InitialDate], just use a
query. You are always guaranteed of having current data.

Note: DateAdd() function can handle your "plus two years"
calculation.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a field called ReviewDue which gets populated from InitialDate
+
730
on the first record. I want to be able to put in when the review
was
completed (RevDateComp) and start a new record with ReviewDue having
the
date
from RevDateComp + 730. I need to have a review done every two
years.
I
want to have it ReviewDue autopopulate after a RevDateComp field is
completed
on the previous record. Could anyone assist?