Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Progress Meter

Introduction

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.

The Quick Solution

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.

A Better Approach

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.

    Usage of SysCmd().

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

Need a Better Method

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.

Download



Download Access2003 Version


Download Access2007 Version


Share:

No comments:

Post a Comment

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Class Module Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Menus and Toolbars Controls Event MsaccessLinks Process Controls Art Work Collection Object WithEvents msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Command Button Data Dictionary Object Emails and Alerts Form Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item Object Reference msaccessprocess security advanced Access Security Add Custom Functions Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database ms-access msaccess wizards reference text tutorial vba code 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 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 msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captu...

Labels

Blog Archive

Recent Posts