Solved

MS Access equivalent of Application.OnTime

Posted on 2010-09-07
26
3,498 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) 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
 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

749 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