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

Jarred Meyer
Jarred Meyer used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
where are the rest of the codes?

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

Author

Commented:
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!
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
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

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
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

Top Expert 2016
Commented:
your code looks good.. what is the problem ?

add a msgbox in the form's timer event to check if it is firing
Production Manager
Commented:
mbizup, I don't want the TimeIn to populate until someone fills in the OperID field. I also tried the code you gave me and that wasn't triggering the messagebox. I went back to the original code and did start getting the message box to pop up.

So then I removed the messagebox and let it run again and it has been working ever since.. I don't understand.. I messed with it for so long before I posted the question and it wasn't working. Just to clarify my current code vs. what I copied and pasted to the initial questions;

Here is my current code;

Private Sub Form_Timer()

Me.Refresh

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

Me.Refresh

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

End If

End Sub


And here is the code I originally posted;

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


Not sure why it wasn't working to begin with. Rather bazaar.. It's working like a charm now.

Thanks all for your input though!
Jarred MeyerProduction Manager

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial