UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> progress bar to show progress of query    
 
   
aamer
post Nov 29 2004, 10:15 AM
Post #1

UtterAccess Addict
Posts: 128



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....
Go to the top of the page
 
+
strive4peace
post Nov 29 2004, 10:56 PM
Post #2

UtterAccess Leader
Posts: 20,385



for reporting the time...
put these procedures in a general module
!--c1-->
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

in the beginning of your code
StartTime
then
currentdb.execute "Queryname"
at the end of your code
ReportElapsedTime "Whatever message -- optional"
Go to the top of the page
 
+
strive4peace
post Nov 29 2004, 11:14 PM
Post #3

UtterAccess Leader
Posts: 20,385



for a status bar
nsert, 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
Go to the top of the page
 
+
aamer
post Dec 1 2004, 12:56 PM
Post #4

UtterAccess Addict
Posts: 128



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.
Go to the top of the page
 
+
WildBird
post Dec 1 2004, 01:43 PM
Post #5

UtterAccess VIP
Posts: 3,163
From: Perth, Australia



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.
Go to the top of the page
 
+
strive4peace
post Dec 1 2004, 04:32 PM
Post #6

UtterAccess Leader
Posts: 20,385



where do i get the 'value' in "me.prgbar_cointrolname.value
alue is 1 to 100 -- a perentage of how far along you want the display
Odon'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.
Go to the top of the page
 
+
aamer
post Dec 1 2004, 06:37 PM
Post #7

UtterAccess Addict
Posts: 128



Wildbird,
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")

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.
Go to the top of the page
 
+
strive4peace
post Dec 1 2004, 08:30 PM
Post #8

UtterAccess Leader
Posts: 20,385



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 wink.gif Maybe even drink it and get another cup too!
Go to the top of the page
 
+
martino954
post Apr 8 2013, 03:33 PM
Post #9

UtterAccess Enthusiast
Posts: 59



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
Go to the top of the page
 
+
gemmathehusky
post Apr 9 2013, 11:23 AM
Post #10

UtterAccess VIP
Posts: 2,765
From: UK



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.
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 28th November 2014 - 04:34 AM