How to Use a Progress Meter in Access

[ADezii]

Many Access Users fail to realize that it has a built-in Progress Meter that can display the relative completion percentage of various processes. It is fairly limited, but nonetheless, does provide visual feedback indicating the percent completion of a certain task. The Meter itself, when activated, rests on the left hand corner of the Status Bar and is controlled via the SysCmd() Method. It is straightforward , simple to use, and involves only 3 steps to implement it, These steps are listed below. Following these steps, a code segment involving the updating of a Field within a Recordset, will demonstrate its use.

1. Initiate the Meter using the acSysCmdInitMet er Action Argument, descriptive text, and a Value Argument which is the Maximum Value of the Meter.
2. Periodically update the Meter with the acSysCmdUpdateM eter Action Argument and a Value Argument indicating the relative progress of the task at hand.
3. Remove the Meter using the acSysCmdClearSt atus.

'The following code will loop through all Records in tblEmployee
and Update the value in a newly created Field called [Full Name] 
to [FirstName] & " " & [LastName]. The relative completion percentage 
of this operation will be displayed in our Progress Meter.
 
Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Dim varReturn, intCounter As Long, dblNum, intNoOfRecs As Long
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblEmployee", dbOpenDynaset)
 
MyRS.MoveLast: MyRS.MoveFirst
intNoOfRecs = MyRS.RecordCount
 
 'Initialize the Progress Meter, set Maximum Value = intNoOfRecs
varReturn = SysCmd(acSysCmdInitMeter, "Updating...", intNoOfRecs)
 
Do While Not MyRS.EOF
  With MyRS
    .Edit
      ![Full Name] = ![FirstName] & " " & ![LastName]
        intCounter = intCounter + 1
        'Update the Progress Meter to (intCounter/intNoOfRecs)%
        varReturn = SysCmd(acSysCmdUpdateMeter, intCounter)    .Update
        .MoveNext
  End With
Loop
 
 'Remove the Progress Meter
varReturn = SysCmd(acSysCmdClearStatus)
 
MyRS.Close

NOTE: If updating a large Recordset, you may wish to periodically relinquish control to the Windows Environment using DoEvents. If anyone is interested in how to do this, please let me know,

[PCurtin]

I am using syscmd for the meter....if I do not stay on the screen and go to another application, when I try to come back to the screen it does not update the meter anymore....I have the same thing with msgbox.

[wassimdaccache]

Very useful code thank you

I'm using a full screen form am I able to change the place of the update meter ? for example having it as msgbox in my form ..



I'm interesting in DoEvents because I'm using a very large recordset (more than 1000 000 records/ table ) I'm always have delay if I want to select a record in the recordset or navigate using loop ...


Can u explain for me the best way to search a value in a very large recordset ?
Does memories (ram) || cash memory || CPU frequencies || system heat are factors to change the speed of getting a value in a recordset ?

[ADezii]

Very useful code thank you

I'm using a full screen form am I able to change the place of the update meter ? for example having it as msgbox in my form ..



I'm interesting in DoEvents because I'm using a very large recordset (more than 1000 000 records/ table ) I'm always have delay if I want to select a record in the recordset or navigate using loop ...


Can u explain for me the best way to search a value in a very large recordset ?
Does memories (ram) || cash memory || CPU frequencies || system heat are factors to change the speed of getting a value in a recordset ?

The most efficient and fastest way to search for a value in a very large Recordset is by using the Seek() Method. It must, however, be based on a Table Type Recordset. Everything you mentioned is more than likely a factor that effects the speed with which searches can be done, but one of the most critical factors, in my opinion, is whether or not the Field is Indexed.

[rcollins]

Here is the error I am getting now: "You can't reference a property or method for a control unless the control has the focus."
Here is my exact code for picking from two choises. I only get this error on the first choice, but don't get anything in the text box with the second one.

