How to periodically run an event while another procedure is operating?

[gnawoncents]

I am running VBA code that takes a while to execute and set up a popup form to notify the user to "Please wait." On this form I have two text boxes (txtUpdate1 and txtUpdate2).

I would like to periodically update the text in the boxes to provide status and/or other words to let the user know the program is still running.

I have tried to use the timer event on the popup form or the timer event on the original form (referencing the text boxes on the popup), neither of which worked.

It appears that the On Timer event does not run while my other procedure is running. Is there any way to fix this?

**Edit for Clarification:
This process is not a single loop or operation involving a set number of records or a set time. In short, a progress meter is not a realistic option for me. This is why I want to edit the text boxes every 5 seconds or so until the entire sub has completed.

Is there any way to get the On Timer event to work in this situation or to use some other method to periodically update the text boxes, based on the constraints above?

[PhilOfWalton]

There are 2 methods

1) Use the built in Progress Meter; It is well documented using Access Help. The problem is it is an insignificant bar at the bottom of the screen.

I prefer to use a more colourful & informative version. See image below.

The progress meter is the small form at the top left, the larger form has instructions and allows you to "play" with the meter.

Phil

[NeoPa]

I believe you're out of luck. Both approaches suggested by Phil, and one is certainly very impressive, rely on being managed by the running code. IE. The code must break at certain points (Even if that is only to the extent of running DoEvents every now and again.) to allow the changes to be called for on the screen.

So, your options are :

1. To sew the notification code into your process code.
2. Include DoEvents code in your process code as much as possible and have timer-based code that can determine what to show purely based on time, or other information that may be available to it.

Neither option's perfect but if you code consistently to use either it should enhance your projects as far as usability is concerned.

PS. I've not tested the second approach personally but your question caused me to think about it and I don't see a reason why it wouldn't work. Easy enough to test if you're interested, of course.

PPS. When running anything query-based it's not possible to break within a query itself. It would need to be done between queries.

[gnawoncents]

Thank you both for your replies. I've tried all those methods, and they do work for what they are meant to do. It's helpful just knowing that what I'm trying to do is not possible. I'll stop wasting time on it now. Thanks again!

[NeoPa]

Hi Gnawoncents.

I guess you were trying to get it to reflect how far through it got on individual queries then yes?

[jforbes]

Basically what you want to run is what they call a Worker Thread, which aren't available in Access. Multithreading: Creating Worker Threads

So like most people, it's easier to work within Access' boundaries and with Access' tools. I use the Status Bar. I'll provide you with a stripped down version of my code.

' This is on the MainMenu Form, which is always open.
'----------------------------------------------------
Option Compare Database   'Use database order for string comparisons
Option Explicit
Private Const nTimerInterval = 15000
 
Private Sub Form_Timer()
On Error GoTo ErrorOut
 
Dim sSpinner As String
 
Select Case gStatusCount Mod 5
    Case 0
        sSpinner = " | "
    Case 1
        sSpinner = " / "
    Case 2
        sSpinner = " - "
    Case 3
        sSpinner = " \ "
    Case 4
        sSpinner = " | "
End Select
 
Select Case gStatusCount
    Case Is < 0
        Call setStatus(Right(gStatusText, Len(gStatusText) / 20 * gStatusCount))
        Me.TimerInterval = mBlinkIntervalShort
    Case Is < 60
        Call setStatus(sSpinner & " " & gStatusText)
        Me.TimerInterval = mBlinkIntervalLong
    Case Else
        Me.TimerInterval = 0
        Call setStatus("")
End Select
 
gStatusCount = gStatusCount + 1
 
ExitOut:
    Exit Sub
 
ErrorOut:
    Resume ExitOut
End Sub
 
 
' This code is in a Module
'--------------------------
Public gStatusCount As Integer
 
    'isLoaded
Function isLoaded(ByRef sFormName As String) As Boolean
    ' Determines if a Form is loaded
    If Len(sFormName) > 0 Then
        isLoaded = CurrentProject.AllForms(sFormName).isLoaded
    End If
End Function
 
    'setStatus
Public Sub setStatus(ByRef sStatus As String)
 
    ' Sets the Status Bar Text
    If sStatus = "" Then
        Call SysCmd(acSysCmdClearStatus)
        If isLoaded("MainMenu") Then
            Forms("MainMenu").TimerInterval = 0
        End If
    Else
        Call SysCmd(acSysCmdSetStatus, sStatus)
        If isLoaded("MainMenu") Then
            If Forms("MainMenu").TimerInterval = 0 Then
                Forms("MainMenu").txtSessionHistory.Value = Forms("MainMenu").txtSessionHistory.Value & vbCrLf & sStatus
            End If
        End If
    End If
    DoEvents
End Sub
 
    'resetStatus
Public Sub resetStatus(ByRef sStatus As String)
    ' Sets the Status Bar Text, and clears the History
    If sStatus = "" Then
        Call SysCmd(acSysCmdClearStatus)
        If isLoaded("MainMenu") Then
            Forms("MainMenu").TimerInterval = 0
            Forms("MainMenu").txtSessionHistory.Value = ""
        End If
    Else
        Call SysCmd(acSysCmdSetStatus, sStatus)
        If isLoaded("MainMenu") Then
            If Forms("MainMenu").TimerInterval = 0 Then
                Forms("MainMenu").txtSessionHistory.Value = sStatus
            End If
        End If
    End If
    DoEvents
End Sub
 
Public Sub setStatusBlink(ByRef sText As String)
    gStatusCount = 0
    gStatusText = sText
    setStatus (sText)
    If isLoaded("MainMenu") Then Forms("MainMenu").TimerInterval = 10
End Sub
 
 

In this example there is a Large TextBox on a tab on the MainForm that captures every status entry. This allows me to fire off a process that my have a lot of messages and then that message history can be examined after getting coffee.

To set the Status: SetStatus("New Status")
To clear the status: SetStatus("")
To set the Status and clear the history: resetStatus("New Status")
To have a spinner in front of the text for those special messages you want the user to see, like the process is complete: setStatusBlink("All Done")

Example One

Example Two