Updating a field in a table.

[Guest]

I have this time clock application I made that uses datediff to calculate
number of hours when someone clocks out. Some times a manager must change
someones time from query. What can I do to make it recalculate the number of
hours after one of the times has been changed in a query?

 

[Joseph Meehan]

I have this time clock application I made that uses datediff to
calculate number of hours when someone clocks out. Some times a
manager must change someones time from query. What can I do to make
it recalculate the number of hours after one of the times has been
changed in a query?

Is the "number of hours" computed in the query, or is it an amount saved
to a table?

If it is computed in the query:
Do one of the following:
a.. To refresh the records in Datasheet or Form view, click Refresh on the
Records menu.
b.. To refresh the records in PivotTable or PivotChart view, click Refresh
on the PivotTable or PivotChart toolbar.
c.. To requery the records, press SHIFT+F9
If it is stored in the table, then it is a design problem Calculated
values should not be stored, for this and other reasons. It is far better
design to recompute them every time you want to see them.

 

[John Vinson]

I have this time clock application I made that uses datediff to calculate
number of hours when someone clocks out. Some times a manager must change
someones time from query. What can I do to make it recalculate the number of
hours after one of the times has been changed in a query?

This is one of the reasons that derived data, such as the result of
your datediff expression, SHOULD NOT be stored in a table AT ALL. It's
all too easy to get into this situation, where the StartTime is 08:00,
the EndTime is 08:30, and the HoursWorked is 8.25!

Consider having your table store only the start and end time values,
NOT storing the number of hours; instead use a Query to calculate it
as needed.

If you wish to store the derived, redunedant data, then you must
update the HoursWorked field whenever one of the time values changes.
This could be done in code from the form (in the AfterUpdate event of
each time textbox), or you could run an Update query.

John W. Vinson[MVP]