Auto time stampe a field

[Mary10k]

Hi,
I have two fields (Start Time and End Time) on my Patient form. Is there a function I can create to auto populate those fields with the Now function.

I am thinking start would be the time the form opens and end would be when the user moves off of a record.

Thank you.

 

[rstitzel]

You could on the OPEN FORM EVENT add the code

me!StartTime = Time
me!EndTime = Time

 

[TheAceMan1]

How are ya Mary10k . . . . .

Are the fields [blue]bound[/blue] or [blue]unbound?[/blue]

Are you sure you want [purple]EndTime[/purple] to be tagged [purple]when user changes record?[/purple] (this doesn't appear to fit any logic, so I'll have to believe you have your reasons . . .)

See Ya! . . . . . .

 

[SilentAiche]

Mary10K:

This newbie hesitates to respond given the experience of the first two correspondents, but I had a question. Do you need to keep a patient history of start/end times? I wondered if the suggested design wouldn't overwrite previous start/end times each time the form was opened. Do you want the times to change each time someone opens the form (even if no data is changed)? My "Event" skills are weak, which may explain why I have a question.

Best wishes to all,
Tim

 

[Mary10k]

The fields are bound to a contact table which allows me to track each time a record is accessed/changed. Yes, I want to record the history of the length of time someone works on a record.

Thank you.

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmmm, the Time a form is opened is not the same as the time when the record was retrieved. See th ubiquitous {F1} (aka H E L P) for the differences. I believe you want OnActivate vs. OnOpen event as the start.

Maintaing a "History" is, however, vastly different than having the most recent occurance of the time (interval) the record was opened. See faq181-291 for at least ONE way to actually track the HISTORY of a record, although that version does not include the time (interval) the record was available via the form. Further, be aware that this (and other schemes) are not - within Ms. A. (Jet) recordsets, prevent (sophisticated) Users from bypassing the forms and changing data directly within th tables, leaving no track / trace of th who, what, when of the alterations. You CAN use the built in security to limit users' access to objects and thus enhance the tracking.





MichaelRed

 

[TheAceMan1]

OK Mary10k . . . . .

The following will update [blue]Start/End Time[/blue] as you've prescribed . . . . at least as far as these fields of the RecordSource of the form are concerned! Table structure & names may be required if a seperate history table from that of the form (as I suspect) is involved.

Note: The [purple]updates are done in the background[/purple] as [blue]I don't believe this is something you want in view for users to see[/blue]. If this is not so, then slight modifications will have to be made to the code . . . here's the steps:
[ol][li][purple]Add a TextBox[/purple] to the form (perferably in the form header or footer). [purple]Delete the Label[/purple] of the textbox. Set the following properties for the textbox:
[ol a][li][purple]Left[/purple] 0[/li]
[li][purple]Top[/purple] 0[/li]
[li][purple]Width[/purple] 0[/li]
[li][purple]Height[/purple] 0[/li]
[li][purple]Name[/purple] PrevID[/li][/ol][/li]
[li] In the [blue]On Dirty Event[/blue] of the form, copy/paste the follwoing code (in all code . . . [blue]you[/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Me!StartTime = Now()
   Me!PrevID = Me![purple][b]PrimaryKeyName[/b][/purple][/blue]

[/li]
[li]Next . . . in the [blue]On Current Event[/blue] of the form, copy/paste the following code:

[blue]   Dim SQL As String
   
   If Trim(Me!PrevID & "") <> "" Then
      SQL = "UPDATE [purple][b]TableName[/b][/purple] SET EndTime = Now() " & _
            "WHERE ([purple][b]PrimaryKeyName[/b][/purple]=" & Me![PrevID] & ");"
      DoCmd.RunSQL SQL
      Me!PrevID = ""
   End If[/blue]

[/li][/ol]
Again, the times are set in the background. Believing you have another form for viewing these times, they should show there.

[blue]Thats it! . . . . give it a whirl & let me know! . . . .[/blue]

See Ya! . . . . . .

 

[Mary10k]

Hello,
I tried your code by typing in the following and I am getting an error "Method or data member not found". Do you know what causes this?
Private Sub Form_Current()
Dim SQL As String

If Trim(Me!PrevID & "") <> "" Then
SQL = "UPDATE Contact SET EndTime = Now() " & _
"WHERE (Contact_ID=" & Me![PrevID] & ");"
DoCmd.Run SQL
Me!PrevID = ""
End If

End Sub



Private Sub PrevID_Dirty(Cancel As Integer)
Me!StartTime = Now()
Me!PrevID = Me!Contact_ID
End Sub

 

[TheAceMan1]

Mary10k . . . . .

[blue] DoCmd.Run SQL
[purple][b]should be:[/b][/purple]
 DoCmd.RunSQL SQL[/blue]

See Ya! . . . . . .