By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,368 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Progress Indicator in Access

NeoPa
Expert Mod 15k+
P: 31,489
Progress Indicator in Access


Introduction
Access has a bad habit of getting tied up with its processing and not giving a clue as to whether it has crashed or not (The operating system and tools simply indicate that the application is Not Responding, which is the same message seen when an application really has crashed). An unfortunate result of this, understandable in a way, is that many users assume that it has crashed when it hasn't. Unfortunately, their response to this is often to crash out forcibly anyway, and start again. This can be quite a problem, as other than the fact that this is one of the most reliable ways found to cause database corruptions, it can also trigger problems due to code not often being designed to be self-recovering (IE. If the code is made up of blocks A, B & C, then it is necessary for the blocks to be run singly and in sequence. If A runs, then B fails to run, thereby causing the process to start again, block A will run again. The code was never designed to support A running twice before block B runs). As an aside, Access (DAO and ADODB) does support transactional processing - (BeginTrans, CommitTrans, Rollback Methods, but many databases don't incorporate this in their designs).


Suggested Solution
Let me first introduce the concept of the Progress Indicator in the Status Bar. Application.SysCmd() provides that facility. It is not my intention to go into any further detail on that here, but the Help system describes it in full for anyone who's interested.

The alternative concept, that I do intend to discuss, uses a non-modal form. I include an image of the design here to illustrate the basic concept. This is a more complicated version than the basic requirement, but over time I've added useful complexities, and as they are already available I thought they may as well be included.


The idea is that the form is displayed at the start of the running code, with the descriptions of the main steps passed to it beforehand, and a call is made to update the display when each step is completed. When control returns to the operator at the end the form is allowed to stay visible for a pre-determined period (Two seconds is the default), but then it is closed. The operator can choose to clear it within that final delay period by clicking on the title if they wish. An image of how it looks when run live (This is after completing the whole set of tasks) is included here as an illustration.



To avoid the problem of the form disappearing when, for instance, the operator clicks somewhere else on the application, the timer routine is set to reselect the form every 1/4 of a second. This ensures there is never any reason for an operator to panic and crash the application.


Implementation

Notice that there are a number of controls appearing in a list below the top label, which says Please wait... Each row consists of two controls: one to indicate the status (Not started; Running; Completed; Hidden (not to be run on this occasion)), and the other simply to show the caption of each task. There are 25 rows in all, and each row consists of lblTicknn and lblLabelnn where nn reflects the two-digit row number starting from 00 (lblTick00, lblLabel00, lblTick01, ..., lblTick24, lblLabel24).

Any unnecessary rows are hidden from sight before the form is displayed, so it is only ever as large as it needs to be for the tasks at hand.

