VBA Access - How to make Access wait.

[AxGryndr]

I have seen this issue posted a huge amount of times so I am going to post how to resolve this issue.

Access does not have any type of wait, pause or sleep functions. So the question is always, "How do I make Access VBA wait for some length of time before going on?"

The typicall answer is to create a loop such as:

For i = 1 to 10000000
Next

While this does take time to process it is unreliable and may result in an error. VBA does have the proper tools to be able to create a wait.

TWait = Time
TWait = DateAdd("s", 15, TWait)
Do Until TNow >= TWait
     TNow = Time
Loop

First we have to establish the time that it is now - TWait = Time.

Next we have to determine how long we want to wait. I am using the DateAdd function to add 15 seconds to TWait. For more on the DateAdd funtion use help in Access.

Now, we need to prevent the VBA coding from moving to the next block of code and for that we are using the loop. The Do Until loop does just that, it loops until TWait is greater than or equal too TNow. Each time through the loop the current time is evaluated and then compared to our wait time and if the time to now is not greater than the wait time the code will loop again.

I had to add the greater than to the loop because I found that the time only displays in HH:MM:SS this is not the case for the variable so if the time passes then it is ok to move on.

I had to use this to make Access VBA wait while I was trying to send keystrokes to another program. There is a download lag that I needed to wait until it was complete before I could send the next set of keys. I have not had an issue with this option for waiting.

Hope you find this helpful.

 

[pbaldy]

How about

http://www.mvps.org/access/api/api0021.htm

 

[AxGryndr]

Personally I think that is too much work.

 

[pbaldy]

Certainly the example is more complex than necessary. All you really need is the Declare line and :

sapiSleep 2000

In any case, it's your prerogative not to like it, but this:

"Access does not have any type of wait, pause or sleep functions"

is not really accurate, given the Sleep API available to it.

 

[gemma-the-husky]

ah but why do you want it to wait?

are you opening a form and waiting for it to close - thats a different thing?

 

[AxGryndr]

An API is really complicated and for a lot of people they are just looking for a quick fix. It is not for me to question why you want Access VBA to wait; I am just trying to provide a simple solution that will work.

 

[gemma-the-husky]

its not that

its if the reason for making access wait is to give time for a different process to complete, then there are mechanisms to do that specifically - rather than just wait an arbitrary time, which STILL might not ber long enough.

otherwise what possible purpose can be served by generating information at a slower rate - its not like trying to make a game easier.

 

[Banana]

Just want to add something:

While there may be legitimate reasons for needing Access to wait, be it running a batch job or creating a connection, it is important to realize that it also can be the wrong solution.

I would not use Wait to perform anything such as SendKeys because I perceive SendKeys as a fudge and is likely to be more buggy and problematic (what if the code started running at wrong time? What if the wrong windows had focus? What if the windows layout/shortcuts changed? So many things could go wrong).

In my old project, I tried to manage a command prompt via code, but found it so cumbersome, so buggy that it finally motivated me to look and get a free library and with some wrapping, that provided the same functionality I needed, but as a library, I could manage via code without any of sending keys and waiting nonsense (and crossing my fingers it doesn't error out).

For that reason, I'm generally skeptic whenever we are asked to make Access wait. When this is in a response to sending keys or manipulating windows or such thing, I'd advise to stop and start looking somewhere else.

For those time where we do actually need Access to wait, perhaps so we can do batch job for instance, I would use ShellAndWait function (google for it) instead. This works because it assume we aren't interacting with it, which is usually the case with batch files anyway.

All in all, the reasons for making Access are few and far between, so the first question should be "Do I really need to make it wait or is there a way that doesn't require me to wait or at least contain a state flag I can check on?"

 

[AxGryndr]

The ShellandWait is another overly complicated program that someone created that was much more fluff than a lot of simple programs need. gemma you mention that the time may not be long enough, and that is something that is up to the person using the code to determine. Whether you use the couple lines I wrote or you use what Dev wrote yout still need to know the length of time and it may need to be changed. Banana you metioned an issue with what if things change, again that is up to the programmer to maintain or give the user the option to manipulate. Unforetunately, we all use programs that cannot always be touched directly with code and manpulated. Where this is the case and a programmer has to resort to SendKeys then yes it is neccessary to wait for one process to complete before sending the next set of keys. I shouldn't have to justify the reasons for the use of the code but there are plenty. I just want to provide a simply solution.

 

[Banana]

Don't misunderstand; we're not asking you to justify anything. I put this up for same reasons as you put up your sample- to educate other users. I wanted to shared my exerpience, and I remain convinced that it is far preferable to find a solution that does not involve sending keys, interacting with UI via code and waiting, but as I already noted before, there are in fact legitimate scenarios where we need to have Access wait.

Ultimately, the decision lies with the reader, not with us on what code they will end up using.

 

[gemma-the-husky]

sorry ax, i misunderstood

i thought you were aking how to make a programme wait.

----------
I still feel it is better to understand why we need it to wait - and assuming its some sort of synchronisation issue, see if there is a more appropriate solution

TWait = Time
TWait = DateAdd("s", 15, TWait)
Do Until TNow >= TWait
TNow = Time
Loop

in particular this code, without any doevents will just "hang" access - so you would vertainly want to put the hourglass up before doing it this way.

 

[AxGryndr]

I appreciate the understanding from both of you and gemma I will look at adding the hourglass to the code - good point thanks.

 

[criskober]

The loop was simple enough and exactly what I needed. Short, simple effective. Thanks! I am using this to delay email messages sent from Access. We need to have at least 4 seconds between each message sent to a distribution list.

Thanks for sharing the solution!

 

[ghudson]

I use the below custom Pause function [set to .1 seconds] to allow my status labels to show the updated message when running or calling multiple processes in one Sub or Function. The Sleep API or DoEvents, etc. does not work for the label does not always update but the Pause function does.

Public Function Pause(NumberOfSeconds As Variant)
On Error GoTo Err_Pause

    Dim PauseTime As Variant, start As Variant

    PauseTime = NumberOfSeconds
    start = Timer
    Do While Timer < start + PauseTime
    DoEvents
    Loop

Exit_Pause:
    Exit Function

Err_Pause:
    MsgBox Err.Number & " - " & Err.Description, vbCritical, "Pause()"
    Resume Exit_Pause

End Function

'This is all I need to run it for .1 seconds

    If CurrentProject.AllForms("myForm").IsLoaded Then Forms![myForm].[lblStatus].Caption = "Running report #1, please wait..."
    Pause (0.1)

 

[bulbisi]

Just working well, simple, so exactly what I wanted.

2 variables were running, but the second one needed the result of the first one. The time between was too short (or my computer too fast) and the second was therefore empty of values; so I needed to create a pause (1 second enough!).

 

[Xproterg]

How about something even simpler?

Public Sub PauseApp(PauseInSeconds As Long)
    
    Call AppSleep(PauseInSeconds * 1000)
    
End Sub

Then call it anytime during your program with:

PauseApp 5

 

[pbaldy]

Is AppSleep something new? As expected, that won't compile in 2000 or 2007.

 

[JANR]

I beleive it is a User-Defined-Function which Xprotag should have provided along with his code exemple to be of any use.

JR

 

[pbaldy]

I assume so too, but that means it isn't "even simpler".

 

[gray]

Thanks Ghudson.. just the job!