Ontime Method?

jme

New member
Local time
Today, 15:49
Joined
Sep 12, 2007
Messages
7
hola

my procedure needs to loop/stop and restart every 1minute. I've attempted to use the onTime method, however, after further reading i've found that this is not reference to access, but only excel.

Has anyone ever had a similar issue - also thought about using the sleep method, however, that does recall the procedure?!

Thx, J
 
Ok sorted this issue

Using VBA Editor Access
Click Tools > References
Select Microsoft Excel (Office 11) for 2003

Now the object references for excel are now loaded into access

use the following to create excel application object to use application.methods

Dim appXL As New Excel.Application
Set appXL = CreateObject("Excel.Application")
appXL.OnTime Now + TimeValue("00:00:30"), "yourProcedure"
Set appXL = Nothing

Thanks,
J
 
have another issue with this.

when i call my procedure, nothing happens...im assuming this is because it's an excel reference only calls macros in excel?
 
The question is, what is going on in the foreground (if anything) while this procedure of yours is looping?

It is possible to use a form with a timer. You would put 60000 (60K) in the form-name.timer property. 60K milliseconds later, the form's OnTimer event fires. You can put whatever you want in the way of VBA into the OnTimer routine. Then when your loop's single-cycle code finishes one cycle, you can decide to let it run again (by reloading the timer) or let it stop (by zeroing the timer). If you want this to be a background operation or a totally transparent operation, you must open this form programmatically and immediately minimize it and make it not visible. I.e. this secondary form is there only as a host for the VBA code.

You can have "global" variables in the form's class module if you were counting something or tracking something from one loop cycle to the next. If you want to communicate this information outside of the form's class module, you would have to put something in a general module that could be accessed by other VBA segments.

This has the effect of putting the code in the background. If you try to put the code in the foreground using a sleep function, you need to remember to use DoEvents (which you can look up in Access Help) so that you don't block other functions in Access. You also could tie up your system when you do that, though.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom