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.MoveLast
recs = 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.Edit
rst![ExtendedPrice] = ExtendedPrice
rst.Update

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


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.

---

MS-Access & Mailmerge-3
MS-Access & Mailmerge-2
MS-Access & Mail-Merge
MS-Access Object Documenter
Useful Report Functions

Labels: progressmeter

Continued from previous article: Progress Meter


We will try the Progress Bar Control on the sample Form that we have already designed earlier, for using with Macros.


NB: This method for Macros found working only on Microsoft Access 2000. When tried in MS-Access 2003 it totally ignores updating the Progress Bar Control, even with the database Formats MS-Access 2000, 2002 and 2003. If any readers found a solution to this problem please share it with me too.


However, you can use this method with our earlier Program replacing the sysCmd() lines for transaction level processing, to view the Progress Bar on your own Form rather than on the Status Bar.

1. Open the ProgressMeter Form in Design View.


2. Select ActiveX Control Option from 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. You have to do some resizing to make it look like the sample below. You can make it 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. Change the following Property Values are shown below:





• Name = PB2


• Visible = False





7. Create a label at 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 Tools Menu. Uncheck the Status Bar under the Show Option group on 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 we will modify it for our demo. A sample Macro Image is given below for reference.





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. Values of the parameters represents 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. The Function must be called after every Query running step to update the Progress Bar. But, subsequent calls to the function ProgMeter2(0) needs only with 0 (zero) as Parameter indicating that it is the Progress Meter's updating step.



13. Insert one row each after every Query on the Macro, Copy and paste the RunCode macro line on the inserted rows calling the Function ProgMeter2(0)
.


14. At the end of the Macro steps add one more line to call the Function ProgMeter2(2), with the parameter value 2 indicating that the work is over and to turn off the ProgressBar.



15. If you have added more steps later in the Macro and forgot to modify the initializing value (second parameter 5) the program will ignore further updating calls and wait for the terminating parameter 2 for closing the Progress Bar.



16. Save the Spl_Report Macro after the above changes.



17. Copy and Paste the following VB Code into a Global Module in your Project 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 of SysCmd() we have used for updating the Status Bar in our earlier example. The only difference is our program updates the Progress Bar on a Form. The built-in Function SysCmd() have other implementations too, with different set of Parameters.



18. Open the ProgressMeter Form in Normal View. Click on the [Process Orders] Command Button to Run the process steps in Spl_Report Macro.

The Progress Bar will now show up on the Form with the message Working… at the left side Label.


The image of a sample run is given below.






You can implement this method on any Form, like on your Control Screen or on a separate report running parameter Form. When you call the Function ProgMeter2() use the Name that you have given to the Progress Bar Control as third parameter to the Function in place of PB2, that we have used for our example: ProgMeter2(1,5,”PB2”). Subsequent call to the Functions needs only one parameter value 0 for updating the control at each step and 2 to close the Progress Bar at the end.


Further improvement on the design of the Form and the Code is required to prevent Users from closing the Form by accident or straying away from the Form for doing something else making the Form inactive etc. In such situations the program may run into Error.


As I have mentioned at the beginning you can use this function for your VB Routines that updates Table at record level.

---

MS-Access & Mailmerge-2
MS-Access & Mail-Merge
MS-Access Object Documenter
Useful Report Functions
Reminder Pop Up

Labels: progressmeter

When we process large volume of data in MS-Access for Reports it may take few minutes to several minutes to complete them, depending on the size of the transactions involved. We have to run VB Routines or several Action Queries, which takes data from other Queries or Tables, and sequence the process steps through Macros to complete them. If you have Queries of Select Type or Action Query that pulls data from Crosstab Queries and if large volume of transactions involved, it takes longer than the Normal Queries to prepare the output.


In all these situations it will be difficult to the user to know how long the whole process will take to complete the task. After running the process few times the user may get a rough idea as how long it will take. Normally at the beginning of a lengthy process the Mouse Pointer can be turned on into an Hourglass shape (Docmd.Hourglass True) indicating that the machine is engaged and at end of the program we can turn it off. But, this method will not give an exact indication when the process will be over and every time the user looks at it and if it takes a little more time than usual he/she gets worried. If it takes more than the usual time, depending on other factors, like increase in volume of transactions or due to busy network traffic and so on, it is difficult to determine whether the process is really running or we are facing a machine hang up.


When we run several Action Queries in chain from within a Macro, MS-Access displays a Progress Meter for a brief moment for each Query on the Status Bar. If the Status Bar is not visible you can turn it on. Select Options from Tools menu, select View Tab on the displayed Dialog Control. Put check mark on the Status Bar option under Show Option Group. But it will not give an overall time indicator for the full process.


