Update Condition Field based on Date Comparison

[Guest]

Here is my setup:

[TblHist] - Containing [InstallDate] field and [RecordID]
[TblRecords] - Containing [ConditionDate] field and [RecordID]

I have a field that is input initiially by the user which give an estimate
of time till replacement [InspectionCondition]. This field is bound to
[TblRecords] and is a combo box with the available conditions.

As this database is used, Other users will input new [InstallDate], which
would negate the originally indicated [InspectionCondition]. I would like to
be able to have the [InspectionCondition] field autoupdate when the
[InstallDate] changes.

I experimented with doing the comparisons of the InstallDate to Present Date
on the form, and was able to get my conditions to update in an unbound field.
But I really want that to be updated back to the table. I know this is
redundant, but without this I have problems when I attempt to display reports
for more than one record.

I know I will probably need to provide more information, but anyone have any
suggestions?

Thank you.

 

[Pat Hartman\(MVP\)]

It isn't necessary to copy data from the parent record to each child record.
In your forms/reports, use queries as the RecordSource rather than tables.
This allows you to join the two tables and select data from each.
What you are attempting to do would require a code loop that runs in the
parent form to read EVERY child record for the current parent record and
update it. You would use the RecordSetClone to do this. You would never
open a new recordset.