Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Progress Counter


This is the continuation of a series of examples and Demos tried earlier and uses Table and Form designed for earlier examples. If you have started on this Page please go through the earlier Topics and then continue. Click the Links Below or select them from the TRICKS Menu Tab above to open those Pages.

  1. Progress Meter
  2. Progress Bar on Form

The new method that we are going to try out is better in look and feel and very active in nature. The progress control is a small form and it is updated at the record level, rather than based on the percentage of records processed, in the case of the progress bar indicator to advance forward.

This method cannot be used for Macros as we did with the Progress Bar Control in the earlier Article. When the processing time takes more than a few minutes and involves several files, you can easily incorporate the use of this new method with a few lines of Code in your Program. A Demo Run image of the Control is given below:

The display is self-explanatory. At the start of the Program, the ProgressCounter Form opens and initializes the fields with the Values of Program Name, Total Records to process, and the process Start Time. Subsequent calls to the controlling program will update the Processed number of records and Process Time. When the process is complete the End Time control is updated and the Form stays displayed for 4 seconds, giving enough time to view the Total Time taken for processing, and then closes automatically.

When the Program is active the Application is locked for processing and doesn't allow the user to close the Process Control Form or work with other objects in the database window.

Designing a New Form

We will design the above Form with Label Controls as shown below.

  1. Open a New Form and add twelve Labels to the Form in the Detail Section.
  2. Arrange the Labels, in size and shapes, as shown below, and fill the background Color of 3 Labels with dark Colors and 3 Labels at the Bottom with White Color.

  3. Click on the Top left label on your design, display the Property Sheet (View Menu - - > Properties), and type Program: in the Caption Property.
  4. Change the Captions of other labels with the descriptions as shown above following the same procedure.
  5. Now, we are left with six Labels, 3 with dark backgrounds, and 3 Labels 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)
  6. Draw a Box around the Labels as shown. If the box overlaps the labels when drawn, change its Back-Style Property = Transparent.
  7. 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.
  8. Display the Property Sheet and change the Height Property Value to 1.2396".  You may modify the design suitable to your own liking.

  9. Click on the dark rectangle on the top left corner of the Form, to de-select 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
    • Whats This Button = No
    • Width = 2.3438"
    • Allow Design Changes = Design View Only
  10. Display the Code Module of the Form. Select Code from 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
  11. Save the Form after changes with the Name: ProcessCounter. Give the name exactly as shown. No space between Process and Counter, because this name is referred to in the ProcCounter() Program.

    We will use the same Demo Form ProgressMeter used in our earlier examples and the only change on that Form is the Command Button's On Click Property to call the Order Details Table updating the new Program.

  12. Open the ProgressMeter Form. Click on the command button with the caption Process Orders and display its property sheet. Change the On Click property value to =ProcessOrders3() and don't forget the = sign. Save the Form.

I have made a copy of our earlier program ProcessOrders() and changed the three lines that call the ProgMeter2() routine for updating the Progress Bar in our earlier discussion. Since the Program is a Copy of the earlier code I changed the Procedure Name to ProcessOrders3(). The code is given below for your convenience and you may copy and paste the code into the Standard Module and save it.

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)
ecs = rst.RecordCount

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

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

ProcCounter 2, rst.AbsolutePosition + 1


ProcCounter 3, rst.AbsolutePosition

Set rst = Nothing
Set db = Nothing

End Function

You need the Order Details Table, that we imported from the Northwind.mdb sample database earlier, for this example also. I hope that table is still available in your project. If not, download it again. If you forgot the location of the database visits the Page Saving Data on Form not in Table for location references.

The ProcCounter() Function

The ProcessCounter Form updating Program is given below. Copy and paste it into a Global Module in your Project and 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

Exit Function

   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")
    End With

   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")
    End With

End Function

The Demo Run

Open the ProgressMeter Form in Normal View. Click on the Process Orders Command Button. The ProcessCounter Form will appear updating the count of records already processed. Process Start Time and Process Time taken so far will be updated too. When the Processed records are equal to the total number of records in the Order Details file, the End Time will be updated. The control will stay visible for about 4 seconds displaying the details and after that, it will close itself.

A delay loop is built into the ProcessOrders3() Program to slow down the action. You may remove this when you implement the code in your own project.

Download the Demo Database.



  1. Genial brief and this enter helped me alot in my college assignement. Thanks you seeking your information.

  2. Fantastic website , I have really learned a lot . I are already searching at getting a mini stereo system and are already thinking about a Sony shelf system does anybody here have any suggestions. Thanks


Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code