DateDiff and timer like functioning event 1

[Countymnca]

Hello,
I need to change the forecolor of a box that displays the last time an entry was made if the time is greater than 10 minutes ago.

I have changed form and text box properties before with a timer and have gotten the Datediff function calculating the difference in time (minutes) between the time in the box and Time() but getting the text boxes to change is proving to be a problem. There are a dozen of these boxes that I need to do this with on one form.

How would I go about this?

Thanks in advance

 

[MajP]

hilite all applicable controls
in the after update event property type in:
=setEnterTime()

Public Function setEnterTime()
  Me.ActiveControl.Tag = Now
End Function

Private Sub Form_Timer()
  Dim ctrl As Access.Control
  For Each ctrl In Me.Controls
    If IsDate(ctrl.Tag) Then
      Debug.Print DateDiff("n", CDate(ctrl.Tag), Now)
      If DateDiff("n", CDate(ctrl.Tag), Now) >= 10 Then
        'do what you want here
        ctrl.BackColor = vbRed
      End If
    End If
  Next ctrl
End Sub

 

[Countymnca]

Either I am missing something or I didnt give you the entire story.

The text boxes display times retrieved from a table but they are not bound (=DMax("[Room1]","[LogTable]","[Date]=Date()") is what I use to pull the last time).

The form is set to refresh every 5 minutes now since more than one person can do entries at different computers around the building. Data is entered into the table via another form which when it closes, requeries this form with all the time display boxes. Basiclly it is just a display board that stays up on the screen all day. For entries, they need to go to another unlocked form and then return to this form which updates.

I put =setEnterTime() on the unbound boxes and then put the Code in the main forms timer event and then entered a new time record for a test. Nothing changed.

 

[MajP]

You initial post was not very clear

I need to change the forecolor of a box that displays the last time an entry was made if the time is greater than 10 minutes ago.

So the provided code tracks when a user enters data in an identified textbox and then ten minutes later changes the properties
if the values are instead in the text boxes. Then identify those by putting a question mark in the tag property. (or some other tag, but do not use parentheses)

Private Sub Form_Timer()
Dim ctrl As Access.Control
For Each ctrl In Me.Controls
If ctrl.Tag = "?" Then
if isdate(ctrl)
If DateDiff("n", ctrl,0), Now) >= 10 Then
'do what you want here
ctrl.BackColor = vbRed
End If
end if
End If
Next ctrl
End Sub

 

[TheAceMan1]

How are ya Countymnca . . .

There's a [blue]time ambiguity[/blue] you need to allow for if your using [blue]time only[/blue] in your tables. In relation to the [blue]DateDiff[/blue] function its called [purple]midnight crossover![/purple] What happens is [blue]DateDiff[/blue] produces negative numbers. This inverts your logic until you crossover the time in the textbox. See table below:

[tt][blue] Tdat Tnow Diff/Min
*********** *********** **********
10:00:00 PM 11:00:00 PM 60
10:00:00 PM 11:59:00 PM 119
10:00:00 PM 12:00:00 AM -1320 [green]'Midnight Crossover[/green]
10:00:00 PM 11:59:00 AM -601
10:00:00 PM 12:00:00 PM -600
10:00:00 PM 09:59:00 PM -1
10:00:00 PM 10:00:00 PM 0
10:00:00 PM 10:01:00 PM 1[/blue][/tt]

To you this means the next day your color won't change until you crossover your textbox time ... when in fact it should as your way over ten minutes. This is all dependent on a textbox value persisting for more that 24hrs.

To nail this you can simply change your time field to [blue]Date & Time[/blue] ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Countymnca]

AceMan,
I am aware of that problem, but am not really concerned about it. Checks are required every 10 minutes, so even if it resets at midnight, it only takes 10 minutes before they are out of compliance anyway.

I will fight with it a bit more later today. I will try to get it working with just a bound text box on an new blank form first and then adapt it to this mess later if that works. Thanks MajP as well.

 

[Countymnca]

Majp that did it. Thanks!!!