The code for the form itself is included here :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. 'The frmProgress form is designed to stay visible for about 2" after it expires.
  4. 'However, the operator can cancel the delay if he clicks on the form's title.
  5. '11/5/2006  Allows ten entries.
  6. '15/5/2006  Resize form to handle only the number of entries required.
  7. '           This cannot work as the form size itself never changes on screen.
  8. '18/08/2008 Tried again using Access 2003
  9.  
  10. Private Const conMaxStep As Integer = 24    'Steps = conMaxSteps + 1 (From 0)
  11. Private Const conDelSecs As Integer = 2     'Default delay in secs
  12. Private Const conProgSep As String = "~"    'Separator character within strMsgs
  13. Private Const conCross As Long = &HFB       'Wingdings cross
  14. Private Const conTick As Long = &HFC        'Wingdings tick
  15. Private Const conCM As Long = &H238         'Centimeter
  16.  
  17. 'intPeriod 1/4"s counted after completion; intDelay 1/4"s to count;
  18. 'intLastStep is the last step used on the form
  19. Private intPeriod As Integer, intDelay As Integer, intLastStep As Integer
  20. Private lblTicks(0 To conMaxStep) As Label, lblSteps(0 To conMaxStep) As Label
  21.  
  22. Private Sub Form_Open(Cancel As Integer)
  23.     Dim strStep As String
  24.     Dim ctlThis As Control
  25.  
  26.     'Assign all labels to the arrays.  Ignore any failures.
  27.     On Error Resume Next
  28.     For Each ctlThis In Controls
  29.         strStep = Right(ctlThis.Name, 2)
  30.         Select Case Left(ctlThis.Name, 7)
  31.         Case "lblTick"
  32.             Set lblTicks(CInt(strStep)) = ctlThis
  33.         Case "lblStep"
  34.             Set lblSteps(CInt(strStep)) = ctlThis
  35.         End Select
  36.     Next ctlThis
  37.     On Error GoTo 0
  38. End Sub
  39.  
  40. 'intStep = 0            Reset all and set up captions
  41. 'intStep = Positive     Operate on relevant (intStep-1) line of the display
  42. 'intStep = Negative     Close Progress form after processing -intStep
  43.  
  44. '  intState = 0         Not started yet - visible / dim
  45. '  intState = 1         In progress     - visible / bold
  46. '  intState = 2         Completed       - visible / ticked
  47. '  intState = 3         Hidden          - visible / dim / crossed
  48. '  intState = 4         In progress for intStep - Completed for previous step
  49. '  intState = 5         In progress for intStep - Hidden for previous step
  50. Public Sub SetStep(ByVal intStep As Integer, _
  51.                    Optional ByVal intState As Integer = -1, _
  52.                    Optional ByRef strMsgs As String = "", _
  53.                    Optional ByVal intDelSecs As Integer = -1, _
  54.                    Optional ByVal dblCM As Double = 0)
  55.     Dim intIdx As Integer, intTop As Integer
  56.     Dim lngSize As Long
  57.     Dim blnClose As Boolean
  58.  
  59.     'Cancel any pending close (see Timer code)
  60.     intPeriod = 0
  61.     'Default intDelSecs if not set
  62.     If intDelSecs = -1 Then intDelSecs = conDelSecs
  63.     'Default intState depending on intStep
  64.     If intState = -1 Then
  65.         Select Case intStep
  66.         Case 0              'Open - Default = 1 In progress
  67.             intState = 1
  68.         Case Is > 0         'Change step - Default = 4 Complete & In progress
  69.             intState = 4
  70.         Case Is < 0         'Close - Default = 2 Complete
  71.             intState = 2
  72.         End Select
  73.     End If
  74.     Select Case Abs(intStep)
  75.     Case 0      'Reset all and set up captions
  76.         intDelay = intDelSecs * 4 + Sgn(intDelSecs)
  77.         'find number of elements in strMsgs
  78.         intTop = UBound(Split(strMsgs, conProgSep))
  79.         If intTop > conMaxStep Then intTop = conMaxStep
  80.         For intIdx = 0 To conMaxStep
  81.             If intIdx > intTop Then
  82.                 lblTicks(intIdx).Visible = False
  83.                 lblSteps(intIdx).Visible = False
  84.             Else
  85.                 lblSteps(intIdx).Visible = True
  86.                 lblSteps(intIdx).Caption = Split(strMsgs, conProgSep)(intIdx)
  87.                 Call SetState(intStep:=intIdx, _
  88.                               intState:=IIf(intIdx = 0, intState, 0))
  89.             End If
  90.         Next intIdx
  91.         'Resize form depending on # of lines used and lngWidth passed
  92.         With Me
  93.             If intTop < conMaxStep Then
  94.                 lngSize = (conMaxStep - intTop) * conCM / 2
  95.                 .boxInner.Height = .boxInner.Height - lngSize
  96.                 .boxOuter.Height = .boxOuter.Height - lngSize
  97.                 .InsideHeight = .InsideHeight - lngSize
  98.                 'Following line depends on Access 2003
  99.                 Call .Move(Left:=.WindowLeft, Top:=.WindowTop + lngSize / 2)
  100.             End If
  101.             If dblCM > 0 Then
  102.                 lngSize = dblCM * conCM
  103.                 .lblTitle.Width = .lblTitle.Width - lngSize
  104.                 .boxInner.Width = .boxInner.Width - lngSize
  105.                 .boxOuter.Width = .boxOuter.Width - lngSize
  106.                 .InsideWidth = .InsideWidth - lngSize
  107.                 For intTop = intTop To 0 Step -1
  108.                     lblSteps(intTop).Width = lblSteps(intTop).Width - lngSize
  109.                 Next intTop
  110.                 'Following line depends on Access 2003
  111.                 Call .Move(Left:=.WindowLeft + lngSize / 2)
  112.             End If
  113.         End With
  114.     Case 1 To conMaxStep + 1
  115.         Call SetState(Abs(intStep) - 1, intState)
  116.     End Select
  117.     If intStep < 0 Then     'Close Progress form
  118.         If intDelay = 0 Then Call CloseMe
  119.         'Otherwise start timer
  120.         intPeriod = 1
  121.     End If
  122.     'Update the screen
  123.     DoEvents
  124. End Sub
  125.  
  126. Private Sub SetState(intStep As Integer, intState As Integer)
  127.     lblTicks(intStep).Caption = Chr(conTick)
  128.     lblSteps(intStep).FontBold = False
  129.     Select Case intState
  130.     Case 0          'Not started yet (dim)
  131.         lblTicks(intStep).Visible = False
  132.         lblSteps(intStep).ForeColor = vbBlue
  133.     Case 1, 4, 5    'In progress (bold)
  134.         lblTicks(intStep).Visible = False
  135.         lblSteps(intStep).ForeColor = vbRed
  136.         lblSteps(intStep).FontBold = True
  137.         If intState > 3 And intStep > 0 Then _
  138.             Call SetState(intStep:=intStep - 1, intState:=intState - 2)
  139.     Case 2      'Completed (Tick)
  140.         lblTicks(intStep).Visible = True
  141.         lblSteps(intStep).ForeColor = vbRed
  142.     Case 3      'Hidden (dim / cross)
  143.         lblTicks(intStep).Caption = Chr(conCross)
  144.         lblTicks(intStep).Visible = True
  145.         lblSteps(intStep).ForeColor = vbBlue
  146.     End Select
  147.     'Always bring frmProgress to front when updating
  148.     Call DoCmd.SelectObject(ObjectType:=acForm, ObjectName:=Me.Name)
  149.     'Update the screen
  150.     DoEvents
  151. End Sub
  152.  
  153. Private Sub lblTitle_Click()
  154.     If intPeriod > 0 Then Call CloseMe
  155. End Sub
  156.  
  157. Private Sub Form_Timer()
  158.     Select Case intPeriod
  159.     Case 0
  160.         Exit Sub
  161.     Case Is < intDelay
  162.         intPeriod = intPeriod + 1
  163.         Call DoCmd.SelectObject(ObjectType:=acForm, ObjectName:=Me.Name)
  164.     Case Else
  165.         Call CloseMe
  166.     End Select
  167. End Sub
  168.  
  169. Private Sub CloseMe()
  170.     Call DoCmd.Close(ObjectType:=acForm, ObjectName:=Me.Name)
  171. End Sub


