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.

[B]'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.[/B]

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

[B]'Initialize the Progress Meter, set Maximum Value = intNoOfRecs
varReturn = SysCmd(acSysCmdInitMeter, "Updating...", intNoOfRecs)[/B]

Do While Not MyRS.EOF
  With MyRS
    .Edit
      ![Full Name] = ![FirstName] & " " & ![LastName]
        intCounter = intCounter + 1
  [B]      'Update the Progress Meter to (intCounter/intNoOfRecs)%
        varReturn = SysCmd(acSysCmdUpdateMeter, intCounter)[/B]    .Update
        .MoveNext
  End With
Loop

[B]'Remove the Progress Meter
varReturn = SysCmd(acSysCmdClearStatus)[/B]

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:
[CODE=vb]Private Sub StandardizedAll ocation_AfterUp date()
If Me![StandardizedAll ocation] = "HOUSE MEETINGS:" Then
Me![StandardizedAll ocationSumm] = "Consult with direct care staff ongoing medical concerns and plan of care."
ElseIf Me![StandardizedAll ocation] = "MD Appt.Prep:" Then
Me![StandardizedAll ocationSumm] = "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[/CODE]

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

[Narender Sagar]

Awaiting response please..

[ADezii]

Without getting very complex, the easiest approach would be to Update the Progress Meter by 25% after the execution of each individual Update Query.

[Narender Sagar]

Thanks ADezii,
But how to proceed for it..
Please help me to understand this.
thanks again.

[ADezii]

You can try the following Code Segment which will simulate the 4 Update Processes, and adjust the Progress Meter at the end of each Process. It also has Error Handling which will Reset the Hourglass, and remove the Meter in the event of an Error.

Private Sub cmdTest_Click()
On Error GoTo Err_cmdTest_Click
Dim varReturn As Variant
Dim intCtr As Long

'Initialize the Progress Meter, set Maximum Value = 100
varReturn = SysCmd(acSysCmdInitMeter, "Updating...", 100)

DoCmd.Hourglass True

'Simulate the 1st Update Process
For intCtr = 1 To 10000
  Debug.Print intCtr
Next

'Update the Progress Meter to 25%
varReturn = SysCmd(acSysCmdUpdateMeter, 25)

'Simulate the 2nd Update Process
For intCtr = 1 To 10000
  Debug.Print intCtr
Next

'Err.Raise 13

'Update the Progress Meter to 50%
varReturn = SysCmd(acSysCmdUpdateMeter, 50)

'Simulate the 3rd Update Process
For intCtr = 1 To 10000
  Debug.Print intCtr
Next

'Update the Progress Meter to 75%
varReturn = SysCmd(acSysCmdUpdateMeter, 75)

'Simulate the 4th Update Process
For intCtr = 1 To 10000
  Debug.Print intCtr
Next

'Update the Progress Meter to 100%
varReturn = SysCmd(acSysCmdUpdateMeter, 100)

DoCmd.Hourglass False

'Remove the Progress Meter
varReturn = SysCmd(acSysCmdClearStatus)


Exit_cmdTest_Click:
  Exit Sub

Err_cmdTest_Click:
  DoCmd.Hourglass False
  varReturn = SysCmd(acSysCmdClearStatus)   'Clear Meter in event of an Error
    MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
    Resume Exit_cmdTest_Click
End Sub

P.S. - Any other questions, feel free to ask.

[Narender Sagar]

Thank you so much dear..! I'll try this, and let you results.
(O God..! when will I learn all this..)