We will make use of the Progress Meter for our data processing tasks more effectively and will look into more than one method. The users of our Application can relax during the whole process and take little time off to flip through the Weekly Magazine with an occasional glance on the Progress Meter.

1. Displaying the Progress Meter on the Status Bar


2. Displaying the Progress Meter on a Form


3. Usage of a transactions count down method

Usage of Progress Meter on the Status Bar.

We will use the Order Details Table from the Northwind.mdb sample database for our example and write a VB Routine to calculate the Extended Price on each entry in this Table. If you have not already imported this Table for our earlier examples you may do it now. If you don’t know the exact location of this File on your machine please visit the page Saving Data on Forms not in Table for references.

1. Import the Order Details Table from Northwind.mdb sample Database.



2. Open the Order Details Table in Design View and add a new field with the name ExtendedPrice (Field Type: Number, Field Size: Double) at the end of the existing fields. We will write a program to calculate ExtendedPrice of each record and update this field.



3. Design a simple Form similar to the one shown below with a Command Button on it. We will modify this Form for our next example also.





4. Click on the Command Button and display the Property Sheet (View -> Properties).



5. On the On Click Property type =ProcessOrders() to run the Program, which we are going to write now. Do not forget the equal sign in =ProcessOrders(), otherwise MS-Access will take it as a Macro name.



6. Close the Form and save it with the name ProgressMeter.



7. Copy and Paste the following Code into a Global VB Module of your Project and save it.



Public Function ProcessOrders()
Dim db As Database, rst As Recordset
Dim TotalRecords As Long, xtimer As Date
Dim ExtendedValue As Double, Quantity As Integer
Dim Discount As Double, x As Variant, UnitRate As Double

On Error GoTo ProcessOrders_Err

DoCmd.Hourglass True

Set db = CurrentDb
Set rst = db.OpenRecordset("Order Details", dbOpenDynaset)
rst.MoveLast
TotalRecords = rst.RecordCount

rst.MoveFirst
Do While Not rst.EOF
  With rst
    Quantity = ![Quantity]
    UnitRate = ![UnitPrice]
    Discount = ![Discount]
    ExtendedValue = Quantity * (UnitRate * (1 - Discount))
    
    .Edit
    ![ExtendedPrice] = ExtendedValue
    .Update

    If .AbsolutePosition + 1 = 1 Then
       x = SysCmd(acSysCmdInitMeter, "process:", TotalRecords)
    Else
      'a delay loop to slow down the program 
      'to view the Progress Meter in action.
      'you may remove it.
      '=================================================
      xtimer = Timer
      Do While Timer < xtimer + 0.02
        'do nothing
      Loop
      '=================================================
    
      x = SysCmd(acSysCmdUpdateMeter, .AbsolutePosition + 1)
    End If

   .MoveNext
  End with
Loop
rst.Close
x = SysCmd(acSysCmdRemoveMeter)
DoCmd.Hourglass False

MsgBox "Process completed.", , "ProcessOrders()"

Set rst = Nothing
Set db = Nothing

ProcessOrders_Exit:
Exit Function

ProcessOrders_Err:
MsgBox Err.Description, , "ProcessOrders()"
Resume ProcessOrders_Exit

End Function




8. Open the ProgressMeter Form in normal view and click on the Command Button. You will see the Progress Meter slowly advancing and when it reaches the end of the bar a message will appear announcing that the work is complete.

We have used MS-Access’s built-in Function SysCmd() to update the Progress Meter on the Status Bar. When the Function is first called, the Maximum number of Records in the File is passed as third parameter to the function to initialize the Progress Meter. Subsequent calls are made with the current record number to update the Meter with current status. MS-Access calculates a percentage on the current number of records processed based on the Total Records that we have passed to the InitMeter step and updates the Progress Meter. The blue colored indicator on the Progress Meter may advance one step, only after processing several records depending on the total number of records in the file.


A delay loop is built into the Code to slow down the program and view the Progress Meter in action. You may remove these lines when using in your Project.


We cannot use this method when we sequence our process steps in Macros involving Queries. Because, when each Action Query is run MS-Access uses the Status Bar to display the progress of each and every query separately overwriting our overall process time meter. We have to device a method of our own to do this.

Next we will see the usage of a Progress Meter on a Form, for the Data Processing steps sequenced through Macro.

---

MS-Access & Mail-Merge
MS-Access Object Documenter
Useful Report Functions
Reminder Pop Up
MS-Access & Graph Charts-2

Labels: progressmeter