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 record level, rather than based on the percentage of records processed, in the case of progress bar's 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 few minutes and involves several files, you can easily incorporate the use of this new method with 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 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.

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

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

  3. Click on the Top left label in 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 back-ground and 3 Labels with white background 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". If this size is not suitable for your present design then retain yours.
  9. Click on the dark rectangle on the top left corner of the Form, to de-select other controls and to 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:
    'Global declaraction
    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

    The first two lines of the above code should go at the top of the Module, below the

    Option Compare Database

    Option Explicit


  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 in the ProcCounter() Program.

    We will use the same Demo Form ProgressMeter used for 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 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 calls 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 a Global 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 have imported from 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 visit the Page Saving Data on Form not in Table for location references.

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

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



  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



Subscribe in a reader
Your email address:

Delivered by FeedBurner


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.


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

Featured Post

WithEvents and Defining your own Events

Hope that you have gone through last week’s WithEvents, Event, RaiseEvent introduction, tried out the sample Forms and understood as how bo...


Blog Archive

Recent Posts