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. - Initiate the Meter using the acSysCmdInitMeter Action Argument, descriptive text, and a Value Argument which is the Maximum Value of the Meter.
- Periodically update the Meter with the acSysCmdUpdateMeter Action Argument and a Value Argument indicating the relative progress of the task at hand.
- Remove the Meter using the acSysCmdClearStatus.
- '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,
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.
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 ?
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.
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
-
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
Awesome, sorry I posted this in the wrong place, but working on three databases at the same time. Worked perfect.
@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.
ADezii, thank you very much! I will look into making this work in my application.
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.
Awaiting response please..
Without getting very complex, the easiest approach would be to Update the Progress Meter by 25% after the execution of each individual Update Query.
Thanks ADezii,
But how to proceed for it..
Please help me to understand this.
thanks again.
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.
Thank you so much dear..! I'll try this, and let you results.
(O God..! when will I learn all this..)
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?
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.
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.
In the declaration of your variables, add this:
Then immediately before and after the code to run your Append query, add this:
This might force the system to show the meter.
how do I dim X? Because I know that you have to dim X as something
But thank you very much for the answer
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.
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.
This is the full code am using now - On Error GoTo Err_handler
-
DoCmd.SetWarnings False
-
Dim x
-
Dim varReturn As Variant
-
Dim intCtr As Long
-
Dim StrWhere As String
-
Dim strMsg As String
-
Dim StrReport As String
-
StrReport = "Form1Term1SR"
-
If Me.Dirty Then
-
Me.Dirty = False
-
End If
-
-
'Initialize the Progress Meter, set Maximum Value = 100
-
x = DoEvents
-
varReturn = SysCmd(acSysCmdInitMeter, "Loading Reports Please Wait...", 100)
-
DoCmd.Hourglass True
-
-
-
DoCmd.OpenQuery "form1QTerm1appendQ"
-
-
x = DoEvents
-
For intCtr = 1 To 10000
-
Debug.Print intCtr
-
Next
-
-
'Update the Progress Meter to 80%
-
varReturn = SysCmd(acSysCmdUpdateMeter, 80)
-
-
-
DoCmd.OpenReport StrReport, acViewPreview
-
-
'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_handler:
-
Exit Sub
-
-
Err_handler:
-
DoCmd.Hourglass False
-
varReturn = SysCmd(acSysCmdClearStatus) 'Clear Meter in event of an Error
-
MsgBox Err.Description, vbExclamation, "Error in Meter"
-
Resume Exit_handler
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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"...
|
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...
|
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?...
|
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....
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |