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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Andrew_WebsterConnect With a Mentor Commented:
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.