UtterAccess.com
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 VIP
Posts: 20,318
From: Colorado



for reporting the time...
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


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 VIP
Posts: 20,318
From: Colorado



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
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,119
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 VIP
Posts: 20,318
From: Colorado



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.
Go to the top of the page
 
+
aamer
post Dec 1 2004, 06:37 PM
Post #7

UtterAccess Addict
Posts: 128



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?

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 VIP
Posts: 20,318
From: Colorado



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 (IMG:http://www.utteraccess.com/forum/style_emoticons/default/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 Member
Posts: 37



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,470
From: UK



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



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: 20th April 2014 - 11:00 AM