I need access to show a progress bar displaying what percent of an action query has completed, just like Access shows it in the status bar. Only I need it to show up in a form.
I'm familiar with the Microsoft Progress bar control, but don't know where to get query completion data from...this may not even be possible.
I would also like to estimate how much time running a query will take if possible....
Full Version: progress bar to show progress of query
for reporting the time...
put these procedures in a general module
in the beginning of your code
StartTime
then
currentdb.execute "Queryname"
at the end of your code
ReportElapsedTime "Whatever message -- optional"
put these procedures in a general module
CODE
Sub StartTime(Optional pMsg)
On Error Resume Next
gStartTime = Now()
DoCmd.Hourglass True
If IsMissing(pMsg) Then Exit Sub
Debug.Print "--- START-------------" & pMsg & " ----- " & CStr(gStartTime)
End Sub
Sub ReportElapsedTime(Optional pMessage)
On Error Resume Next
Dim m As String, mEndTime As Date
mEndTime = Now()
DoCmd.Hourglass False
If IsMissing(pMessage) Then
m = ""
Else
m = pMessage & ": " & vbCrLf & vbCrLf
Debug.Print "-------------" & pMessage & " ----- "
End If
m = m & "Start Time: " & Format(gStartTime, "hh:nn:ss") & vbCrLf _
& "End Time: " & Format(mEndTime, "hh:nn:ss") & vbCrLf & vbCrLf _
& "Elapsed: " & Format((mEndTime - gStartTime) * 24 * 60, "0.##") & " minutes"
MsgBox m, , "Time to execute "
End Sub
On Error Resume Next
gStartTime = Now()
DoCmd.Hourglass True
If IsMissing(pMsg) Then Exit Sub
Debug.Print "--- START-------------" & pMsg & " ----- " & CStr(gStartTime)
End Sub
Sub ReportElapsedTime(Optional pMessage)
On Error Resume Next
Dim m As String, mEndTime As Date
mEndTime = Now()
DoCmd.Hourglass False
If IsMissing(pMessage) Then
m = ""
Else
m = pMessage & ": " & vbCrLf & vbCrLf
Debug.Print "-------------" & pMessage & " ----- "
End If
m = m & "Start Time: " & Format(gStartTime, "hh:nn:ss") & vbCrLf _
& "End Time: " & Format(mEndTime, "hh:nn:ss") & vbCrLf & vbCrLf _
& "Elapsed: " & Format((mEndTime - gStartTime) * 24 * 60, "0.##") & " minutes"
MsgBox m, , "Time to execute "
End Sub
in the beginning of your code
StartTime
then
currentdb.execute "Queryname"
at the end of your code
ReportElapsedTime "Whatever message -- optional"
for a status bar
Insert, ActiveX control
Microsoft StatusBar Control
right-click on it and choose SBarCtrl Object. Properties...
to set General, Panel, font, and picture information
example of how to set text in a panel:
Forms(gForm)!controlname.Panels(1).Text = m
for a progress bar:
Insert, ActiveX control
Microsoft ProgressBar Control
to change the value (if you are in code behind the form, use Me., otherwise the full reference)
me.prgbar_cointrolname.value = ## number: 0 -100
you can also set the Visable property, of course, to hide it
Insert, ActiveX control
Microsoft StatusBar Control
right-click on it and choose SBarCtrl Object. Properties...
to set General, Panel, font, and picture information
example of how to set text in a panel:
Forms(gForm)!controlname.Panels(1).Text = m
for a progress bar:
Insert, ActiveX control
Microsoft ProgressBar Control
to change the value (if you are in code behind the form, use Me., otherwise the full reference)
me.prgbar_cointrolname.value = ## number: 0 -100
you can also set the Visable property, of course, to hide it
Thanks for helping.
The code above for reporting query run time, will report the time after the query has run correct? I want to report estimated query run time and estimated time left for query to complete while the query is running.
For the progressbar....where do i get the 'value' in "me.prgbar_cointrolname.value" ? And how do I get code to update the value when code is running the query. If I say "db.execute queryname", code won't move to the next line to update the progressbar control until "db.execute queryname" has finished running.
Any help is appreciated.
The code above for reporting query run time, will report the time after the query has run correct? I want to report estimated query run time and estimated time left for query to complete while the query is running.
For the progressbar....where do i get the 'value' in "me.prgbar_cointrolname.value" ? And how do I get code to update the value when code is running the query. If I say "db.execute queryname", code won't move to the next line to update the progressbar control until "db.execute queryname" has finished running.
Any help is appreciated.
Only way I can think of is to use code for all of it, not a query. Open a recordset of records you want to take the action on. Get a recordcount, and then for each one you can show a percentage bar, or a message box etc showing progress as you will know what record it is processing, and how many in total it will process. Not as quick as a query, but may be better as user can see progress.
Sorry dont have time to give code etc, just the theory at this stage!
Good luck.
Sorry dont have time to give code etc, just the theory at this stage!
Good luck.
where do i get the 'value' in "me.prgbar_cointrolname.value
value is 1 to 100 -- a perentage of how far along you want the display
I don't think you can update while the query is running, but if have more than one step, you can update it before each one.
When a query runs, there already IS a progress bar that shows up on the status line -- Tools, Options, View tab to turn on Status bar.
value is 1 to 100 -- a perentage of how far along you want the display
I don't think you can update while the query is running, but if have more than one step, you can update it before each one.
When a query runs, there already IS a progress bar that shows up on the status line -- Tools, Options, View tab to turn on Status bar.
Wildbird,
I like your theory...but this would work only on an action query right? The query that I need estimated run times for are select queries...
For e.g the code below would select the records....after the second line has finished executing, all records would've been selected....and hence no way to determine what percent of a query has already completed...or to estimate completion times.
Is there another way to create a recordset which will perhaps allow me to do what you're suggesting?
strive4peace2004 - I'm aware that there already IS a progressbar, but client also wants percentange of query completion (not in the built-in progressbar), and estimated query completion times.
I like your theory...but this would work only on an action query right? The query that I need estimated run times for are select queries...
For e.g the code below would select the records....after the second line has finished executing, all records would've been selected....and hence no way to determine what percent of a query has already completed...or to estimate completion times.
Is there another way to create a recordset which will perhaps allow me to do what you're suggesting?
CODE
set dim rs as Recordset
set rs = currentdb.openrecordset("SELECT WHATEVER FROM WHATEVER")
set rs = currentdb.openrecordset("SELECT WHATEVER FROM WHATEVER")
strive4peace2004 - I'm aware that there already IS a progressbar, but client also wants percentange of query completion (not in the built-in progressbar), and estimated query completion times.
I can understand wanting a progressbar, but not at the expense of the routine taking more time -- queries are generally faster than looping through recordsets. It would be better to put a timer on and simply report how long it takes. The user will learn that they have time to get coffee 
Maybe even drink it and get another cup too!
Maybe even drink it and get another cup too!
Hi,
you say:
for a progress bar:
line 1- Insert, ActiveX control
line 2 ------ Microsoft ProgressBar Controlto change the value (if you are in code behind the form, use Me., otherwise the full reference)
me.prgbar_cointrolname.value = ## number: 0 -100
you can also set the Visable property, of course, to hide it
How Can I do line 1 and line 2?
I use Access 2007.
Thanks
Martino
you say:
for a progress bar:
line 1- Insert, ActiveX control
line 2 ------ Microsoft ProgressBar Controlto change the value (if you are in code behind the form, use Me., otherwise the full reference)
me.prgbar_cointrolname.value = ## number: 0 -100
you can also set the Visable property, of course, to hide it
How Can I do line 1 and line 2?
I use Access 2007.
Thanks
Martino
QUOTE (martino954 @ Apr 8 2013, 09:33 PM) 
Hi,
you say:
for a progress bar:
line 1- Insert, ActiveX control
line 2 ------ Microsoft ProgressBar Controlto change the value (if you are in code behind the form, use Me., otherwise the full reference)
me.prgbar_cointrolname.value = ## number: 0 -100
you can also set the Visable property, of course, to hide it
How Can I do line 1 and line 2?
I use Access 2007.
Thanks
Martino
you say:
for a progress bar:
line 1- Insert, ActiveX control
line 2 ------ Microsoft ProgressBar Controlto change the value (if you are in code behind the form, use Me., otherwise the full reference)
me.prgbar_cointrolname.value = ## number: 0 -100
you can also set the Visable property, of course, to hide it
How Can I do line 1 and line 2?
I use Access 2007.
Thanks
Martino
the point was, I think, that the progress bar shown during execution of a single query is determined by access.
you can't usefully add your own as the query itself is just a single event.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.