Solved

MS Access equivalent of Application.OnTime

Posted on 2010-09-07
26
3,378 Views
Last Modified: 2013-11-27
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
Comment
Question by:Milewskp
  • 9
  • 9
  • 4
  • +2
26 Comments
 
LVL 2

Accepted Solution

by:
murphomatic earned 200 total points
ID: 33623129
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33623141
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
 
LVL 1

Author Comment

by:Milewskp
ID: 33623189
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33623210
ok, the hidden form approach will work but just not sure about the msgbox. Will need to give it a try.
0
 
LVL 2

Assisted Solution

by:murphomatic
murphomatic earned 200 total points
ID: 33623212
rockiroads & author -

The timer event will still fire, even if a modal messagebox is present on the screen..  Your solution should work rockiroads -
0
 
LVL 75
ID: 33623245
I just confirmed that the Timer event will continue to trigger with a Message box open.

mx
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33623248
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33623254
thanks murphomatic and mx. I gave it a test anyways to satisfy any doubts Milewskp may of had.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 100 total points
ID: 33623325
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33623365
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
 
LVL 75
ID: 33623378
Don't for get to take an apple ...

mx
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33623388
lol
0
 
LVL 1

Author Comment

by:Milewskp
ID: 33625772
Hi mx,
Do you have a link that describes how to use the Windows API timer function?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 1

Author Comment

by:Milewskp
ID: 33628237
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
 
LVL 1

Author Comment

by:Milewskp
ID: 33628281
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
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 200 total points
ID: 33628450
Could you not try issuing a sendkeys after application.quit?

SendKeys "{Enter}"
0
 
LVL 1

Author Comment

by:Milewskp
ID: 33629334
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
 
LVL 2

Expert Comment

by:murphomatic
ID: 33629368
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
 
LVL 75
ID: 33629418
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
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 200 total points
ID: 33629542
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
 
LVL 1

Author Comment

by:Milewskp
ID: 33629546
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
 
LVL 33

Expert Comment

by:Norie
ID: 33629628
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
 
LVL 1

Author Comment

by:Milewskp
ID: 33629630
Works for open Input boxes too!
0
 
LVL 1

Author Comment

by:Milewskp
ID: 33685690
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33686517
Thats probably because the original timer code is still running and it cannot execute until it completes, which makes sense.
0
 
LVL 1

Author Comment

by:Milewskp
ID: 33724558
Actually, I didn't want to close the question, I wanted to award points. Not sure what happened.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now