If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > time delay in vba?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Mar 2003
Posts: 225
time delay in vba?

how do i get vba to run some code then wait a couple of seconds before running the rest?
Reply With Quote
  #2 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
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:
Code:
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:
Code:
'My code doing stuff and now I want a pause for three seconds
 
PauseApp 3
 
'My code resumes doing stuff
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #3 (permalink)  
Old
Grand Poobah
 
Join Date: Sep 2003
Location: MI
Posts: 3,713
Quote:
Originally Posted by pootle flump
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:
Code:
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:
Code:
'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??? Just being argumentative here ...

What about that ol delay routine of mine that does the time compare ...
__________________
Back to Access ... ADO is not the way to go for speed ...
Reply With Quote
  #4 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
Quote:
Originally Posted by M Owen
But then again Poots, it stops all processing ... What if you want to do something else in that slice of time??? 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):
Code:
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/Data..._21876140.html
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #5 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
Quote:
Originally Posted by M Owen
But then again Poots, it stops all processing ... What if you want to do something else in that slice of time??? Just being argumentative here ...
Ah - I see - like if the user started navigating the app. Hmmm - yes - a problem.
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #6 (permalink)  
Old
Grand Poobah
 
Join Date: Sep 2003
Location: MI
Posts: 3,713
Quote:
Originally Posted by pootle flump
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):
Code:
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/Data..._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
__________________
Back to Access ... ADO is not the way to go for speed ...
Reply With Quote
  #7 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,080
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?
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #8 (permalink)  
Old
Village Idiot
 
Join Date: Jul 2003
Location: Michigan
Posts: 1,941
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...
__________________
Inspiration Through Fermentation
Reply With Quote
  #9 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,080
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
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #10 (permalink)  
Old
Grand Poobah
 
Join Date: Sep 2003
Location: MI
Posts: 3,713
Quote:
Originally Posted by RedNeckGeek
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) ...
__________________
Back to Access ... ADO is not the way to go for speed ...
Reply With Quote
  #11 (permalink)  
Old
Village Idiot
 
Join Date: Jul 2003
Location: Michigan
Posts: 1,941
Quote:
Originally Posted by pootle flump
See what you think - is your code different\ better?
http://www.experts-exchange.com/Data..._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...
__________________
Inspiration Through Fermentation
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Location: Chicago, IL
Posts: 1,312
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:

Code:
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
Reply With Quote
  #13 (permalink)  
Old
Cavalier King Charles
 
Join Date: Dec 2002
Location: Préverenges, Switzerland
Posts: 3,739
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
__________________
currently using SS 2008R2
Reply With Quote
  #14 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
Quote:
Originally Posted by M Owen
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.

Quote:
Originally Posted by RedNeckGeek
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 ) 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:
Quote:
Originally Posted by Andy1
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?

Cheers
Quote:
Originally Posted by pootle
I 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
Quote:
Originally Posted by TimCottee
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
Quote:
Originally Posted by pootle
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.
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
Join Date: Oct 2002
Location: Maine
Posts: 41
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".
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On