By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,762 Members | 1,838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,762 IT Pros & Developers. It's quick & easy.

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

gnawoncents
100+
P: 214
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?
Jun 19 '17 #1

✓ answered by 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.

Share this Question
Share on Google+
5 Replies


PhilOfWalton
Expert 100+
P: 1,430
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
Jun 19 '17 #2

NeoPa
Expert Mod 15k+
P: 31,489
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.
Jun 19 '17 #3

gnawoncents
100+
P: 214
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!
Jun 19 '17 #4

NeoPa
Expert Mod 15k+
P: 31,489
Hi Gnawoncents.

I guess you were trying to get it to reflect how far through it got on individual queries then yes?
Jun 19 '17 #5

jforbes
Expert 100+
P: 1,107
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.

Expand|Select|Wrap|Line Numbers
  1. ' This is on the MainMenu Form, which is always open.
  2. '----------------------------------------------------
  3. Option Compare Database   'Use database order for string comparisons
  4. Option Explicit
  5. Private Const nTimerInterval = 15000
  6.  
  7. Private Sub Form_Timer()
  8. On Error GoTo ErrorOut
  9.  
  10. Dim sSpinner As String
  11.  
  12. Select Case gStatusCount Mod 5
  13.     Case 0
  14.         sSpinner = " | "
  15.     Case 1
  16.         sSpinner = " / "
  17.     Case 2
  18.         sSpinner = " - "
  19.     Case 3
  20.         sSpinner = " \ "
  21.     Case 4
  22.         sSpinner = " | "
  23. End Select
  24.  
  25. Select Case gStatusCount
  26.     Case Is < 0
  27.         Call setStatus(Right(gStatusText, Len(gStatusText) / 20 * gStatusCount))
  28.         Me.TimerInterval = mBlinkIntervalShort
  29.     Case Is < 60
  30.         Call setStatus(sSpinner & " " & gStatusText)
  31.         Me.TimerInterval = mBlinkIntervalLong
  32.     Case Else
  33.         Me.TimerInterval = 0
  34.         Call setStatus("")
  35. End Select
  36.  
  37. gStatusCount = gStatusCount + 1
  38.  
  39. ExitOut:
  40.     Exit Sub
  41.  
  42. ErrorOut:
  43.     Resume ExitOut
  44. End Sub
  45.  
  46.  
  47. ' This code is in a Module
  48. '--------------------------
  49. Public gStatusCount As Integer
  50.  
  51.     'isLoaded
  52. Function isLoaded(ByRef sFormName As String) As Boolean
  53.     ' Determines if a Form is loaded
  54.     If Len(sFormName) > 0 Then
  55.         isLoaded = CurrentProject.AllForms(sFormName).isLoaded
  56.     End If
  57. End Function
  58.  
  59.     'setStatus
  60. Public Sub setStatus(ByRef sStatus As String)
  61.  
  62.     ' Sets the Status Bar Text
  63.     If sStatus = "" Then
  64.         Call SysCmd(acSysCmdClearStatus)
  65.         If isLoaded("MainMenu") Then
  66.             Forms("MainMenu").TimerInterval = 0
  67.         End If
  68.     Else
  69.         Call SysCmd(acSysCmdSetStatus, sStatus)
  70.         If isLoaded("MainMenu") Then
  71.             If Forms("MainMenu").TimerInterval = 0 Then
  72.                 Forms("MainMenu").txtSessionHistory.Value = Forms("MainMenu").txtSessionHistory.Value & vbCrLf & sStatus
  73.             End If
  74.         End If
  75.     End If
  76.     DoEvents
  77. End Sub
  78.  
  79.     'resetStatus
  80. Public Sub resetStatus(ByRef sStatus As String)
  81.     ' Sets the Status Bar Text, and clears the History
  82.     If sStatus = "" Then
  83.         Call SysCmd(acSysCmdClearStatus)
  84.         If isLoaded("MainMenu") Then
  85.             Forms("MainMenu").TimerInterval = 0
  86.             Forms("MainMenu").txtSessionHistory.Value = ""
  87.         End If
  88.     Else
  89.         Call SysCmd(acSysCmdSetStatus, sStatus)
  90.         If isLoaded("MainMenu") Then
  91.             If Forms("MainMenu").TimerInterval = 0 Then
  92.                 Forms("MainMenu").txtSessionHistory.Value = sStatus
  93.             End If
  94.         End If
  95.     End If
  96.     DoEvents
  97. End Sub
  98.  
  99. Public Sub setStatusBlink(ByRef sText As String)
  100.     gStatusCount = 0
  101.     gStatusText = sText
  102.     setStatus (sText)
  103.     If isLoaded("MainMenu") Then Forms("MainMenu").TimerInterval = 10
  104. End Sub
  105.  
  106.  
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

Attached Images
File Type: jpg StatusBar6.jpg (47.1 KB, 60 views)
File Type: jpg Untitled.jpg (61.6 KB, 52 views)
Jun 22 '17 #6

Post your reply

Sign in to post your reply or Sign up for a free account.