Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3833
  • Last Modified:

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?
0
Milewskp
Asked:
Milewskp
  • 9
  • 9
  • 4
  • +2
5 Solutions
 
murphomaticCommented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
rockiroadsCommented:
ok, the hidden form approach will work but just not sure about the msgbox. Will need to give it a try.
0
 
murphomaticCommented:
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)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
 
rockiroadsCommented:
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
 
rockiroadsCommented:
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
 
NorieCommented:
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 9
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now