473,326 Members | 2,337 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,326 developers and data experts.

How to Use a Progress Meter in Access

ADezii
8,834 Expert 8TB
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 acSysCmdInitMeter Action Argument, descriptive text, and a Value Argument which is the Maximum Value of the Meter.
  2. Periodically update the Meter with the acSysCmdUpdateMeter Action Argument and a Value Argument indicating the relative progress of the task at hand.
  3. Remove the Meter using the acSysCmdClearStatus.

Expand|Select|Wrap|Line Numbers
  1. 'The following code will loop through all Records in tblEmployee
  2. and Update the value in a newly created Field called [Full Name] 
  3. to [FirstName] & " " & [LastName]. The relative completion percentage 
  4. of this operation will be displayed in our Progress Meter.
  5.  
  6. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  7. Dim varReturn, intCounter As Long, dblNum, intNoOfRecs As Long
  8.  
  9. Set MyDB = CurrentDb()
  10. Set MyRS = MyDB.OpenRecordset("tblEmployee", dbOpenDynaset)
  11.  
  12. MyRS.MoveLast: MyRS.MoveFirst
  13. intNoOfRecs = MyRS.RecordCount
  14.  
  15. 'Initialize the Progress Meter, set Maximum Value = intNoOfRecs
  16. varReturn = SysCmd(acSysCmdInitMeter, "Updating...", intNoOfRecs)
  17.  
  18. Do While Not MyRS.EOF
  19.   With MyRS
  20.     .Edit
  21.       ![Full Name] = ![FirstName] & " " & ![LastName]
  22.         intCounter = intCounter + 1
  23.         'Update the Progress Meter to (intCounter/intNoOfRecs)%
  24.         varReturn = SysCmd(acSysCmdUpdateMeter, intCounter)    .Update
  25.         .MoveNext
  26.   End With
  27. Loop
  28.  
  29. 'Remove the Progress Meter
  30. varReturn = SysCmd(acSysCmdClearStatus)
  31.  
  32. 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,
May 12 '07 #1
23 42035
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.
Aug 22 '07 #2
wassimdaccache
222 100+
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 ?
Aug 31 '07 #3
ADezii
8,834 Expert 8TB
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.
Aug 31 '07 #4
rcollins
234 100+
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub StandardizedAllocation_AfterUpdate()
  2. If StandardizedAllocation.Text = "HOUSE MEETINGS:" Then StandardizedAllocationSumm.Text = "Consult with direct care staff ongoing medical concerns and plan of care."
  3. If StandardizedAllocation.Text = "MD Appt.Prep:" Then StandardizedAllocationSumm.Text = "Case file reviewed for pending medical appointment.  Plan of care reviewed and updated."
  4.  
  5. End Sub
  6.  
Oct 10 '07 #5
ADezii
8,834 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub StandardizedAllocation_AfterUpdate()
  2. If StandardizedAllocation.Text = "HOUSE MEETINGS:" Then StandardizedAllocationSumm.Text = "Consult with direct care staff ongoing medical concerns and plan of care."
  3. If StandardizedAllocation.Text = "MD Appt.Prep:" Then StandardizedAllocationSumm.Text = "Case file reviewed for pending medical appointment.  Plan of care reviewed and updated."
  4.  
  5. End Sub
  6.  
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub StandardizedAllocation_AfterUpdate()
  2. If Me![StandardizedAllocation] = "HOUSE MEETINGS:" Then   
  3.   Me![StandardizedAllocationSumm] = "Consult with direct care staff ongoing medical concerns and plan of care."
  4. ElseIf Me![StandardizedAllocation] = "MD Appt.Prep:" Then   
  5.   Me![StandardizedAllocationSumm] = "Case file reviewed for pending medical appointment.  Plan of care reviewed and updated."
  6. Else
  7.   'not sure what you want to do here, if anything
  8. End If
  9. End Sub
Oct 10 '07 #6
rcollins
234 100+
Awesome, sorry I posted this in the wrong place, but working on three databases at the same time. Worked perfect.
Oct 10 '07 #7
ADezii
8,834 Expert 8TB
@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.
Expand|Select|Wrap|Line Numbers
  1. 'Code intentionally removed.............................................
  2. Do While Not MyRS.EOF
  3.   With MyRS
  4.     .Edit
  5.       ![Full Name] = ![FirstName] & " " & ![LastName]
  6.         intCounter = intCounter + 1
  7.           'Check the Environment every 50,0000th Iteration
  8.           If intCounter Mod 50000 Then DoEvents
  9.             'Update the Progress Meter to (intCounter/intNoOfRecs)%
  10.             varReturn = SysCmd(acSysCmdUpdateMeter, intCounter)
  11.     .Update
  12.         .MoveNext
  13.   End With
  14. Loop
  15. 'Code intentionally removed.............................................