Instructions for Use
The code to use this is fairly straightforward at its most basic, but does provide flexibility for quite involved processes containing many tasks.


Example Code
Here is the code used in the attached example database :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private frmProg As Form_frmProgress
  5.  
  6. Private Sub Form_Open(Cancel As Integer)
  7.     Call DoCmd.Restore
  8.     If DBWindowVisible() Then
  9.         Call DoCmd.SelectObject(ObjectType:=acForm, InDatabaseWindow:=True)
  10.         Call DoCmd.RunCommand(Command:=acCmdWindowHide)
  11.     End If
  12. End Sub
  13.  
  14. Private Sub cmdTest_Click()
  15.     Dim strMsgs As String
  16.     Dim datStart As Date
  17.  
  18.     strMsgs = "Task taking 5 seconds~" & _
  19.               "This task takes just 1 second~" & _
  20.               "This task is skipped~" & _
  21.               "This task takes 20 seconds"
  22.     Set frmProg = New Form_frmProgress
  23.     Call frmProg.SetStep(intStep:=0, strMsgs:=strMsgs)
  24.     datStart = Now()
  25.     Do
  26.         DoEvents
  27.     Loop While Now() < (datStart + (5 / 86400))
  28.     Call frmProg.SetStep(intStep:=2)
  29.     datStart = Now()
  30.     Do
  31.         DoEvents
  32.     Loop While Now() < (datStart + (1 / 86400))
  33.     Call frmProg.SetStep(intStep:=3, intState:=4)
  34.     Call frmProg.SetStep(intStep:=4, intState:=5)
  35.     datStart = Now()
  36.     Do
  37.         DoEvents
  38.     Loop While Now() < (datStart + (20 / 86400))
  39.     Call frmProg.SetStep(intStep:=-4)
  40. End Sub
  41.  
  42. Private Sub cmdExit_Click()
  43.     Call DoCmd.Close
  44. End Sub
  45.  
  46. Private Sub Form_Close()
  47.     'Method must exist in order for container to handle event.
  48.     If Not DBWindowVisible() Then _
  49.         Call DoCmd.SelectObject(ObjectType:=acForm, InDatabaseWindow:=True)
  50. End Sub