Private Sub StandardizedAllocation_AfterUpdate()
If StandardizedAllocation.Text = "HOUSE MEETINGS:" Then StandardizedAllocationSumm.Text = "Consult with direct care staff ongoing medical concerns and plan of care."
If StandardizedAllocation.Text = "MD Appt.Prep:" Then StandardizedAllocationSumm.Text = "Case file reviewed for pending medical appointment.  Plan of care reviewed and updated."
 
End Sub
 

[ADezii]

Here is the error I am getting now: "You can't reference a property or method for a control unless the control has the focus."
Here is my exact code for picking from two choises. I only get this error on the first choice, but don't get anything in the text box with the second one.

Private Sub StandardizedAllocation_AfterUpdate()
If StandardizedAllocation.Text = "HOUSE MEETINGS:" Then StandardizedAllocationSumm.Text = "Consult with direct care staff ongoing medical concerns and plan of care."
If StandardizedAllocation.Text = "MD Appt.Prep:" Then StandardizedAllocationSumm.Text = "Case file reviewed for pending medical appointment.  Plan of care reviewed and updated."
 
End Sub
 

You use the Text property to set or return the text contained in a Text Box or in the text box portion of a Combo Box. It is a Read/write String. To set or return a control's Text property, the Control must have the focus, or an error occurs. If you are just concerned with setting/retrieving the value in the Control itself, then adjust your code to:

Private Sub StandardizedAllocation_AfterUpdate()
If Me![StandardizedAllocation] = "HOUSE MEETINGS:" Then   
  Me![StandardizedAllocationSumm] = "Consult with direct care staff ongoing medical concerns and plan of care."
ElseIf Me![StandardizedAllocation] = "MD Appt.Prep:" Then   
  Me![StandardizedAllocationSumm] = "Case file reviewed for pending medical appointment.  Plan of care reviewed and updated."
Else
  'not sure what you want to do here, if anything
End If
End Sub

[rcollins]

Awesome, sorry I posted this in the wrong place, but working on three databases at the same time. Worked perfect.

[ADezii]

@djbit:

Hello ADezzi, I saw your article on using the access progress bar. it worked nicely thanks!

Anyways, you mentioned in the notes that you could implement this along with the DoEvents function. Can you show me how you would do that. I have a process that takes about 5-10 minutes and I think this DoEvents thing is needed.

This Logic would be used when processing very large Recordsets, which yours apparently is. In the partial Code listing below, it will periodically check the Environment ever 50,000th Iteration of the Loop. You can, of course, vary this number. Later on, I'll send you an Attachment illustrating amuch simpler, and more graphic technique, of accomplishing the same thing.

'Code intentionally removed.............................................
Do While Not MyRS.EOF
  With MyRS
    .Edit
      ![Full Name] = ![FirstName] & " " & ![LastName]
        intCounter = intCounter + 1
          'Check the Environment every 50,0000th Iteration
          If intCounter Mod 50000 Then DoEvents
            'Update the Progress Meter to (intCounter/intNoOfRecs)%
            varReturn = SysCmd(acSysCmdUpdateMeter, intCounter)
    .Update
        .MoveNext
  End With
Loop
'Code intentionally removed.............................................

P.S. - Got back to adding that Attachment that I was referring to.

[djbit]

ADezii, thank you very much! I will look into making this work in my application.

[Narender Sagar]

Hi ADezii,
I want to update about 4 tables (quite much data in each table) with click of a command button. And I would like to show users progress bar with respect to data uploaded in each table.
How can make use of above code for that.
I am using following codes for updating data in the tables. (I have written a very bad code- as I am still learning stage)

Private Sub Command1_Click()
    DoCmd.Hourglass True
    DoCmd.SetWarnings False
    DoCmd.OpenQuery ("updateZDBCT0111")
    DoCmd.OpenQuery ("updateZD008")
    DoCmd.OpenQuery ("updateZD010")
    DoCmd.OpenQuery ("updateZD012")
    MsgBox "Data Updated"
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    Me.Requery
End Sub

Please help me..
Thanks.