Calculatting Time difference

[Greg]

I have a form where I am entering all issues. On this form I placed subform
where I am displaying issues that are still open and not resolved. I want to
display time difference between current time and the last update. Here is
the code I am using:


Private Sub Form_Timer()

Dim sngElapsedTime As Date
Dim Last_Update As Date
'determine the amount of time that has elapse between last update
' and now.
sngElapsedTime = Now() - [Last Update]
'Update the timer
txtTimeDiff = Format(sngElapsedTime, "hh:mm:ss")

End Sub

Timer interval is set to 1000 so the difference is updated every second but
calculation works only for first record on this subform. If I have 3 records
same time is displayed for all 3 records. For example if difference for
first record is 50 min, that difference is displayed for every record on this
subform even if time difference for second record is 30 min. How I can
calculate time difference for each individual record on my subform?

Also how I can display popup warning message informing that time difference
is greater then 60 min?

 

[Tom van Stiphout]

On Sat, 27 Oct 2007 01:12:00 -0700, Greg

This problem occurs because the control with the calculated value is
not bound to a field in a table. So here you have to make a decision.
If you badly want the current design and functionality, you'll need to
violate a db design rule that says "no calculated values" in tables,
and add the field.
Then update the values using an Update query. I hope you'll consider
running it less frequently than once a second. Something like:
Update SomeTable set MyCalculatedField = DateDiff("d", Now(), [Last
Update])
Then apply the format in your control.
The condition for >60 minutes I would handle with conditional
formatting, setting the background to Red.

-Tom.