Solved

MS Access equivalent of Application.OnTime

Posted on 2010-09-07
26
3,344 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

747 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