Help with VBA PLEASE!!!

[PL]

I created a timer to time certain orders that I will be processing. I
created the timer form based on a table so that the times would be bound (the
ElapsedTime text box) to it. I then added two command buttons, one to reset
the timer, and one to start/stop the timer. I attempted to code it so that
each record would have its own separate timer (before this the timer
continually counted with each record.)The only problem I have is my timer now
counts time very fast and progressively faster as time goes on. I believe
the problem to be in the private sub form timer portion of my code, but have
had no luck. I am a beginning coder and need help desperately!! Here is the
present code. Please Help!!

Option Compare Database
Option Explicit
Dim TotalElapsedSec As Long
Dim StartTickCount As Long
Private Declare Function GetTickCount Lib "kernel32" () As Long

Private Sub cmdReset_Click()
TotalElapsedSec = 0
Me!ElapsedTime = "00:00:00"
End Sub

Private Sub cmdStartStop_Click()

If Me.TimerInterval = 0 Then
StartTickCount = GetTickCount()
Me.TimerInterval = 30
Me!cmdStartStop.Caption = "&STOP"
Me.cmdStartStop.ForeColor = RGB(255, 0, 0)
Me.cmdStartStop.FontSize = "12"
Me!cmdReset.Enabled = False
Else
TotalElapsedSec = TotalElapsedSec + (GetTickCount() - StartTickCount)
Me.TimerInterval = 0
Me!cmdStartStop.Caption = "Start the &Timer"
Me.cmdStartStop.ForeColor = RGB(0, 64, 128)
Me.cmdStartStop.FontSize = "8"
Me!cmdReset.Enabled = True
End If
End Sub


Private Sub Form_Timer()
Dim Hours As String
Dim Minutes As String
Dim Seconds As String
Dim Msg As String
Dim ElapsedSec As Long
Dim hour As Long
Dim minute As Long
Dim second As Long
Dim milisec As Long
Dim temp As String
hour = Left(Me!ElapsedTime, 2)
temp = Left(Me!ElapsedTime, 5)
minute = Right(temp, 2)
second = Right(Me!ElapsedTime, 2)
TotalElapsedSec = 3600000 * hour
TotalElapsedSec = TotalElapsedSec + (60000 * minute)
TotalElapsedSec = TotalElapsedSec + (second * 1000)

ElapsedSec = (GetTickCount() - StartTickCount) + TotalElapsedSec

Hours = Format((ElapsedSec \ 3600000), "00")
Minutes = Format((ElapsedSec \ 60000) Mod 60, "00")
Seconds = Format((ElapsedSec \ 1000) Mod 60, "00")

Me!ElapsedTime = Hours & ":" & Minutes & ":" & Seconds
End Sub

 

[Ron Weiner]

PL

I am having trouble following your code so here is some code that works
correctly in a test form I created. You can easily recreate this form by:

On a new Form
Add Label - lblElapsedTime
Add Command Button - cmdStartStop
Set the "Allow Design Changes" property of the form to "Design View Only"


Then in the code module for the form add:

Option Compare Database
Option Explicit
Private Declare Function GetTime Lib "kernel32" Alias "GetTickCount" () As
Long
Dim mlngStartTime As Long

Private Function ConvertElapsedTime(lngElapsedTimeInMs) As String
' Purpose Convert an elapsed time in Ms to a HH:MM:SS.Ms string
Dim strMs As String, strSeconds As String
Dim strMinutes As String, strHours As String

strMs = ((lngElapsedTimeInMs Mod 1000) \ 10) / 100
strMs = Mid(strMs & "00", InStr(strMs, ".") + 1, 2)
strSeconds = Right("00" & (lngElapsedTimeInMs \ 1000) Mod 60, 2)
strMinutes = Right("00" & (lngElapsedTimeInMs \ 60000) Mod 60, 2)
strHours = Right("00" & (lngElapsedTimeInMs \ 3600000) Mod 24, 2)
ConvertElapsedTime = strHours & ":" & strMinutes & ":" & strSeconds &
"." & strMs
End Function

Private Sub cmdStartStop_Click()
If Me.TimerInterval = 0 Then
' Start the timer
mlngStartTime = GetTime()
lblElapsedTime.Caption = ConvertElapsedTime(0)
Me.TimerInterval = 1000
Else
' Stop the timer
Me.TimerInterval = 0
lblElapsedTime.Caption = ConvertElapsedTime(GetTime() -
mlngStartTime)
End If
End Sub

Private Sub Form_Timer()
lblElapsedTime.Caption = ConvertElapsedTime(GetTime() - mlngStartTime)
End Sub

Watch out for any wrapped lines above and you should be good to go. This
code updates the display once a second. I noticed that you are updating the
display every 30Ms. Unless the events you are attempting to time are very
short lived (they don't appear to be) I would recommend stretching out this
setting to once a second. Updating the screen every 30 Ms seems wasteful to
me, especially since you seem to be throwing away the Ms.

Another suggestion would be to NOT set the Elapsed Time to a field that was
bound to a column in your back end. I recommend that you set that column
just once, when the timer stops. You may be hitting the database every 30Ms
with a new value. Also a waste of resources.

Ron W

 

[PL]

Thanks Rob, the form works well. There is still the problem I was having
though. When you click stop on the form, and then start again, the time
resets to 0. I would like it to not reset, but instead begin timing where it
left off. Most of all, I need the text box to be bound to a table to record
the times, which I have, and need the timer TO reset when going to a new
record (a new order) to time. If there is already a value in the text box
other than 0, say 00:00:12:00, then I would like it to begin counting again
at 00:00:13:00 when started again. I am attempting to create a form that
records the time to process an order, but there will be many orders at once,
and the orders will be stopped and started again and different times, so the
timer must be, in a sense, bound only to each correspoding record. I am
caught on this problem and any more advice will help. Thanks for the
previous reply.
PL

 

[Ron Weiner]

PL

If you store the elapsed time in your database as Long in Milliseconds then
you can easily do what you waln by making a few small changes to the form in
my example.

Replace the following code in the form I submitted last week with:

Private Sub cmdStartStop_Click()
If Me.TimerInterval = 0 Then
' Start the timer
mlngStartTime = GetTime()
lblElapsedTime.Caption = ConvertElapsedTime(Me!ElapsedTime)
Me.TimerInterval = 1000
Else
' Stop the timer
Me.TimerInterval = 0
lblElapsedTime.Caption = ConvertElapsedTime(GetTime() -
mlngStartTime + Me!ElapsedTime)
Me!ElapsedTime = Me!ElapsedTime + (GetTime() - mlngStartTime)
End If
End Sub

Private Sub Form_Timer()
lblElapsedTime.Caption = ConvertElapsedTime(GetTime() - mlngStartTime +
Me!ElapsedTime)
End Sub

Where the name of the column in the table the form is bound to is
ElapsedTime. This should I think get you where where you want to go.

Ron W