Adding Elapsed times

[joeserrone]

Hello Everyone,
I wanted to get your opinion on a database I'm working on. I have a subform
that displays a record, when the form opens I'm capturing the current time as
my Start Time and when the user exits from the form I'm capturing the current
time as my Ending Time. I then use the time difference for statistical data.
This works fine but I wanted to bring it a step forward.
How Can I design the database so that if the user goes in the Record again
the time spend will be added to the initial time. So for example, Bob
accesses Record A he opens the form at 9:00 A.M. and closes it at 9:02 A.M.
he then goes back in record A to do some more keying at 9:05 and exits out of
the record at 9:07. Therefore he was in Record A for a total of 4 minutes.

I appreciate any help on this.

Thanks

 

[WayPay]

You can use the form's OnCurrent event to write a log entry when the user changes records.

 

[missinglinq]

This routine will calculate the time worked, round it to the nearest minute, and add it to the previous time worked, keeping a running total.

Add a field to your underlying table, name it TimeWorked.

Set it's Datatype to Number. Leave the Default Value to 0 (zero)

If your form is based on a Query, go into the Query Grid and add the field to the Query, so that it will be available to the form. If the form is based directly on your table, skip this step.


In Design View, add a textbox to your form, name it txtWorkTime.

Switch to the Data tab and set it's Control Source to the field TimeWorked

Add two unbound textboxes to your form, name them txtStartTime and txtStopTime. Go into Properties - Format and set the Visible property for each of them to No.

Now enter this code.

This code "starts the clock" when you move to a record. If the user doesn't actually work on the record, i.e. if they simply scroll thru it or simply look it over, without making any changes, the "TimeWorked" won't be changed, because there'll be no end time recorded.

Private Sub Form_Current()
 
 Me.txtStartTime = Now()

End Sub

If a change is made to the record, however, the Form_BeforeUpdate event will fire, and the ending time will be generated, the elapsed time calculated and added to any previous "TimeWorked."

Private Sub Form_BeforeUpdate(Cancel As Integer)
  
  Me.txtStopTime = Now()
  
  If Me.txtWorkTime = 0 Then
    Me.txtWorkTime = Round(DateDiff("s", Me.txtStartTime, Me.txtStopTime) / 60)
  Else
    Me.txtWorkTime = Me.txtWorkTime + Round(DateDiff("s", Me.txtStartTime, Me.txtStopTime) / 60)
  End If

  Me.txtStartTime = Null
  Me.txtStopTime = Null

End Sub

 

[joeserrone]

Thanks missinglinq I appreciate all your help on this

 

[missinglinq]

Glad to help!