Progress Counter

Introduction.

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

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

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

Download ProgressMeter2007.zip

Download ProgressMeter.zip

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