P.S. - Got back to adding that Attachment that I was referring to.
Attached Files
File Type: zip Progress Bar.zip (16.1 KB, 1102 views)
Feb 12 '11 #8
djbit
1
ADezii, thank you very much! I will look into making this work in my application.
Feb 14 '11 #9
Narender Sagar
189 100+
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)
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command1_Click()
  2.     DoCmd.Hourglass True
  3.     DoCmd.SetWarnings False
  4.     DoCmd.OpenQuery ("updateZDBCT0111")
  5.     DoCmd.OpenQuery ("updateZD008")
  6.     DoCmd.OpenQuery ("updateZD010")
  7.     DoCmd.OpenQuery ("updateZD012")
  8.     MsgBox "Data Updated"
  9.     DoCmd.Hourglass False
  10.     DoCmd.SetWarnings True
  11.     Me.Requery
  12. End Sub
Please help me..
Thanks.
Dec 21 '11 #10
Narender Sagar
189 100+
Awaiting response please..
Dec 23 '11 #11
ADezii
8,834 Expert 8TB
Without getting very complex, the easiest approach would be to Update the Progress Meter by 25% after the execution of each individual Update Query.
Dec 23 '11 #12
Narender Sagar
189 100+
Thanks ADezii,
But how to proceed for it..
Please help me to understand this.
thanks again.
Dec 23 '11 #13
ADezii
8,834 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdTest_Click()
  2. On Error GoTo Err_cmdTest_Click
  3. Dim varReturn As Variant
  4. Dim intCtr As Long
  5.  
  6. 'Initialize the Progress Meter, set Maximum Value = 100
  7. varReturn = SysCmd(acSysCmdInitMeter, "Updating...", 100)
  8.  
  9. DoCmd.Hourglass True
  10.  
  11. 'Simulate the 1st Update Process
  12. For intCtr = 1 To 10000
  13.   Debug.Print intCtr
  14. Next
  15.  
  16. 'Update the Progress Meter to 25%
  17. varReturn = SysCmd(acSysCmdUpdateMeter, 25)
  18.  
  19. 'Simulate the 2nd Update Process
  20. For intCtr = 1 To 10000
  21.   Debug.Print intCtr
  22. Next
  23.  
  24. 'Err.Raise 13
  25.  
  26. 'Update the Progress Meter to 50%
  27. varReturn = SysCmd(acSysCmdUpdateMeter, 50)
  28.  
  29. 'Simulate the 3rd Update Process
  30. For intCtr = 1 To 10000
  31.   Debug.Print intCtr
  32. Next
  33.  
  34. 'Update the Progress Meter to 75%
  35. varReturn = SysCmd(acSysCmdUpdateMeter, 75)
  36.  
  37. 'Simulate the 4th Update Process
  38. For intCtr = 1 To 10000
  39.   Debug.Print intCtr
  40. Next
  41.  
  42. 'Update the Progress Meter to 100%
  43. varReturn = SysCmd(acSysCmdUpdateMeter, 100)
  44.  
  45. DoCmd.Hourglass False
  46.  
  47. 'Remove the Progress Meter
  48. varReturn = SysCmd(acSysCmdClearStatus)
  49.  
  50.  
  51. Exit_cmdTest_Click:
  52.   Exit Sub
  53.  
  54. Err_cmdTest_Click:
  55.   DoCmd.Hourglass False
  56.   varReturn = SysCmd(acSysCmdClearStatus)   'Clear Meter in event of an Error
  57.     MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
  58.     Resume Exit_cmdTest_Click
  59. End Sub
  60.  
P.S. - Any other questions, feel free to ask.
Dec 24 '11 #14
Narender Sagar
189 100+
Thank you so much dear..! I'll try this, and let you results.
(O God..! when will I learn all this..)
Dec 24 '11 #15
I just saw this and saw your sample too. It works perfectly but I have a different case all together. Mine has to deal with the opening of a report. I have a form which opens based on the operation of queries. The report takes about a minute or two to open.I want the progress bar to stay on from the time command is click to open the report till the report is opened. Any help?
Dec 12 '14 #16
twinnyfo
3,653 Expert Mod 2GB
yashuaking,

If I understand your question correctly, your report takes a while to open because of the query behind it? In this case, it would be impossible to update the meter as you wait for the report, as that is considered one event. Now, if you have one hundred events and each event takes 1/10th of a second to execute, using Adezii's method, the progress meter would increment along every 1/10th of a second. However, if one of those events took two minutes to execute, the meter would appear to "hang" while it executed that one event.

I hope this makes sense. But, that is a good question.
Dec 12 '14 #17
Thank you very much Twinnyfo
Actually the query the runs before the report opens is an Append Query. It only appends data to a table then the Report takes data from the table. So they are actually two separate events. I designed it this way because the Query is not able to append Pictures. I rather append a users ID to a table then the table is linked to another table by a primary key to get the picture. So you understand my situation now

