Progress Counter

Introduction.

This section continues the series of examples and demonstrations presented earlier. It builds upon the Table structure and Form design previously created. If you have landed directly on this page, it is recommended that you review the earlier topics first for proper context and continuity.

You can access the previous pages by clicking the links below or by selecting them from the TRICKS menu tab above.

1. Progress Meter
2. Progress Bar on Form

The new method we are going to explore offers a more visually appealing and responsive user experience. Unlike the earlier approach, where the progress bar advanced based on the percentage of records processed, this method updates a compact, standalone Progress Form at the record level, making it more dynamic and informative.

Please note that this technique cannot be used with Macros, unlike the previous method involving the built-in Progress Bar Control. However, when dealing with time-consuming tasks involving multiple files and large data volumes, this method can be seamlessly integrated into your VBA procedures with just a few lines of code.

An image of the demo run is shown below for reference:

The display is intuitive and self-explanatory. At the beginning of the program, the ProgressCounter form opens automatically, initializing its fields with key information such as the Program Name, Total Records to process, and the Start Time. As the process runs, subsequent calls to the controlling procedure update the number of records processed and the elapsed processing time in real-time.

Once the task is complete, the End Time is recorded, and the form remains visible for four seconds, allowing the user to review the total time taken. After this brief pause, the form closes automatically.

During the execution of this process, the application is locked for user interaction. The Process Control Form cannot be closed, and the user is prevented from interacting with other objects in the database window, ensuring uninterrupted processing.

Creating the Visual Layout for the Progress Counter.

1. Open a new Form in Design View.

2. Add twelve Labels to the Detail Section of the Form.

3. Arrange the Labels in a horizontal sequence or in a visual pattern similar to the layout shown in the illustration below (insert illustration if available).

4. Adjust the size and shape of each Label to maintain a uniform and visually pleasing layout.

   

5. Click on the Top left label on your design, display the Property Sheet (View Menu -> Properties), and type Program: in the Caption Property.

6. Change the Captions of other labels to match the descriptions shown above, following the same procedure.

7. Now, we are left with six Labels, three with dark backgrounds and three with white backgrounds, arranged horizontally at the bottom. Display the Property Sheet of these controls and change their Name Property as given below:

   • Name = lblProgram (label to the right of Program:)
   • Name = TRecs ( -do- Total Records:)
   • Name = PRecs ( -do- Processed:)
   • Name = ST (label below Start Time)
   • Name = PT ( -do- Process Time)
   • Name = ET ( -do- End Time)

8. Draw a Box around the Labels as shown. If the box overlaps the labels when drawn, change its Back-Style Property = Transparent.

9. Next, we must change the Properties of the Form. Click on the Detail Section of the Form or on the horizontal bar above the Detail Section with the description Detail.

10. Display the Property Sheet and change the Height Property Value to 1.2396".  You may modify the design to your own liking.

11. Click on the dark rectangle in the top left corner of the Form to deselect other controls and select the Form, and display the Form's Property Sheet. Change the Properties as given below:

    • Caption = Progress Counter

    • Default View = Single Form

    • Allow Edits = Yes

    • Allow Deletions = No

    • Allow Additions = No

    • Scroll Bars = Neither

    • Record Selectors = No

    • Navigation Buttons = No

    • Auto Resize = Yes

    • Auto Center = Yes

    • Pop Up = Yes

    • Modal = Yes

    • Border Style = Dialog

    • Control Box = No

    • Min Max Buttons = None

    • Close Button = No

    • What's This Button = No

    • Width = 2.3438"

    • Allow Design Changes = Design View Only

12. Display the Code Module of the Form. Select Code from the View Menu. Copy and paste the following Code into the Module, overwriting the existing lines:

    Option Compare Database
    Option Explicit
    Dim i As Integer
    
    Private Sub Form_Timer()
    i = i + 1
    Select Case i
      Case 1 To 16    'do nothing
      Case 17
        Me.TimerInterval = 0
        DoCmd.Close acForm, Me.Name
    End Select
    End Sub

13. Saving the Progress Form

    Once you've finished designing the Form with the labels and formatting:

    • Save the Form with the name: ProcessCounter

      ⚠️ Make sure to use exactly this name—with no spaces between “Process” and “Counter”.

    This specific name is referenced directly in the ProcCounter() function. Using a different name or including a space may cause the program to fail when attempting to open or update the form.

    We will continue with the same demo Form ProgressMeter from our earlier examples. The only change required is updating the Command Button’s On Click property to call the revised program that updates the Order Details table using the new progress tracking method.

