MS Access equivalent of Application.OnTime

I understand that Application.OnTime is available in Excel VBA and Word VBA, but not in Access.
What can I use to automatically run a procedure at a given time even if the user has a Msgbox open?
LVL 1
MilewskpAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
murphomaticConnect With a Mentor Commented:
You could come at this a couple of different ways:

1.  Use the Timer control on an Access form to fire an event in VBA on a time interval.
2.  Use Windows Scheduled Tasks to launch MSAccess with command-line switches to open a specific database and execute a macro, that in-turn executes some VBA code.
0
 
rockiroadsCommented:
Don't know of any equivalent but maybe you can create your own

How about you create a form, a hidden form say which makes use of the timer command. You can set the timer interval on form_load then form_timer will kick in at the specified interval and run whatever you tell it to do so.

You can configure this form to run on app startup or start it up your own way.

0
 
MilewskpAuthor Commented:
Folks, let me elaborate,
I want to run a procedure that resides in an mdb file that is currently open, even if the user has a msgbox open in that application.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
rockiroadsCommented:
ok, the hidden form approach will work but just not sure about the msgbox. Will need to give it a try.
0
 
murphomaticConnect With a Mentor Commented:
rockiroads & author -

The timer event will still fire, even if a modal messagebox is present on the screen..  Your solution should work rockiroads -
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I just confirmed that the Timer event will continue to trigger with a Message box open.

mx
0
 
rockiroadsCommented:
Ok, gave it a test but boy do I hate shitty A2007, so damn long winded to do anything simple

I had a form where I clicked a button and a msgbox came up. All fine and dandy.

Behind the scenes though, I had a timer setup so that every 5 seconds I would get an annoying msgbox.

So ran the form, waited for timer msgbox, came up, clicked ok.
then hit button, my msgbox came up, left it up and lo and behold, timer msgbox came up also

so you can run your procedures whilst msgbox is running
0
 
rockiroadsCommented:
thanks murphomatic and mx. I gave it a test anyways to satisfy any doubts Milewskp may of had.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
If you are really going to go the Timer route, I would be using the Windows API timer function, which is a lot less problematic than the Access form Time can be ...

mx
0
 
rockiroadsCommented:
mx, I was thinking the same, just thought the form would be easier initially so you don't have to remember to kickstart the interval in any starting form, unless there is a way. I gotta pop out now, back to school nite, is what they call it, gotta meet the teachers
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Don't for get to take an apple ...

mx
0
 
rockiroadsCommented:
lol
0
 
MilewskpAuthor Commented:
Hi mx,
Do you have a link that describes how to use the Windows API timer function?
0
 
MilewskpAuthor Commented:
Folks,
My good but short memory told me that event procedures don't run while a Msgbox is open (thanks for correcting me on that); in fact, it's Application.Quit that doesn't execute while a Msgbox is open.

Unfortunatelty, that is what my procedure is supposed to do (quit the app), and is the reason I was interested in Application.OnTime.

0
 
MilewskpAuthor Commented:
If I could use code to close the open Msgbox before I execute Application.Quit, that would probably do it. But I don't know if that's possible.
0
 
rockiroadsConnect With a Mentor Commented:
Could you not try issuing a sendkeys after application.quit?

SendKeys "{Enter}"
0
 
MilewskpAuthor Commented:
Hi rocki,
You are a genius!

This works great:

Private Sub Form_Timer()
   Beep
   Me.TimerInterval = 1
   Application.Quit
   SendKeys "{Enter}"
 
End Sub

NOTE: The reason for Me.TimerInterval = 1 is that if a Msgbox is open, the application doesn't actually close until Form_tTmer is run a second time. Can anyone explain why?
0
 
murphomaticCommented:
The application doesn't actually quit until the msgbox is cleared.. so, the first time through attempts to quit, fails to quit due to the msgbox, and then clears the msgbox w/the sendkeys command.  The 2nd time through, the Application.Quit command succeeds as there is no longer a msgbox preventing the quit..
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You have the Timer set to trigger every 1/1000 of second!  I cannot believe your app will function correctly with that kind of setting. Even @ 1 second is questionable.

mx
0
 
rockiroadsConnect With a Mentor Commented:
The reason why I specified SendKeys after was just in case there was no message box so you wouldn't be hitting enter in the wrong place.

If you are sure you will get a message box or hitting enter will not do any other harm then placing sendkeys before might do it for you,
0
 
MilewskpAuthor Commented:
Thanks murph,

I tried this:
 
   Application.Quit
   SendKeys "{Enter}"
   Application.Quit

but it still took two passes to quit. Is this a timing issue; ie will Me.TimerInterval = 1  always work, or do I need to use a longer time, eg., Me.TimerInterval = 1000?

0
 
NorieVBA ExpertCommented:
If you really are going to look into API or other methods then you could try a different sort of message box.

I can't remember the API or library but I'm sure there is code out there that will display a message for a set amount of time
and then continue with the rest of the code.

SendKeys might be an idea but it might be as hicky as the hidden userform approach.

0
 
MilewskpAuthor Commented:
Works for open Input boxes too!
0
 
MilewskpAuthor Commented:
I discovered that if a form timer opens a Msgbox or Input box, that timer event will not fire again until the msgbox/ inputbox is closed by the user.  So use a form instead of a Msgbox/ Input box.
0
 
rockiroadsCommented:
Thats probably because the original timer code is still running and it cannot execute until it completes, which makes sense.
0
 
MilewskpAuthor Commented:
Actually, I didn't want to close the question, I wanted to award points. Not sure what happened.
0
All Courses

From novice to tech pro — start learning today.