I tried fixing in the code but could not see the progress meter at the status bar as expected.

Any other way out?

I made it this way that from the load of the progress bar to the appending of data should load up to 75% and when the Report is loaded it should read 100% and close but could not see the meter though the hour glass is working. Am using MS Access 2010.
Dec 12 '14 #18
twinnyfo
3,653 Expert Mod 2GB
In the declaration of your variables, add this:

Expand|Select|Wrap|Line Numbers
  1. Dim x
Then immediately before and after the code to run your Append query, add this:

Expand|Select|Wrap|Line Numbers
  1. x = DoEvents
This might force the system to show the meter.
Dec 12 '14 #19
how do I dim X? Because I know that you have to dim X as something
Dec 12 '14 #20
But thank you very much for the answer
Dec 12 '14 #21
Ok thanks very much. It is working. Let me post the code here for clarification. I really appreciate you efforts to help me solve this problem.
Dec 12 '14 #22
twinnyfo
3,653 Expert Mod 2GB
To clarify your question on Post #20, by dimming x without a type, it can take on any form until it is assigned a value. Setting x = DoEvents just allows DoEvents to let Access release control of the system to the Operating System.
Dec 12 '14 #23
This is the full code am using now
Expand|Select|Wrap|Line Numbers
  1. On Error GoTo Err_handler
  2. DoCmd.SetWarnings False
  3. Dim x
  4. Dim varReturn As Variant
  5. Dim intCtr As Long
  6. Dim StrWhere As String
  7. Dim strMsg As String
  8. Dim StrReport As String
  9. StrReport = "Form1Term1SR"
  10. If Me.Dirty Then
  11.     Me.Dirty = False
  12. End If
  13.  
  14.  'Initialize the Progress Meter, set Maximum Value = 100
  15. x = DoEvents
  16. varReturn = SysCmd(acSysCmdInitMeter, "Loading Reports Please Wait...", 100)
  17. DoCmd.Hourglass True
  18.  
  19.  
  20. DoCmd.OpenQuery "form1QTerm1appendQ"
  21.  
  22. x = DoEvents
  23. For intCtr = 1 To 10000
  24. Debug.Print intCtr
  25. Next
  26.  
  27. 'Update the Progress Meter to 80%
  28. varReturn = SysCmd(acSysCmdUpdateMeter, 80)
  29.  
  30.  
  31. DoCmd.OpenReport StrReport, acViewPreview
  32.  
  33.  'Simulate the 4th Update Process
  34. For intCtr = 1 To 10000
  35.   Debug.Print intCtr
  36. Next
  37.  
  38. 'Update the Progress Meter to 100%
  39. varReturn = SysCmd(acSysCmdUpdateMeter, 100)
  40.  
  41. DoCmd.Hourglass False
  42.  
  43. 'Remove the Progress Meter
  44. varReturn = SysCmd(acSysCmdClearStatus)
  45.  
  46. Exit_handler:
  47. Exit Sub
  48.  
  49. Err_handler:
  50.   DoCmd.Hourglass False
  51.   varReturn = SysCmd(acSysCmdClearStatus)   'Clear Meter in event of an Error
  52.     MsgBox Err.Description, vbExclamation, "Error in Meter"
  53.     Resume Exit_handler
Dec 12 '14 #24

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Julia Briggs | last post by:
Hello, I've read quite a bit of discussion on different approaches of how to create a download progress meter that can be implemented into a Web site. I understand that by the very nature of...
3
by: Brian Birtle | last post by:
**** A CHALLENGE TO THE GURUS - refute the statement "It's impossible to build a file upload progress meter using ASP.NET" **** First person to prove me wrong gets "All Time .NET Programming GOD"...
1
by: Doug | last post by:
In I.E. 6 (and probably other versions as well), there is a sort of progress meter in the panel at the bottom of the window. This meter increments a few pixels at a time when the browser is trying...
8
by: Brian Henry | last post by:
I created a smooth progress bar with this code.. but if you update the values in a row quickly of it and watch it on screen it flickers... how would i change this to reduce the flickering?...
4
by: bfulford | last post by:
I have a macro that needs to have a progress meter displayed since it is long running. I moved the Macro's instructions to a table and pulled those records into a recordset that is looped through....
0
by: Eric Pradel | last post by:
Hi, This is my first post, and I'm a real newcomer to scripting. I have a script that is doing exactly what I want it to do, which is to open a new window with set specifications. However, in the...
2
by: Adam R | last post by:
Looking for an upload progress meter which can works with 'non-patched' PHP4. -- -------------------------------------- Adam Raszkiewicz Brothers-in-arts.com...
5
by: maniesh | last post by:
I want to complete a project and I want to place a timed progress bar on my splash screen, I want to find out a method of timing my progress bar to update every 10 seconds. here's what I got so...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.