By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,889 Members | 1,358 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,889 IT Pros & Developers. It's quick & easy.

Time During Query

blyxx86
100+
P: 256
I have created a form that keeps record of a few queries that are ran. One set of queries takes 30 minutes to pull the information. The other takes about 6 minutes.

I managed to use two variables to store the time before and after the queries are finished.

However, I am trying to have a textbox on one of the forms count up (one second at a time) to show the elapsed time. I've tried setting up an 'OnTimer' event with TimerInterval = 1000

I'm handwriting all of this as it's on another station currently...
The form functions when a query is not running.

SetTimeFormat is a function I found through this website or Google that converts a number to Minutes:Seconds or Hours:Minutes

It seems like the OnTimer event stops running when a query is running?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Timer()
  2.  
  3. Dim dblElapsed As Double
  4.    Me.Repaint
  5.    dblElapsed =  DateDiff("s","" & Me.txtStart & "", "" & Time() & "")
  6.    Me.Repaint
  7.    Me.txtElapsed = SetTimeFormat(dblElapsed)
  8.    Me.Repaint
  9. End Sub
  10.  
Aug 16 '07 #1
Share this Question
Share on Google+
3 Replies


FishVal
Expert 2.5K+
P: 2,653
I have created a form that keeps record of a few queries that are ran. One set of queries takes 30 minutes to pull the information. The other takes about 6 minutes.

I managed to use two variables to store the time before and after the queries are finished.

However, I am trying to have a textbox on one of the forms count up (one second at a time) to show the elapsed time. I've tried setting up an 'OnTimer' event with TimerInterval = 1000

I'm handwriting all of this as it's on another station currently...
The form functions when a query is not running.

SetTimeFormat is a function I found through this website or Google that converts a number to Minutes:Seconds or Hours:Minutes

It seems like the OnTimer event stops running when a query is running?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Timer()
  2.  
  3. Dim dblElapsed As Double
  4.    Me.Repaint
  5.    dblElapsed =  DateDiff("s","" & Me.txtStart & "", "" & Time() & "")
  6.    Me.Repaint
  7.    Me.txtElapsed = SetTimeFormat(dblElapsed)
  8.    Me.Repaint
  9. End Sub
  10.  
Hi, blyxx.
You are right no timer event in form will be triggered while query is running.
I'm not sure whether it will work, but you may use Win timers to handle it.
Maximum it will crash your system. ;) Make backup first.

In the form module (I guess) before query runs.
Expand|Select|Wrap|Line Numbers
  1. Dim lngTimerID As Long 'global var to store TimerID to kill it further
  2.  
  3. Private Sub btnRunQuery_Click()
  4.     ............
  5.     'set 1000 ms system timer
  6.     lngTimerID = SetTimer(Me.hwnd, 0, 1000, AddressOf TimerProc)
  7.  
  8. End Sub
  9.  
  10. Private Sub Form_Close()
  11.     KillTimer Me.hwnd, lngTimerID
  12. End Sub
  13.  
In a public module
Expand|Select|Wrap|Line Numbers
  1. Public Declare Function SetTimer Lib "user32" _
  2.      (ByVal hwnd As Long, ByVal uTimerID As Long, ByVal uInterval As Long, _
  3.      ByVal fpCallback As Long) As Long
  4.  
  5. Public Declare Function KillTimer Lib "user32" _
  6.     (ByVal hwnd As Long, ByVal uTimerID As Long) As Boolean
  7.  
  8.  
  9. Public Sub TimerProc(ByVal hwnd As Long, ByVal uMsg As Long, _
  10.     ByVal uEvent As Long, ByVal dwTime As Long)
  11.  
  12.     On Error Resume Next
  13.     Forms!Form1!txtTime = Now
  14. End Sub
  15.  
at least with MsgBox active (you know, this blocks events too) it runs perfectly.
Aug 16 '07 #2

FishVal
Expert 2.5K+
P: 2,653

blyxx86
100+
P: 256
I will give this a shot. Thank you. :)
Aug 16 '07 #4

Post your reply

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