Date/Time

[Mary10k]

Hello,
I have a form with a start date and end date text box. I would like these text boxes to auto populate with today's date and time so I can calculate the amount of time a user spends on a record. Is this possible and could I start this from the time a record is selected from a list box to the time another record is selected or until the user exits the application?

I am a beginner.
Thank you.

 

[PHV]

Take a look at the Now() function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBslammer]

A bound form provides an event that tells you when it becomes "Dirty," so that would be an ideal place to start the editing clock. A user can view a record without editing it (or "Dirtying" it), so if you want to know when a user is merely viewing a record you could use the Form_Current event, but is that what you want?.

Another event named "Form_AfterUpdate" is fired after the record is saved, which is where the stop time should be recorded.

To capture the times, instead of using textboxes, you can use module-level variables for the form, placed in the declarations section of the module:

Option Compare Database
Option Explicit

Private mblnDirty As Boolean    [green]'is the form dirty?[/green]
Private mdatStartTime As Date   [green]'the start time[/green]
Private mdatEndTime As Date     [green]'the end time[/green]

Then create a function that performs the calculation:

Sub UpdateEditingTime()
  Dim datElapsed As String

  If mblnDirty = True Then
    mdatEndTime = Now
    datElapsed = Format(mdatEndTime - mdatStartTime, "hh:mm:ss")
    MsgBox "Editing Time: " & datElapsed
    mblnDirty = False
  End If
End Sub

Then you can set the mblnDirty variable and the start time in the Form_Dirty() event:

Private Sub Form_Dirty(Cancel As Integer)
  mblnDirty = True
  mdatStartTime = Now
End Sub

Finally, call the function after the editing is completed in the Form_AfterUpdate() event:

Private Sub Form_AfterUpdate()
  Call UpdateEditingTime
End Sub

This example merely displays a message box showing the editing time, so you'll have to add a line of code to put the time in a textbox for storage.

VBSlammer

Unemployed in Houston, Texas

 

[Mary10k]

Hi,
Thank you very much for the code VBSlammer. I entered this into a Module and nothing happens. My two fields in the table are StartTime and EndTime. Do I need to change the names to mdatStartTime? Any advice?

Thank you.

 

[VBslammer]

I'm not sure what you're attempting to do. If you track the edits in the same table as the data, each update will overwrite the previous one. The only way to track multiple audits for a single record would be to create a separate related table for the audit history.

Either way, if you just want to capture the start and end times in a bound field:

Private Sub Form_Dirty(Cancel As Integer)
  Me![StartTime] = Now
End Sub

Private Sub Form_AfterUpdate()
  Me![EndTime] = Now
End Sub

VBSlammer

Unemployed in Houston, Texas