This is mostly dummy code, but the important lines are :
  1. #18 through #21 - Sets the captions for each of the four tasks.
  2. #22 creates the instance of the frmProgress form.
  3. #23 sets it up and passes the captions (in strMsgs) it needs to deal with.
  4. #28, #33, #34 & #39 deal with updating the status for the various lines.
  5. #39 particularly, as the negative number indicates that frmProgress should start the timer for closing itself down.
There are more options to explore in the code, but this covers the basics.
Attached Images
File Type: jpg frmProgress.jpg (84.4 KB, 5195 views)
File Type: jpg frmProgressLive.Jpg (12.5 KB, 4768 views)
Attached Files
File Type: zip frmProgress.Zip (1.15 MB, 15796 views)
Feb 5 '11 #1
Share this Article
Share on Google+
6 Comments


ADezii
Expert 5K+
P: 8,637
@NeoPa - First of all, let me compliment you on you excellent Progress Indicator in Access. I find it to be a very useful and flexible Tool that I will use in my Apps that will deploy multiple Processes. I also thank you for sharing this with us here at Bytes. I must be a little dense lately, because there are just a couple of points that I cannot fathom at this time. If you could answer these questions, I would be very grateful. Thanks in advance.
  1. The actual Value passed to the SetStep() Sub-Routine does not appear to matter, only if this Value is =0, < 0, or > 0. Is this correct?
  2. What is the difference between the State Values of 1, 4, and 5?
  3. What exactly does a State Value of 3 designate?
P.S. - Again, I hope this period of not being able to comprehend is brief! (LOL). As always, a pleasure.
Feb 6 '11 #2

NeoPa
Expert Mod 15k+
P: 31,489
ADezii, if you don't see something, there's a good chance that it means many (most) others won't either. I'm therefore more than happy to clarify any points you bring up, on the understanding that they are likely to benefit many more people than just you.
  1. The actual Value passed to the SetStep() Sub-Routine does not appear to matter, only if this Value is =0, < 0, or > 0. Is this correct?
    No. Absolutely not correct.

    While the Select Case Abs(intStep) statement of line #74 controls whether the code should set the form up (if it's zero) or manage an update in the form (if it's non-zero), the code at line #115 (Call SetState(Abs(intStep) - 1, intState)) calls a procedure, passing the the value of Abs(intStep) - 1. This value determines which of the possible messages gets their status updated. Without a specific caption number passed this would be impossible to determine, so that value is definitely required and crucial to the working of the form.
  2. What is the difference between the State Values of 1, 4, and 5?
    State #4 & #5 are shortcuts. Essentially, the calls to say "Set step #2 to completed, then set step #3 to In progress" can be handled by the single call using intStep:=3 and intState:=4. In code that would be :
    Expand|Select|Wrap|Line Numbers
    1. Call Call frmProg.SetStep(intStep:=3, intState:=4)
    instead of using the longer form of :
    Expand|Select|Wrap|Line Numbers
    1. Call Call frmProg.SetStep(intStep:=2, intState:=2)
    2. Call Call frmProg.SetStep(intStep:=3, intState:=1)
    The code using intState:=5, equally, could replace two lines for intState values of 3 & 1.
  3. What exactly does a State Value of 3 designate?
    This is used within a process which includes steps which are conditional on other factors. An example might be a step to delete a table which comes before a step to create that table again. If a prior step indicates that table doesn't already exist, the code would not execute the step to delete the table. On the form this would be indicated by that step being set to Hidden (State #3).

I hope this helps to clarify things.
Feb 6 '11 #3

ADezii
Expert 5K+
P: 8,637
Thanks for taking up the time to clear up a fogged mind.
Feb 6 '11 #4

NeoPa
Expert Mod 15k+
P: 31,489
Always a pleasure, and it doesn't just help you that I make the points easier to follow ;-)
Feb 7 '11 #5

Nauticalgent
P: 92
NeoPa,

I will be implementing this code ASAP, thanks for sharing. I will post in a new thread as to why I THINK I shouldn't need it and hopefully we can resolve that issue.

Thanks again.
Jul 6 '18 #6

NeoPa
Expert Mod 15k+
P: 31,489
I'll keep a lookout for it :-)
Jul 6 '18 #7