14. Open the 'ProgressMeter' form and click on the Process Orders command button.
    Open its Property Sheet (by pressing Alt + Enter or selecting Properties from the ribbon).
    Change the On Click property value to:

    =ProcessOrders3()
    

    Make sure to include the equal sign (=) at the beginning—this indicates you're calling a function rather than referring to a macro name.
    Save and close the form.

I’ve created a copy of our earlier routine ProcessOrders() and modified the three lines that called the ProgMeter2() function to now use our updated Progress Counter logic discussed earlier. Since this version is a copy of the original, I’ve renamed the procedure to ProcessOrders3().

The updated code is provided below for your convenience. You can copy and paste it into a standard module in your database and save the changes.

Public Function ProcessOrders3()
'---------------------------------------------------------
'Author : a.p.r. pillai
'Date   : 05/01/2008
'Remarks: Updates Extended Price on Order Detail Table
'---------------------------------------------------------
Dim db As Database, rst As Recordset
Dim recs As Long, qty As Integer
Dim unitval As Double, ExtendedPrice As Double
Dim Discount As Double, xtimer As Date

Set db = CurrentDb
Set rst = db.OpenRecordset("Order Details", dbOpenDynaset)
rst.MoveLastr
ecs = rst.RecordCount
rst.MoveFirst

ProcCounter 1, 0, recs, "ProcessOrders()"

Do While Not rst.EOF
qty = rst![Quantity]
unitval = rst![UnitPrice]
Discount = rst![Discount]
ExtendedPrice = qty * (unitval * (1 - Discount))
rst.Editrst![ExtendedPrice] = ExtendedPrice
rst.Update

'Time delay loop for demo
'remove in real processing
xtimer = Timer
Do While Timer < xtimer + 0.02'do nothingLoop

ProcCounter 2, rst.AbsolutePosition + 1

rst.MoveNext
Loop

ProcCounter 3, rst.AbsolutePosition
rst.Close

Set rst = Nothing
Set db = Nothing

End Function

For this example, you will need the Order Details table that we previously imported from the Northwind.mdb sample database. I hope the table is still available in your project. If not, please re-import it. If you’ve forgotten the location of the database file, refer to the Saving Data on Form Not in Table page for guidance on locating it.

The ProcCounter() Function.

The ProcessCounter form update program is provided below. Copy and paste the code into a standard (global) module in your project, and then save the module.

Public Function ProcCounter(ByVal intMode As Integer, ByVal lngPRecs As Long, Optional ByVal lngTRecs As Long, Optional ByVal strProgram As String)
'--------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : 5/01/2008
'Remarks: Process Counter Control
'--------------------------------------------------------------
Dim Stime As Double, ptime As Double, ETime As Double
Static m_lngTRecs As Long, m_strProgram As String, FRM As Form

On Error Resume Next

  If intMode = 1 Then
        DoCmd.OpenForm "ProcessCounter", acNormal
        GoSub initmeter
  ElseIf intMode = 2 Then
     GoSub updatemeter
  ElseIf intMode = 3 Then
     FRM.ET.Caption = Format(Now(), "hh:nn:ss")
     FRM.TimerInterval = 250
  End If

ProcMeter_Exit:
Exit Function

initmeter:
   Set FRM = Forms![ProcessCounter]
   m_strProgram = Nz(strProgram, "")
   Stime = Now()

    With FRM
        .lblProgram.Caption = m_strProgram
        m_lngTRecs = lngTRecs
       .TRecs.Caption = m_lngTRecs
       .PRecs.Caption = lngPRecs
       .ST.Caption = Format(Stime, "hh:nn:ss")
       DoEvents
    End With
Return

updatemeter:
   With FRM
        .PRecs.Caption = lngPRecs
        Stime = TimeValue(.ST.Caption)
        ETime = Now()
        ptime = Stime - TimeValue(Format(ETime, "hh:nn:ss"))
        .PT.Caption = Format(ptime, "hh:nn:ss")
        DoEvents
    End With
Return

End Function

The Demo Run

Open the ProgressMeter form in Normal View and click the Process Orders command button. The ProcessCounter form will appear, displaying live updates on the number of records processed. It will also show the Start Time and the Elapsed Time during processing. Once all records in the Order Details table have been processed, the End Time will be updated.

The ProcessCounter form will remain visible for approximately 4 seconds, allowing enough time to review the processing summary, after which it will close automatically.

Note: A delay loop is included in the ProcessOrders3() routine to intentionally slow down the execution for demonstration purposes. You can remove this delay when integrating the code into your production application.

Download the Demo Database.

Download ProgressMeter2007.zip

Download ProgressMeter.zip

• Progress Bar on Form
• Progress Counter
• Progress Meter
• Reminder Pop Up
• MS-Access & Graph Charts-2