Time During Query

[blyxx86]

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?

Private Sub Form_Timer()
 
Dim dblElapsed As Double
   Me.Repaint
   dblElapsed =  DateDiff("s","" & Me.txtStart & "", "" & Time() & "")
   Me.Repaint
   Me.txtElapsed = SetTimeFormat(dblElapsed)
   Me.Repaint
End Sub
 

[FishVal]

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?

Private Sub Form_Timer()
 
Dim dblElapsed As Double
   Me.Repaint
   dblElapsed =  DateDiff("s","" & Me.txtStart & "", "" & Time() & "")
   Me.Repaint
   Me.txtElapsed = SetTimeFormat(dblElapsed)
   Me.Repaint
End Sub
 

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.

Dim lngTimerID As Long 'global var to store TimerID to kill it further
 
Private Sub btnRunQuery_Click()
    ............
    'set 1000 ms system timer
    lngTimerID = SetTimer(Me.hwnd, 0, 1000, AddressOf TimerProc)
 
End Sub
 
Private Sub Form_Close()
    KillTimer Me.hwnd, lngTimerID
End Sub
 

In a public module

Public Declare Function SetTimer Lib "user32" _
     (ByVal hwnd As Long, ByVal uTimerID As Long, ByVal uInterval As Long, _
     ByVal fpCallback As Long) As Long
 
Public Declare Function KillTimer Lib "user32" _
    (ByVal hwnd As Long, ByVal uTimerID As Long) As Boolean
 
 
Public Sub TimerProc(ByVal hwnd As Long, ByVal uMsg As Long, _
    ByVal uEvent As Long, ByVal dwTime As Long)
 
    On Error Resume Next
    Forms!Form1!txtTime = Now
End Sub
 

at least with MsgBox active (you know, this blocks events too) it runs perfectly.

[FishVal]

BTW take look at this.
TheScripts Tip of the Week #24 - ADO Events [Advanced Content]

[blyxx86]

I will give this a shot. Thank you. :)