I have a string of queries to run and track the time it takes to run them all. I have been working on this since last Thursday. I read about using API calls but I couldnt figure out how to apply them to my situation so I thought I would try a timer. My code counts up fine but I can't figure out how to have count down. I picked queries that would run about five minutes to keep testing time down. So I would like my timer to start at 5 minutes than count down as the queries are processed. Any help is greatly appreciated.
Tom
Tom
Code:
Public Sub cmdProcess_Click()
Dim strQryName As String
Dim iTime As Integer
Dim iIntValue As Integer
Dim qryCnt As Integer
Dim strtitle As String
Dim mtrStatus As String
Dim StartTime As Long
Dim ElapsedTime As Long
Dim fIncludeCancel As Boolean
Dim mfCancel As Boolean
Dim mySeconds As Integer
Dim myMinutes As Integer
Dim strConvertedTick As String
Dim strTime As String
Dim StartTime1 As Long
Dim iTotalTime As Long
Dim iTimeRef As Long
Const secondsPerDay As Long = 86400
Const secondsPerHour As Long = 3600
Const secondsPerMinute As Long = 60
Const minutesPerHour As Long = 60
strtitle = "FrmMain"
'Start Timer
StartTime = GetTickCount
'Init Meter Section
Me!lblStatus.Caption = "Time Started at 5:00 minutes"
Me!mtrStatus.Width = 0
Me.Caption = strtitle
Me!cmdCancel.Visible = fIncludeCancel
DoCmd.RepaintObject
mfCancel = False
With DoCmd
.SetWarnings False
For qryCnt = 1 To 8
Select Case qryCnt
Case 1
strQryName = "000_ClearRVUTable"
iIntValue = 500
.
.
.
End Select
'Take reading before query is executed
'Orig Formula
ElapsedTime = GetTickCount - StartTime ' find how long it's been
'Try and count down
Debug.Print GetTickCount
Debug.Print StartTime
'Starting point of 5 minutes
iTimeRef = 5 * 60
iTotalTime = (iTimeRef + ElapsedTime) - ElapsedTime
'Convert Time to minutes and seconds
mySeconds = ElapsedTime * 0.001
myMinutes = mySeconds Mod secondsPerHour
mySeconds = myMinutes Mod secondsPerMinute
myMinutes = myMinutes / secondsPerMinute
strTime = Format$(myMinutes, "00") & ":" & Format$(mySeconds, "00")
.RepaintObject
Me!lblStatus.Caption = "Query " & strQryName & " running " & strTime
Me!mtrStatus.Width = iIntValue
.OpenQuery (strQryName)
.RepaintObject
Next qryCnt
.SetWarnings True
End With
End Sub
