Avatar of Jarred Meyer
Jarred Meyer
Flag for United States of America asked on

Trying to Troubleshoot Form_Timer Code for if statement using DateDiff in Access Form

I have a code on a continuous form that I'm trying to get the Form_Timer function to GoTo a new record if the current record's TimeIn is 60 seconds less than the CurrentTime. So once the record has been sitting there for 60 seconds, based on the TimeIn then it moves to a new record and sets focus to OperID.

Here is my current VBA that is not working.

Private Sub Form_Timer()

Me.Refresh

If DateDiff("s", Me.TimeIn, Time()) > 60 Then

Me.Refresh

DoCmd.GoToRecord , , acNewRec
Me.OperID.SetFocus
Else

End

End If

End Sub
Microsoft Access

Avatar of undefined
Last Comment
Jarred Meyer

8/22/2022 - Mon
Rey Obrero (Capricorn1)

where are the rest of the codes?

did you set the form's timer in the afterupdate event of "TimeIn" ?
Jarred Meyer

ASKER
My apologies, yes I set the time to 10 seconds at the Form_Load event.. Probably should have mentioned that. Here is the code for that.


Private Sub Form_Load()

Me.Form.TimerInterval = 10000


End Sub


I want it to refresh every 10 seconds no matter what, but if it sees that the current record TimeIn meets the criteria of the if statement then proceed with going to a new record and setting focus to OperID of that new record.

Thanks for looking into this for me!
mbizup

Do you need to set a new "Time In" when you move to a new record:

Private Sub Form_Timer()

Me.Refresh

If DateDiff("s", Me.TimeIn, Time()) > 60 Then

Me.Refresh

DoCmd.GoToRecord , , acNewRec
Me.TimeIn = Time()  '<--- Add this
Me.OperID.SetFocus
Else

End

End If

End Sub 

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
mbizup

I  think your DateDif might be returning a negative value too.

Try this instead:

If Abs(DateDiff("s", Me.TimeIn, Time())) > 60 Then

Open in new window


Or this:


If DateDiff("s",  Time(), Me.TimeIn ) > 60 Then 

Open in new window

SOLUTION
Rey Obrero (Capricorn1)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jarred Meyer

ASKER
Didn't modify the code as far as I can tell and it started working. Was led to this using Capricorns MsgBox