Form refresh fails when timer fires a function

Hi,
I am having a problem getting a form label to change when the forms timer function is active.
I have a form called 'frmAutoPoll'.  When the form opens, its label is green.  When the timer interval fires, the code should change the label colour to red and put a new caption in there, BEFORE going off to the called procedure AutoPoll (see attached code).  After the function has finished, the timer code (successfully) changes the form label back to green and modifies the caption.
The problem is that while the function AutoPoll fires and runs, the code to change the label to red is being ignored.  If I put a break point in at the procedure call to AutoPoll, the label changes to red correctly; likewise, if I put a message box requiring human interaction.  The idea is that this form will be active 24/7 unless it is stopped by the user.  The idea of the label colour change is just to alert someone where the system is in the cycle should they come and look.
Can anyone advise how I might achieve the label change before the procedure call fires?  I have tried putting the initial label change to red inside the called procedure without success, and I have also tried refresh and repaint.
Thank you.
Keith Blakesley
AutoPoll.txt
keithedwardbAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Andrew_WebsterCommented:
I'd suggest a couple of things.

First, put DoEvents in before and after AutoPoll.

If that doesn't make a difference, then maybe create a procedure that pauses the code for you for a moment, and place that before AutoPoll.

I've had problems like this before - I've never quite understood what's causing them, but by giving things time to "catch up" I've fixed them.  Stopping the code execution with a break point or a messagebox is the clue, I've definitely seen problems like that - you pause it, it works, you don't it doesn'

That's not to say that one of the gurus here won't put their finger on it right away and tell you something different.
0
keithedwardbAuthor Commented:
Hi Andrew,
Thanks. is 'DoEvents' just that, or is it part of a longer syntax? I haven't come across that before.
Also, I used to program in spectrum basic (!) and could pause the code in that, but haven't a clue of the syntax in vba / vb.NET? Any clues there, please?
Thanks again.
0
Andrew_WebsterCommented:
Ok.

DoEvents is simply that.  It tells Access to take a breath so the operating system can process other events.  See http://office.microsoft.com/en-us/access/HA012288271033.aspx.

I've attached my own Pause routine.  There's one catch to it, it uses the "Timer" function.  This counts the number of milliseconds since midnight.  In that applications where I've been using it, it's never going to be run so that it crosses midnight.  If it did it could potentially run indefinitely!  

If you think there's any chance that your users could do something that would have it run over midnight, then let me know, and I'll spend a few moments thinking up a way to avoid that being a problem.
Option Compare Database
Option Explicit

Public Sub Pause(ByVal MillisecondsToWait As Long)
'---------------------------------------------------------------------------------------
' Procedure   : Pause
' DateTime    : 19/08/2008 14:29
' Author      : Andrew
' Purpose     : Pauses for a given number of milliseconds.  Don't expect it to be accurate.
'---------------------------------------------------------------------------------------
'

Dim sngNow As Single
Dim sngLater As Single

'Debug.Print Timer

sngNow = Timer
sngLater = sngNow + MillisecondsToWait / 1000
Do While Timer < sngLater
    DoEvents
Loop

'Debug.Print Timer

End Sub

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

keithedwardbAuthor Commented:
Hi Andrew,
Thank you.  My access program will be polling 24/7, every 15 minutes in a 24-hour working environment.  Do you think that will be an issue.  If not I'll give your code a try.
Thanks.
Keith
0
Andrew_WebsterCommented:
Ok.  If I have a minute during the day I'll see if I can tweak the code.  Check back here later.
0
Andrew_WebsterCommented:
Took me a while to get this back to a machine where I could post it.

Here you go:
Option Compare Database
Option Explicit
 
Public Sub Pause(ByVal MillisecondsToWait As Long)
'---------------------------------------------------------------------------------------
' Procedure   : Pause
' DateTime    : 19/08/2008 14:29
' Author      : Andrew
' Purpose     : Pauses for a given number of milliseconds.  Don't expect it to be accurate.
' Updated     : Added code to handle pausing over midnight.  Again, don't expect this to
'               be accurate - there are too many things that can happen to slow this code
'               so it might well pause for longer than expected.
'---------------------------------------------------------------------------------------
'
 
Dim sngNow                  As Single
Dim sngLater                As Single
Dim lngSecondsToMidnight    As Long
Dim lngMilliseconds         As Long
Dim datNow                  As Date
 
On Error GoTo Pause_Error
 
DoCmd.Hourglass True
 
'Test if we're too close to midnight - how many
'seconds is it from now until midnight tonight?
lngSecondsToMidnight = DateDiff("s", Now(), Date + 1)
lngMilliseconds = lngSecondsToMidnight * 1000
 
If lngMilliseconds > MillisecondsToWait Then
    'We have more milliseconds until midnight than we're expecting to wait
    'so just wait for the milliseconds
    sngNow = Timer
    sngLater = sngNow + MillisecondsToWait / 1000
    Do While Timer < sngLater
        DoEvents
    Loop
Else
    'Too close to midnight, so wait until midnight then pause again for the difference
    datNow = Date + 1   'Tomorrow i.e. midnight tonight
    Do While Now() < datNow
        DoEvents
    Loop
    'How long do we need to wait after midnight?
    Pause MillisecondsToWait - lngMilliseconds
End If
 
Pause_Exit:
    On Error Resume Next 'In case objects don't exit
    'Close objects etc here
    DoCmd.Hourglass False
    Exit Sub
 
Pause_Error:
 
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Pause of Module modTimer"
 
    Resume Pause_Exit
    Resume  'Debugging manually only
 
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
keithedwardbAuthor Commented:
Hi Andrew,
This looks good.  The issue is I am now on vacation and won't be able to try this for a couple of weeks.  Having said that the code looks fine to me so I will accept this as the answer.  If I have another question following the inclusion of the code I'll post back.  Otherwise thanks for your help.

Best Regards,
Keith Blakesley.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.