LEARN MS-ACCESS TIPS AND TRICKS: progressmeter

Showing posts with label progressmeter. Show all posts

Progress Counter

a.p.r. pillaiJanuary 11, 2008Forms, progressmeter 2 comments

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.

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.

Open a new Form in Design View.
Add twelve Labels to the Detail Section of the Form.
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).
Adjust the size and shape of each Label to maintain a uniform and visually pleasing layout.
Click on the Top left label on your design, display the Property Sheet (View Menu -> Properties), and type Program: in the Caption Property.
Change the Captions of other labels to match the descriptions shown above, following the same procedure.
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)
Draw a Box around the Labels as shown. If the box overlaps the labels when drawn, change its Back-Style Property = Transparent.
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.
Display the Property Sheet and change the Height Property Value to 1.2396". You may modify the design to your own liking.
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

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

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.
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

a.p.r. pillaiJanuary 04, 2008Forms, progressmeter 8 comments

Introduction.

Continued from the previous article: Progress Meter.

We will try the Progress Bar Control on the sample Form that we have already designed earlier, for use with Macros.

Note: This method of using a Progress Bar within a Macro was found to work reliably only in Microsoft Access 2000. When tested in Access 2003, the Progress Bar control failed to update, regardless of the database format used (Access 2000, 2002, or 2003). If any readers have discovered a workaround or solution for this issue, I would greatly appreciate it if you could share it with me.

That said, you can still apply this technique with our earlier VBA program by replacing the SysCmd() lines. This allows you to display the Progress Bar directly on your own Form, instead of relying on the Status Bar, for more controlled and user-friendly transaction-level processing feedback.

The Form Design

Open the 'ProgressMeter' Form in Design View.
Select the ActiveX Control Option from the Insert Menu.
Select Microsoft Progress Bar Control, Version 6.0 from the displayed list.
Draw a Progress Bar on the Form as shown in the image below. Resize the Form to make it look like the sample below. You can make it in any size and shape you prefer.
Select the Progress Bar Control (if you have deselected it) and display the Property Sheet (View --> Properties).
Need to change the following Property Values as shown below:
- Name = PB2
- Visible = False
Create a label on the left side of the ProgressBar. Display its Property Sheet and change the following values:
- Name = lblStatus
- Visible = False
Select the Command Button on the Form, display the Property Sheet, and change the On Click Property.
- On Click = Spl_Report
Spl_Report is a Macro in which we have sequenced our processing steps, and the Progress Bar will show the current status of its activity.
Save the Form after the changes.
Select Options from the Tools Menu. Uncheck the Status Bar under the Show 'Option-group' on the View Tab. Click Apply, and click OK to close the control.
If you have a Macro running several steps of Queries, Macros, and Code, make a copy and rename it as Spl_Report, and modify it for our demo. A sample Macro Image is given below for reference.

The Macro Action Lines
The first two steps on the Macro's Action Field have their values set as No.
The third line calls the ProgMeter2(1, 5, "PB2") Function with the RunCode macro command with three parameters to initialize the Progress Meter. The Values of the parameters represent the following:

1 = indicating that the Progress Bar must be initialized.
5 = This is the maximum number of steps of Queries, other macros, or Functions that we are going to run.
PB2 = The Name of the Progress Bar Control drawn on the Form.

'ProgMeter2()' This function must be called after each query execution step in the Macro to update the Progress Bar. However, subsequent calls for updating the Progress Meter require only a single parameter: ProgMeter2(0), where 0 indicates that the meter should simply advance to the next step.

To implement this:

Insert a RunCode action after each query in your Macro.

In each inserted line, set the function call as ProgMeter2(0).

At the end of the Macro, add one final RunCode action that calls ProgMeter2(2). The parameter 2 signals the function to complete the process and turn off the Progress Bar.

Important: If you later add more steps to the Macro but forget to update the initialization value (i.e., the second parameter in ProgMeter2(1, 5) where 5 is the total number of expected steps), the program will stop updating the meter once it reaches the original maximum step count and will remain idle until it receives the terminating parameter 2.

Save the Spl_Report Macro after the above changes.

The Progress Meter2 Function.
Copy and paste the following VB Code into a Global Module in a Global Module and save it.

Public Function ProgMeter2(ByVal xswitch As Integer, Optional ByVal Maxval As Integer, Optional ByVal strCtrl As String) '---------------------------------------------------------- 'Program : Progress Bar Demo2 'Author : a.p.r. pillai 'Date : 02/01/2008 '---------------------------------------------------------- Static mtrCtrl As Control, i As Integer, xmax As Integer Static lbl As Label, frm As Form Dim position As Integer, xtime As Date On Error GoTo ProgMeter2_Err If xswitch = 1 Then 'init control Set frm = Screen.ActiveForm Set mtrCtrl = frm.Controls(strCtrl) Set lbl = frm.Controls("lblstatus") mtrCtrl.Visible = True lbl.Visible = True xmax = Maxval i = 0 DoEvents ElseIf xswitch = 0 Then i = i + 1 if i > xmax then goto ProgMeter2_Exit end if position = i * (100 / xmax) mtrCtrl.Value = position DoEvents ElseIf xswitch = 2 Then mtrCtrl.Visible = False lbl.Visible = False Set mtrCtrl = Nothing i = 0 xmax = 0 Exit Function End If ProgMeter2_Exit: Exit Function ProgMeter2_Err: MsgBox Err.Description, , "ProgMeter2" Resume ProgMeter2_Exit End Function
The Program Logic is the same as that of SysCmd(), which we used for updating the Status Bar in our earlier example. The only difference is that our program updates the Progress Bar on a Form.
Note: The built-in Function SysCmd() has other implementations too, with a different set of Parameters.

Open the ProgressMeter Form in Normal View. Click on the [Process Orders] Command Button to run the process steps in the Spl_Report Macro.

The Progress Bar will now appear on the Form with the message 'Working...' on the left-side label.
The image of the sample run is given below.
You can implement this method on any Form, such as your Control Screen or a dedicated report parameter Form. When calling the ProgMeter2() function, be sure to use the actual name of the Progress Bar control placed on your Form, replacing "PB2" (in our example) with your control's name. For instance:
vba
ProgMeter2(1, 5, "MyProgressBar")

The first parameter (1) Initializes the Progress Bar.

The second parameter (5) specifies the total number of steps.

The third parameter ("MyProgressBar") is the name of your custom Progress Bar control.

Subsequent calls to update the Progress Bar require only a single parameter:
vba
ProgMeter2(0)
And at the end of the process, use:
vba
ProgMeter2(2)

This final call closes and resets the Progress Bar.

Preventing Pitfalls
Further enhancements to the Form design and underlying code are recommended to prevent unintended user interactions—such as accidentally closing the Form or switching focus to another task—while the process is running. If the Form becomes inactive during execution, it may lead to runtime errors or interrupt the process.
To ensure stability and user focus:

Disable the Close button on the Form.

Set the Form’s Modal and Pop-Up properties to Yes.

Optionally, disable other navigation controls and prevent focus changes during execution.

As mentioned earlier, the ProgMeter2() Function can also be effectively used in VBA routines that perform record-level updates on Tables, providing a clear visual indicator of processing progress and improving user experience during lengthy operations.
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

Progress Meter

a.p.r. pillaiDecember 28, 2007Forms, progressmeter No comments

Introduction.

When processing large volumes of data for Microsoft Access reports, the operation can take anywhere from a few seconds to several minutes, depending on the volume and complexity of the transactions involved. These transactions are typically organized and executed using a combination of Select and Action Queries, orchestrated through Macros or VBA routines to produce the final report output.

If your Action Queries depend on SELECT or CROSSTAB Queries as input sources—particularly when dealing with high transaction volumes—the overall processing time can increase significantly.

In such cases, users may find it difficult to estimate how long the task will take to complete. Over time, with repeated use, they may develop a rough idea of the expected duration.

A common practice is to display the hourglass cursor (DoCmd.Hourglass True) at the beginning of a long-running operation to signal that processing is underway. The hourglass is then reset at the end of the task. However, this visual cue does not provide users with a clear indication of progress or how much time remains until completion.

If the process takes longer than usual—due to factors such as increased transaction volume, heavy network traffic, or other system-related delays—it becomes difficult to determine whether the operation is still actively running or if the system has encountered a hang or failure.

The Quick Solution.

When multiple Action Queries are executed in sequence within a Macro, Microsoft Access briefly displays a Progress Meter on the Status Bar for each query. If the Status Bar is not visible, you can enable it by selecting Options from the Tools menu, navigating to the View tab in the dialog box, and checking the Status Bar option under the Show group. However, this only provides momentary feedback for individual queries and does not indicate the overall progress or estimated time remaining for the entire process.

A Better Approach.

We will explore how to make more effective use of the Progress Meter during data processing tasks by implementing multiple methods. This will allow users of our application to stay informed about the progress, giving them the confidence to momentarily step away or flip through a weekly magazine, while occasionally glancing at the screen to monitor the process.

Displaying the Progress Meter on the Status Bar
Displaying the Progress Meter on a Form
Usage of a transaction countdown method.

Usage of Progress Meter on the Status Bar.

We will use the Order Details table from the Northwind.mdb sample database to demonstrate our example. Our objective is to write a VBA routine that calculates the Extended Price for each entry in this table. If you haven’t already imported the table while working through previous examples, you may do so now. If you're unsure about the exact location of the Northwind.mdb file on your machine, refer to the page Saving Data on Forms Not in Table for guidance.

Steps:

Import the Order Details table from the Northwind.mdb sample database into your project.
Open the table in Design View, and add a new field ExtendedPrice at the end of the existing fields.
- Data Type: Number
- Field Size: Double
Design a simple form with a Command Button on it, similar to the sample shown below. We’ll use and enhance this form for the next example as well.
Assigning the Procedure to the Command Button.
1. Click on the Command Button to select it.
2. Open the Property Sheet (choose View → Properties if it’s not already visible).
3. Locate the On Click property.
4. Enter the following expression in the On Click property:
```
=ProcessOrders()
```
  Note: Make sure to include the equal sign (=) at the beginning. Without it, MS Access will interpret the value as the name of a macro instead of a function call.
5. Close the form and save it with the name: ProgressMeter.
Usage of SysCmd().

Copy and paste the following Code into a Global VB Module of your Project and save it.

Public Function ProcessOrders()
Dim db As Database, rst As Recordset
Dim TotalRecords As Long, xtimer As Date
Dim ExtendedValue As Double, Quantity As Integer
Dim Discount As Double, x As Variant, UnitRate As Double

On Error GoTo ProcessOrders_Err

DoCmd.Hourglass True

Set db = CurrentDb
Set rst = db.OpenRecordset("Order Details", dbOpenDynaset)
rst.MoveLast
TotalRecords = rst.RecordCount

rst.MoveFirst
Do While Not rst.EOF
  With rst
    Quantity = ![Quantity]
    UnitRate = ![UnitPrice]
    Discount = ![Discount]
    ExtendedValue = Quantity * (UnitRate * (1 - Discount))

    .Edit
    ![ExtendedPrice] = ExtendedValue
    .Update

    If .AbsolutePosition + 1 = 1 Then
       x = SysCmd(acSysCmdInitMeter, "process:", TotalRecords)
    Else
      'a delay loop to slow down the program       
      'to view the Progress Meter in action.      
      'you may remove it.      
'=================================================
      xtimer = Timer
      Do While Timer < xtimer + 0.02
        Doevents
      Loop
'=================================================

      x = SysCmd(acSysCmdUpdateMeter, .AbsolutePosition + 1)
    End If

   .MoveNext
  End with
Loop
rst.Close
x = SysCmd(acSysCmdRemoveMeter)
DoCmd.Hourglass False

MsgBox "Process completed.", , "ProcessOrders()"

Set rst = Nothing
Set db = Nothing

ProcessOrders_Exit:
Exit Function

ProcessOrders_Err:
MsgBox Err.Description, , "ProcessOrders()"
Resume ProcessOrders_Exit

End Function

Open the ProgressMeter form in Form View and click the Command Button. You will see the Progress Meter gradually advancing across the status bar. Once it reaches the end, a message will appear indicating that the task has been completed successfully.

We have used Microsoft Access's built-in Function SysCmd() to control and display the Progress Meter on the Status Bar. When the function is first invoked, it is passed the total number of records in the table as the third parameter, which initializes the Progress Meter. Subsequent calls to the function supply the current record number, allowing Access to calculate the percentage of records processed and update the meter accordingly.

The blue indicator on the Progress Meter advances in proportion to the percentage of completion. For large datasets, this means the bar may not visibly move with every single record processed, but instead updates after processing a batch, based on the overall total.

A delay loop has been added to the code for demonstration purposes, allowing users to observe the Progress Meter in action. You may remove these lines when incorporating the routine into your actual project for better performance.

Need a Better Method.

We cannot use this method when sequencing process steps through Macros involving Action Queries. This is because, during execution, Microsoft Access automatically updates the Status Bar to display the progress of each individual query, which overrides and interferes with our custom Progress Meter based on SysCmd().

To overcome this limitation, we must devise an alternative method to track the overall progress of the entire process.

In the next section, we will explore how to use a custom Progress Meter on a Form to visually track the progress of data processing steps executed through a Macro.