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

    Lines.

 

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

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

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.

Download Demo Database

MS-Access & Mailmerge-3

MS-Access & Mailmerge-2

MS-Access & Mail-Merge

MS-Access Object Documenter

Useful Report Functions