Tags: access, code, couple, database, delay, microsoft, mysql, oracle, run, running, seconds, sql, time, vba
time delay in vba?
On Database » Microsoft Access
12,444 words with 17 Comments; publish: Fri, 07 Dec 2007 19:15:00 GMT; (25078.13, « »)
how do i get vba to run some code then wait a couple of seconds before running the rest?
http://ms-access.itags.org/q_ms-access-database_188477.html
All Comments
Leave a comment...
- 17 Comments
- I have been using this recently since someone put me onto it. There are bodgy ways using VBA but they are processor intensive. This is an API call that stops processing of the thread:
Private Declare Sub AppSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
Public Sub PauseApp(PauseInSeconds As Long)
Call AppSleep(PauseInSeconds * 1000)
End Sub
call it with:
'My code doing stuff and now I want a pause for three seconds
PauseApp 3
'My code resumes doing stuff
#1; Tue, 11 Dec 2007 20:54:00 GMT
- I have been using this recently since someone put me onto it. There are bodgy ways using VBA but they are processor intensive. This is an API call that stops processing of the thread:
Private Declare Sub AppSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
Public Sub PauseApp(PauseInSeconds As Long)
Call AppSleep(PauseInSeconds * 1000)
End Sub
call it with:
'My code doing stuff and now I want a pause for three seconds
PauseApp 3
'My code resumes doing stuff
But then again Poots, it stops all processing ... What if you want to do something else in that slice of time? :D Just being argumentative here ...
What about that ol delay routine of mine that does the time compare ...
#2; Tue, 11 Dec 2007 20:55:00 GMT
- But then again Poots, it stops all processing ... What if you want to do something else in that slice of time? :D Just being argumentative here ...
What about that ol delay routine of mine that does the time compare ...I think that it just stops processing of that thread... this is why I shouldn't make comments about things I don't really know - some smartie calls your bluff lol.
I assume your code is a bit like (pseudo):
Dim t as Single
t = timer
Do While t + NumberOfSeconds > Timer
Loop?? If so - that is what I used to. Someone objected to that code and suggested the API call.
See what you think - is your code different\ better?
http://www.experts-exchange.com/Databases/MS_Access/Q_21876140.html
#3; Tue, 11 Dec 2007 20:56:00 GMT
- But then again Poots, it stops all processing ... What if you want to do something else in that slice of time? :D Just being argumentative here ...
Ah - I see - like if the user started navigating the app. Hmmm - yes - a problem.
#4; Tue, 11 Dec 2007 20:57:00 GMT
- I think that it just stops processing of that thread... this is why I shouldn't make comments about things I don't really know - some smartie calls your bluff lol.
I assume your code is a bit like (pseudo):
Dim t as Single
t = timer
Do While t + NumberOfSeconds > Timer
Loop?? If so - that is what I used to. Someone objected to that code and suggested the API call.
See what you think - is your code different\ better?
http://www.experts-exchange.com/Databases/MS_Access/Q_21876140.html
Naw ... That code won't work cause the interpreter will optimize it away ... You have to do something like:
Dim StartTime as Date
StartTime = Now
Do While DateDiff("s",StartTime,Now) < [# of seconds desired]
Loop
#5; Tue, 11 Dec 2007 20:58:00 GMT
- you might want to include a "do events", or whatever its called to allow the rest of the applcations running, or the OS to get a look in on the processor -or has that been made redundant these days with XP SP2?#6; Tue, 11 Dec 2007 20:59:00 GMT
- and here all this time I thought
for x=1 to 10000:next x
was the way to go. Turns out there's a harder way... ;)
#7; Tue, 11 Dec 2007 21:00:00 GMT
- that shoudl work, but the acxtualk dealy will vary depending on what machine you are running the app on - it will vary with processor/memory/network/other apps runiing
MOwens siolutinb will actuall delay for a sepcified number of seconds
#8; Tue, 11 Dec 2007 21:01:00 GMT
- and here all this time I thought
for x=1 to 10000:next x
was the way to go. Turns out there's a harder way... ;)
See what I posted for Pootle ... The interpreter will optimize this away ... No significant delay will occur (I tried this same thing myself long ago) ...
#9; Tue, 11 Dec 2007 21:02:00 GMT
- See what you think - is your code different\ better?
http://www.experts-exchange.com/Databases/MS_Access/Q_21876140.html
PF... your link points to a paid subscription site. Most of us can't see the solution. That site always comes up when you google a computer question.
I've often wondered if it was legit. Why pay for something that you can
probably find for free...
#10; Tue, 11 Dec 2007 21:03:00 GMT
- Now if you really want to take a simple concept and complicate it even more, use M Owen's concept and account for midnight. If you happen to pause just before midnight and then during the pause it becomes another day then you will pause for a day. I had an app that had to run all night and pause several times, so I wrote:
Public Sub Pause(sinSeconds As Single)
Dim sinStartTime As Single
Dim sinFinishTime As Single
'Get the current number of seconds since the beginning of the day
sinStartTime = Timer
sinFinishTime = sinStartTime + sinSeconds
'Adjust for midnight
If sinFinishTime > 86400 Then sinFinishTime = sinFinishTime - 86400
Do Until Timer > sinFinishTime
DoEvents
Loop
End Sub
#11; Tue, 11 Dec 2007 21:04:00 GMT
- another complicated way using timer (and adding the additional complication of a multi-use timer just for fun)
dim switchTimer as integer 'formwide switch for multi-use timer
private sub stuffToRunBeforePause()
'your before code here
switchTimer = 1 'use the timer to delay run of stuffToRunAfterPause
me.timerinterval = 2000 'set 2 secs
end sub
private sub stuffToRunAfterPause()
'your after code here
end sub
private sub form_timer()
select case switchTimer
case 1 'this is your pause
me.timerinterval = 0
stuffToRunAfterPause
case 2
'this is some other use of the timer
case 3
'and yet another use of the timer
end select
end sub
?? better than loops since the machine is still 'alive' during the pause.
izy
#12; Tue, 11 Dec 2007 21:05:00 GMT
- Naw ... That code won't work cause the interpreter will optimize it away Ah - I see what you mean now. The timer solution is more like your solution than the For x = 0 to 1000. The number of loops processed per millisecond will not affect the time taken in the former though it will with the for x loop.
PF... your link points to a paid subscription site. Most of us can't see the solution. That site always comes up when you google a computer question.
I've often wondered if it was legit. Why pay for something that you can
probably find for free...Oops - forgot you can't see it. It is free to "experts" (yours truly :D) which just means you earn enough points per month answering questions. Experts get unlimited points to spend so that's why I moonlight there. In any case - it is a decent site but yes - why pay when you can get the same for free?
Repeated below:
Im running a section of code in a modle and if my program encounters an error accessing a peice of data, I have it change a status lable to read "Data Access Failed Waiting 5 seconds before trying again..."
I've go this all set so that it will only retry 3 times before giving up attempting to auto access the data.
Whats the best way to setup a timer to wait for 5 seconds before continuing?
CheersI use this:
Public Sub PauseApp(PauseInSeconds As Single)
Dim sngStart As Single
sngStart = Timer
Do While sngStart + PauseInSeconds > Timer
Loop
End Sub
Don't know if anyone has anything better.
HTH
Hi Andy1,
Private Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
In the declarations section then:
Sleep 5000
Where you need to wait. This causes the thread to sleep for a period of time. This may be a better solution as a tight loop could increase processor usage and not allow your other applications to run.
If you use the tight loop approach, ensure that you also use DoEvents to release the processor inside the loop.
Tim Cottee
Yep - I like Tim's solution. Put it this way - I will be changing my function in my app. Always thought it was quick and dirty but never got round to checking out for something better.
#13; Tue, 11 Dec 2007 21:06:00 GMT
- These are some great solutions, but my problem still exists, no matter what kind of delay there is. The reason is this, my code is creating folders on the network, and is also copying, and editing Word documents, excel documents etc. While this is all going on, Access is running code that is not necessarily slow, but looks slow due to opening apps, and creating folders etc. Access looks as if it's locked up, but it's not. So I thought I would create a blank form to open saying "Please wait....bla bla bla". Problem is, the form never really displays because the code runs to fast. I tried to use the built-in "OnTimer" event to start my code after the form is loaded, but just learned the hard way that it is a reoccurring event. :)
Does anyone know how to use the OnTimer event once? In other words, I want a function to run 2 seconds after my form is open. Not every 2 seconds.
Creating a loop to count 2 seconds from now doesn't work because access is still running code, so that form never fully loads "graphically".
#14; Tue, 11 Dec 2007 21:07:00 GMT
- These are some great solutions, but my problem still exists, no matter what kind of delay there is. The reason is this, my code is creating folders on the network, and is also copying, and editing Word documents, excel documents etc. While this is all going on, Access is running code that is not necessarily slow, but looks slow due to opening apps, and creating folders etc. Access looks as if it's locked up, but it's not. So I thought I would create a blank form to open saying "Please wait....bla bla bla". Problem is, the form never really displays because the code runs to fast. I tried to use the built-in "OnTimer" event to start my code after the form is loaded, but just learned the hard way that it is a reoccurring event. :)
Does anyone know how to use the OnTimer event once? In other words, I want a function to run 2 seconds after my form is open. Not every 2 seconds.
Creating a loop to count 2 seconds from now doesn't work because access is still running code, so that form never fully loads "graphically".
Set your Me.TimerInterval to 2000 and in the timer event method of the form do your thing and set the TimerInterval to 0
#15; Tue, 11 Dec 2007 21:08:00 GMT
- Set your Me.TimerInterval to 2000 and in the timer event method of the form do your thing and set the TimerInterval to 0
AAAAHHHH!!! I just figured that out about 1 min before your response!
(pat on back)
Thank you! That is exactly what I was looking for!
#16; Tue, 11 Dec 2007 21:09:00 GMT
- so often ignored :( :(
my suggestion is precisely a run-once timer (plus any number of other timers doing other things (not simultaneously (but you can easily fix the simultaneous issue using a bit pattern)))
and as for the grafix stuff: i have often argued here that it makes sense to open an unbound greets form with .timerinterval = 1
the timer does not tick until the form is fully (grafixly) loaded!!
at that stage a run-once timer event turns off the timer (.timerinterval = 0) and loads the data whilst the happy user is enchanted by your opening screen (and fully convinced that your app is 'responsive' even tho it has done nothing so far).
in your case, the initial (designtime and/or Form_Load) .timerinterval MUST be 1
your Form_Timer() event then looks like
private sub Form_Timer()
select case me.timerinterval
case 1 'you know you just loaded the grafix
me.timerinterval = 2000 'your 2secs delay
'your code to run external stuff goes here
case else 'you know your external stuff is supposed to have completed
me.timerinterval = 0
'your after external stuff goes here
end select
end sub
izy
#17; Tue, 11 Dec 2007 21:10:00 GMT