run code on timer

[cpampas]

Hi,
I am wondering if this is posible :
On the timer event of a form i am running a code every 60 seconds

With rstC ' ( the recordset is sorted by the field  !hora, wich is a time field)


Do While Not .EOF
     If  !jaEsta = 0 And now() >TimeValue(!hora) then
         ' here i do something     
     End If
     .MoveNext
Loop
End With

if the current time is for example 4pm, and the lowest/first !hora field is 8pm, it is useless to run the timer every 60 seconds, until it finaly meets the condition to run the code.
Any thoughts

 

[pbaldy]

Not sure what your question is. It doesn't seem valid to compare Now(), which includes the date, to TimeValue(Whatever), which would just be a time. If you're wanting to skip the test, you could calculate the interval between now and the first value in the table and change the TimerInterval property accordingly.

 

[CJ_London]

would need to know more about your data. For example you say

!hora, wich is a time field)

if it is a time field, why do you need the timevalue function? As Paul has alluded to, since time is a decimal value less that 0 and now is a value in excess of 44000, now() >TimeValue(!hora) will always be true

So say the time now is today at 3:45pm and the first record has a time of 8am - what happens then?

You need to be comparing like with like i.e. now(), with a datetime field, not just time - or perhaps you need Time()>!hora

Whatever your actual situation, you can use the timer function to change the timerinterval. The timer interval is expressed as 1000th/sec, so 60 seconds is 60000.

so assuming your hora field is actually datetime you might have something like

me.timerinterval=datediff("s",now(),!hora)*1000

but you will probably need code to change it back to a shorter interval for the next record

 

[cpampas]

I am sorry, i wanted to make more simple, and ended up messing it up, this is the code that executes every 60 seconds on the timer event

Dim strPath As String, horaAgora As Date, horaAcao As Date, ruidoID As Integer, n As Integer
Dim db As Database
Set db = CurrentDb()
Dim rstRuidos As Recordset, rstAcoes As Recordset, rstC As Recordset

Set rstRuidos = db.OpenRecordset("tblRuidos", dbOpenDynaset)
Set rstC = Me.RecordsetClone
horaAgora = Now()
rstC.MoveLast
rstC.MoveFirst
With rstC

Do While Not .EOF
  ruidoID = !ruidoID
  horaAcao = DateValue(!dia) + TimeValue(!hora)    ' horaAcao =  17/01/2021 08:00
     If horaAgora > horaAcao And !jaEsta = 0 Then   ' horaAgora = 17/01/2021 04:00
        
     ' Here I run some code

           Me.Form.Requery
     End If
     .MoveNext
     Loop
End With
Me.Form.Requery

So, I want to find a way to avoid this code to run every 60 seconds, in case the first value horaAcao, is hours away. in the current case the next valid condition to run some code , will only happen within 4 hours

 

[arnelgp]

Form TimerInterval is triggered every X (the value you set) milliseconds, right?
you should First set it to Trigger 1 or 2 seconds (1000 or 2000)

Private Sub Form_Timer()
Dim current_ms As Long
Dim field_ms As Long
Dim diff_ms As Long

current_ms = TimeToMilliSec(Time$)


With rstC ' ( the recordset is sorted by the field  !hora, wich is a time field)
      .MoveFirst
      Do While Not .EOF
            field_ms = TimeToMilliSec(TimeValue(!hora))
            diff_ms = field_ms - current_ms
            If diff_ms >= 0 And diff_ms <= 1000 Then
                  Me.TimerInterval = 0
                  ' here i do something
                  Me.TimerInterval = 2000
            Else
          
                  If diff_ms > 0 Then
                        ' reset TimerInterval
                        Me.TimerInterval = diff_ms
                        Exit Sub
                  End If
                
            End If
          
            .MoveNext
    
      Loop
      Me.TimerInterval = 0
End With
End Sub

copy this to a Module:

Public Function TimeToMilliSec(ByVal t As Variant) As Long
Dim ms As Integer
Dim i As Integer
t = TimeValue(t)
i = InStrRev(t, ".")
If i > 0 Then
      ms = CInt(Mid$(t, i + 1))
End If
TimeToMillisec = ms + (second(t) * 1000) + (Minute(t) * 60000) + (Hour(t) * 3600000)
End Function

 

[cpampas]

​

arnelgp, Your code seems to work as i intended, I am going to study it so that i can figure out why it does not run the code on the timer event (wich is what i wanted), when the next the field !hora is 4 hours later than the current time.Nevertheless in the botton left corner of the access window there is a message that says "calculating....". what is it calculating? would there be any issues because of that, like running out of memory?

Many thanks for your help

 

[The_Doc_Man]

I will answer a specific question. If Access is running out of memory, it is IMMEDIATELY going to tell you that.
the "...calculating" message in the lower left corner is an Access status message that means exactly what it says. But "calculating" CAN include running a query or getting stuck in a code loop.

 

[arnelgp]

it is useless to run the timer every 60 seconds

that was you said in post#1.
therefore i made the code to adjust the Timer.
supposed the difference between !hora and the Current Time is 4 hours,
since it is futile for the timer to check (everytime) if the 4 hours have already elapsed,
the timer is adjust to fire again After 4 hours.

 

[cpampas]

Many thanks, it works great

 

[Pat Hartman]

The whole point of a timer event is that it runs on a schedule. If you change the interval on the fly, test very, very carefully because the interval stays set at the new value which won't be what you want the next day.