Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    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?

  2. #2
    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:
    pootle flump
    ur codings are working excelent.

  3. #3
    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 ...

  4. #4
    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:
    pootle flump
    ur codings are working excelent.

  5. #5
    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:
    pootle flump
    ur codings are working excelent.

  6. #6
    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 ...

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,511
    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 on the Tiger 800 or the Norton

  8. #8
    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

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,511
    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 on the Tiger 800 or the Norton

  10. #10
    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 ...

  11. #11
    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

  12. #12
    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

  13. #13
    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

  14. #14
    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:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    44
    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".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •