MsgBox with Options Menu

Introduction.

This article is a follow-up to my earlier post on using the MsgBox With the Office Assistant.
In this installment, we’ll explore how to leverage the Office Assistant to display a balloon-style menu with multiple options for the user to choose from.

When the user clicks on one of the presented options, our VBA code detects the selected value and executes the corresponding programmed action. This technique can be a fun and interactive way to enhance user engagement in your MS Access applications.

Below is a sample screenshot of the Balloon Menu displayed through the Office Assistant:

Click to Enlarge

Preparing for a Demo.

Design a simple form with a Text Box and a Command Button, as shown in the illustration above, to run the demo program.

1. Rename the Text Box

   • Click on the Text Box and open the Property Sheet (View → Properties).

   • Change the Name property value to OptTxt.

   • This control will be used only for displaying the selected option text during the demo.

2. Rename the Command Button

   • Click on the Command Button and open its Property Sheet.

   • Change the Name property value to cmdOpt.

   • Change the Caption property value to Get Option.

3. Add the VBA Code

   • While the sample form is still in Design View, open the form’s VBA module (View → Code).

   • Copy and paste the code provided below into the form’s module.

   • Save the form with the name OptionCheck, or choose any other name you prefer.

Private Sub cmdOpt_Click()
Dim OptionArray(1 To 5) As String
Dim i As Integer, msg As String, title As String

OptionArray(1) = "Display Report Source Data"
OptionArray(2) = "Print Preview"
OptionArray(3) = "Print"
OptionArray(4) = "CANCEL"

msg = "Please Select an Option"title = "
Report Options"

i = MsgGetOpt(msg, title, OptionArray())

If i > 0 Then
    Me![optTxt] = i & " : " & OptionArray(i)
End If

Select Case i
    Case 1
      'DoCmd.OpenForm "DataForm", acNormal
       msg = "Display Report Source Data"
    Case 2
       'DoCmd.OpenReport "myReport", acViewPreview
        msg = "Print Preview"
    Case 3
       'DoCmd.OpenReport "myReport", acViewNormal
        msg = "Print"
    Case 4
        msg = "CANCEL"
End Select

    MsgOK "selected: " & msg

End Sub

The VBA Code

Let’s break down what happens in the above routine.

First, we define a String array variable  OptionArray with a maximum of five elements, and then load it with values in four of those elements. The main program MsgGetOpt() (code provided below) supports a maximum of five options. Even if you assign more than five elements to OptionArray the statement:

vba
k = IIf(k > 5, 5, k)

will limit the number of options to five.

Next, we initialize the variables msg and Title With the option text and the dialog box title. We then call the MsgGetOpt() program, passing these variables, along with the OptionArray—by reference. This ensures that the option values are sent directly into the routine for display.

If you have already completed the MsgBox with Office Assistant and Command Button Animation examples from earlier articles, you may proceed directly to step 3. However, make sure that you copy and paste these VBA routines into the same project where the main programs were placed earlier. If required, you can copy the main program code again from the following pages.

Setup instructions:

1. Copy the main programs from the page MsgBox with Office Assistant into a Global Module in your project, then save the module.

2. Link the Microsoft Office Library to your project. The procedure for doing this is described on the Command Button Animation page, which also lists other essential library references.

3. Copy and paste the following MsgGetOpt() code into a VBA Global Module, and save it.

Public Function MsgGetOpt(ByVal strText As String, ByVal strTitle As String, ByRef MaxArray5obj) As Integer
Dim intVal As Integer, X As Integer, Bal As Balloon, k As Integer
On Error GoTo MsgGetOpt_Err

k = UBound(MaxArray5obj)
k = IIf(k > 5, 5, k)

With Assistant
  If .On = False Then
    .On = True    
   '.FileName = "OFFCAT.acs"
    .Animation = msoAnimationBeginSpeaking
    .AssistWithHelp = True
    .GuessHelp = True
    .FeatureTips = False
    .Visible = True
  End If
End With

Set Bal = Assistant.NewBalloon
With Bal
   .Animation = msoAnimationWritingNotingSomething
   .Icon = msoIconAlert
   .Heading = strTitle
   .Text = strText
   .BalloonType = msoBalloonTypeButtons
   For X = 1 To k
    If Len(MaxArray5obj(X)) > 0 Then
     .labels(X).Text = MaxArray5obj(X)
    End If
   Next
   .Button = msoButtonSetNone
    intVal = .Show
End With

Assistant.Visible = False
MsgGetOpt = intVal

Set Bal = Nothing

MsgGetOpt_Exit:
Exit Function

MsgGetOpt_Err:
Err.Clear
Resume MsgGetOpt_Exit
End Function

Understanding the MsgGetOpt() Routine.

The MsgGetOpt() Routine is the core program that displays a balloon-style menu using the Office Assistant and allows the user to select from up to five options.

Here’s how it works:

1. Limit on Options – Although you can pass more than five values in the OptionArray The routine automatically restricts the total to five using the line:

   vba
   k = IIf(k > 5, 5, k)
   

   This ensures the display remains neat and fits within the Office Assistant’s balloon window.

2. Balloon Setup – The routine uses the msg  Title values you pass to it to create the balloon message and set the window’s title bar text.

3. Loading the Options – Each element of the OptionArray (up to five) is assigned to the balloon’s menu items, making them clickable choices for the user.

4. Displaying the Menu – The Office Assistant appears with the configured balloon, presenting the options to the user in a visually friendly way.

5. Capturing the User’s Choice – When the user clicks one of the options, the routine detects the selection and returns its value, so your program can take the appropriate action.

By organizing this functionality into a reusable MsgGetOpt() routine, you can call it from any form or module simply by passing the required message text and option values.

To see it in action, open the Demo Form named OptionCheck and click its Command Button. The Office Assistant-based message box will appear, displaying the available options, just like the sample image shown at the top of this page.

When you click one of the options:

1. A standard MsgBox will appear, showing the text of the option you selected.

2. The same selected option text, along with its corresponding sequence number, will also be displayed in the form’s TextBox.

3. Internally, the user’s selection is returned into the variable i inside the cmdOpt_Click() subroutine.

A Select Case ... End Select structure then evaluates the value  i to determine which option was chosen and executes the appropriate block of code for that selection.

This approach makes the process interactive, user-friendly, and easy to adapt for any set of options you want to present.

Next, we will look into the CheckBoxes with the Office Assistant.

Download the Demo Database.

Download OfficeAssistant.zip

• MS-Access and Email
• Dynamic Report
• MS-Access & Mailmerge-3
• MS-Access & Mailmerge-2
• MS-Access & Mail-Merge

Read More

Who changed the Data

Introduction.

"Who changed the data?" — This question often arises when incorrect or inconsistent information is discovered in a database, especially when it leads to serious consequences affecting multiple areas of operation.

At first glance, the issue may not be obvious, and you might wonder what this is all about. To bring the story into focus, let’s consider a scenario that illustrates how such a situation can unfold.

Let’s assume there is a database that maintains records of the company’s credit customers, including high-profile clients such as Ministry officials or even Ministers themselves. These credit accounts are categorized based on status, credibility, or other internal criteria and are assigned specific Category Codes for easier classification.

Periodically, statements showing outstanding payments are generated and sent to these credit parties as part of a routine follow-up process. The purpose is to remind them to make timely payments or to verify the accuracy of the statement.

Since high-profile accounts are assigned special Category Codes, their statements are printed separately for internal records only, and never forwarded to the respective parties, as per strict directives from senior management.

Multiple individuals in the Accounts Department are authorized to update the database. One day, however, an employee accidentally altered the Category Code of a VIP account, inadvertently reclassifying it under the common category. As a result, the monthly statement was printed and dispatched to the VVIP party along with the regular batch.

For accounts under the common category, this is standard practice—statements are routinely sent to encourage timely payments. But when such a statement is delivered to a VVIP client, it can create serious complications. This misstep not only breached protocol but also exposed the company to potential embarrassment and reputational risk at the highest levels.

The investigating committee began pointing fingers at everyone who had access to the data, but no one stepped forward or admitted to the error. Tension mounted as the blame game escalated—until the EDP (Electronic Data Processing) Department was brought in. Now, all eyes turned to the poor application developer, who suddenly found himself in the hot seat. After all, he had full access to the system and the ability to modify any data. It was easy to assume he could be the one responsible.

Fortunately, he was prepared for such a situation. Anticipating that one day such a scenario might arise, he had already built mechanisms into the system to trace changes. Within minutes, he uncovered the truth—the real culprit had left behind a digital fingerprint in the record, unmistakable and undeniable. Whether the change had been made deliberately or by accident was a separate matter, but the evidence was clear.

If you're from the Accounts Department, you may have many questions and might even argue that such a mistake could never happen in your team. Fair enough—but remember, this is just a hypothetical story. Still, the possibility, however unlikely, cannot be ruled out entirely.

User/Date/Time Stamps.

Data Entry and Editing are two critical operations in maintaining an up-to-date and reliable database. To ensure data accuracy, field-level validation checks are implemented to prevent the entry of incorrect or inconsistent information. In modern systems, these functions are increasingly supported by advanced tools such as scanners, handheld terminals (HHTs), and other input devices. These technologies not only streamline data capture but also enhance traceability by automatically recording details such as User IDs, timestamps, and other metadata along with the entered data.

When multiple users are involved in accessing and updating data in Microsoft Access databases, it is essential to deploy the application on a secure network environment. This includes implementing Microsoft Access Workgroup Security, which allows the creation of user groups with specific roles and access levels. Each user should log in with a unique User ID and password, ensuring that their activity is restricted to the permissions assigned to their role.

In the context of maintaining data integrity, we earlier discussed what I like to call "data fingerprinting"—a method for tracking changes made to records. When a new record is added or an existing one is edited, the User ID, along with the date and time of the action, can be recorded directly within the record itself. This metadata serves multiple purposes: it helps in sorting records chronologically, tracking changes over time, and, most importantly, identifying who made a specific change and when.

Inadvertent edits are not uncommon, and the user responsible might not immediately recall the affected record. However, with time and date information captured, users can review recently modified records to locate and correct any errors, provided they have the necessary permissions to do so.

Add extra Time Stamp Fields to the Table.

While designing the Table, add the following three fields at the end of the field list to record the Data Entry Date-Time, User ID, and the record Edited Date-Time:

Field Name	Data Type	Size
DEDate	Date/Time
EditedBy	Text	20
EditDate	Date/Time

Setting Up Auto-Tracking for Data Entry and Edits

To automatically track when a record is added or modified, follow these steps:

1. Open the Table in Design View.

2. Click on the DEDate field (or the equivalent field meant to store the data entry timestamp).

3. In the Default Value property (bottom pane), enter the following expression:

   mathematica
   =Now()
   

   This ensures that the current date and time are recorded automatically when a new record is added.

The EditDate and EditedBy Fields will be used to track the timestamp and user information every time a record is edited. These values will be updated through a VBA event procedure.

---

Form Design Tips for Tracking Fields

When designing your Data Entry/Edit Forms, place the fields DEDate, EditDate, and EditedBy on the Form as well. Then:

• Set the Locked property of each of these fields to Yes, so that users cannot change the values manually.

• Set the Tab Stop property to No, preventing the cursor from navigating into these fields during normal data entry or editing.

Optional Display Settings:

• If you'd like users to see but not interact with the field (e.g., to sort or review their own edits), set:

  • Enabled = No

  • Locked = No

• If you prefer to hide these fields from users altogether:

  • Set the Visible property to No

These settings provide flexibility depending on whether users need visibility into their own change history.

---

Automating the Audit Trail with VBA

To record and edit information automatically, we’ll use the BeforeUpdate event of the form. This code captures the edit timestamp and the user ID each time a record is modified:

Steps:

1. While the Form is still in Design View, go to:

   • View > Code (to open the Form’s VBA module).

2. Copy and paste the following VBA code into the module:

   vba
   Private Sub Form_BeforeUpdate(Cancel As Integer)
       If Me.NewRecord = False Then
           Me![EditDate] = Now()
           Me![EditedBy] = CurrentUser()
       End If
   End Sub
   

3. Save the Form.

---

With this setup, any new or modified record will automatically capture the relevant audit information. This method is especially useful in multi-user environments where tracking accountability is essential.

To Avoid Further Pitfalls.

Limitations of the Simple Audit Trail

The BeforeUpdate() Event helps track who edited what and when. It's important to understand that this approach is not foolproof.

For instance, suppose a user unintentionally makes a critical error in a record. If someone else later edits a different field in that same record—perhaps as part of routine work—the original edit details will be overwritten by the second user's information. As a result, the actual source of the mistake is lost, and blame might fall on an innocent person.

This could lead to confusion—or worse, disciplinary action against the wrong individual.

---

Enhancing the Audit Trail: Suggested Approaches

To improve reliability and accountability, consider these two advanced solutions:

1. Multi-Level Edit Tracking

Add additional fields to the table to track multiple editing events—for example:

• EditDate1, EditedBy1

• EditDate2, EditedBy2

• … up to EditDate5, EditedBy5

Each new edit shifts the previous entries down by one level. When the maximum number of tracked edits is reached (say, 5), the oldest one is overwritten.

This approach allows administrators to trace back a limited history of changes and is helpful for short-term auditing.

2. Full Edit History Table

A more comprehensive and professional solution is to maintain a separate change history table. Every time a record is edited, the original version of the record is copied to this history table, along with:

• Primary Key ID

• Date and Time of Edit

• User ID

• Changed Field Names

• Original and New Values (optional but ideal)

This method enables a complete reconstruction of a record’s change history, offering transparency and accountability, particularly in sensitive environments like finance, legal, or healthcare systems.

---

Implementation Notes

• The history table can be populated using the Form_BeforeUpdate or Form_AfterUpdate events, depending on whether you want to capture data before or after the change.

• Use VBA code to compare the current values with the existing values in the record to detect changes.

• The history table can be indexed on PrimaryKey, EditDate, or EditedBy for quick lookup.

• Consider implementing user permissions that prevent unauthorized access to this history log.

Earlier Post Link References:

• Reminder PopUps
• MS-Access Graph Charts
• MS-Access Graph Charts-2
• Report Page Border
• Highlighting Reports

• Dynamic Report
• MS-Access & Mailmerge-3
• MS-Access & Mailmerge-2
• MS-Access & Mail-Merge
• MS-Access Object Documenter

Read More

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

Read More

Progress Bar on Form

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

1. Open the 'ProgressMeter' Form in Design View.

2. Select the ActiveX Control Option from the Insert Menu.

3. Select Microsoft Progress Bar Control, Version 6.0 from the displayed list.

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

   

5. Select the Progress Bar Control (if you have deselected it) and display the Property Sheet (View --> Properties).

6. Need to change the following Property Values as shown below:

   • Name = PB2
   • Visible = False

7. Create a label on the left side of the ProgressBar. Display its Property Sheet and change the following values:

   • Name = lblStatus
   • Visible = False

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

9. Save the Form after the changes.

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

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

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

Read More