Time Stamp

[tpearo]

Is there a way to for the database to keep a permanent record of time and date when a user enters data into a table through a form.

 

[dhookom]

You can add fields to store the information and then use a form's before update event to stick values in those fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tpearo]

I don't quite understand what you mean. Can you explain it a little bit.

 

[mkov]

You can also just add the fields to the table and set defaults.

Set the default in the date field to =Date()
Set the default in the time field to =Time()

 

[dhookom]

mkov's solution works for origninal entry of a record. Do you need to track the last time a record was edited? If so, do you have some field names that you would like to store the values in?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ProgramError]

If you enter now() in the field it will save both time and date in the same field. All depends on whether you intend working on the time and date as separate items.

I used the now function to check which records have been added by somebody else since I last looked at all the records. neat eh!


Program Error
Why is it, I still think in terms of spectrum BASIC!

 

[tpearo]

dhookom - Yes, I need to keep track of the time of the original entry as well as when the record gets edited.
So I have two fields Date and Time that I have set the default to =Date() and =Time() then I have two more fields
that are CDate and Ctime which represents the completion date and time of a test. Or another words when the record was last edited. I still need to keep the original date and time as well as this represents the time date and time the testing started. This is all in one table. Can you help?

 

[dhookom]

My first reply can be used. You would have code in the before update event of the form like:
Me.txtCDate = Date
Me.txtCTime = Time


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tpearo]

dhookom- O.K. I'm not quite sure exactly how to do this.
I opened the form in design mode and then on the before update I went to code builder and copied exactly what you said Me.txtCDate = Date
Me.txtCTime = Time
And I get a compile error that says: Method or data member not found

What am I doing wrong?

 

[dhookom]

Do you have controls named "txtCDate" and "txtCTime"? If not, substitute your control/field names.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tpearo]

dhookom-The two field names are Cdate and Ctime
Is that what you are referring to as control names?
Do I put the code in the field (before update) or in the form (before update) I'm a little confused. Either way I still get an error.

 

[dhookom]

The code is entered into the form's module in the Before Update event. Your code might look like:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.[CDate] = Date
    Me.ctime = Time
End Sub

Before you go too far consider changing your field names. CDate is a function and can cause issues in your code. If you add a text box to your form you can name the text box txtCdate and avoid some issues.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tpearo]

dhookom - Please bare with me. Maybe I should explain better what I am trying to do. When I put in the code you suggested from your last response the fields CDATE and CTIME returned the value or date/time of the original entry to the record. Another words - I have a field name Date and Time which I use to time stamp when a record was first created. Then I want to use the fields CDATE and CTIME to show when the record was modified. All four fields are in the same table. This table and form is in a multi-user database and there will be one person who enters information in the table when the record is first created. But then another person will add information into other fields later after the initial record creation. I need a time stamp of both when the record was created and then when the record was modified with the additional data entry.
I can rename CDATE to Compdate (completion date)and CTIME to (completion time)
I hope this helps you help me.... Thanks a bunch!

 

[dhookom]

So, did you try the code I suggested? Did it work?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tpearo]

dhookom - Yes I did try the code you suggested however as I stated above it returned the same date and time or the original date and time. Another words the record was originally created on 3/15/05 at 4:00 P.M. that information is stamped in the DATE and TIME field. When the first data is entered.
But I also need to time stamp when the record gets edited. Thus the fields CDATE and CTIME. When I used the code you
suggested it returned 3/15/05 and 4:00 P.M. to the CDATE and CTIME fields.

 

[dhookom]

I think I understand now. Your original fields are named Date and Time (2 fields) which are both function names. You should be more descriptive with your field names. For instance, the Date field should be CreateDate. Your code would then work for the updated field.

Do you see why giving your fields the names of functions will cause Access to get confused?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tpearo]

dhookom - O.K. I renamed the Date and Time fields to CreateDate and CreateTime. I also renamed the fields that I want to update to CompDate and CompTime. I place this code in the BeforeUpdate :

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[Compdate] = Date
Me.Comptime = Time
End Sub

And I get the following error -
Compile error: Method or data member not found
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[Compdate] = Date
Me.Comptime = Time
End Sub
And the Comptime is highlighted.
What does that mean and what am I doing wrong?

I used your example from one of your responses.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[CDate] = Date
Me.ctime = Time
End Sub
But just changed the names.

Thanks for being patient and helping.

 

[dhookom]

Do you have the field CompTime in your form's record source? Do you have a text box bound to this field? If so, what is the name of the text box?

I should have mentioned this earlier but you can store both the date and time in a single field (which I would do). Then your code would only need to be:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[Compdate] = Now()
End Sub


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tpearo]

dhookom - O.K. I found the problem. Although I changed the name in the table I forgot to change the Name in the form.
I had changed the control source alright but forgot to change the name.

Anyway I took your other suggestion and made just one field and put the Me.[Compdate]=Now()

And that works fine.

Thanks for all you help and patience.

Tp