Date/Time stamp of new & modified records

[Guest]

I have developed several tables and associated forms to input the data into
the tables. In each table I have both a Creation Timestamp and a Modify
Timestamp Field Name with the Data Type defined as Date/Time.

For the Creation field I have the Default Value, in the Field Properties set
for =Date()+Time(). This, I believe gives me a date and timestamp for the
creation of the record.

My question is- How do I date and timestamp any modifications that are done
to the record after it was created? I would like to use these two fields to
make a report that shows me what records were modified after the creation of
the original record.

Thank you for any help you can provide.

 

[Guest]

To get the time and date just use Now()

If you update the records through a form, then on the before update event of
the form you can enter the code
Me.dateModifiedFieldName = Now()

 

[Guest]

Ofer:

Thank you for your timely response.

I do not understand this code or where to put it. Where is the before update
event of the form located to add this code? Do I put the code you have
written in exactly the way you have it? My field name is Modify Timestamp. Do
I put this in where you have FieldName or should it look like-
Me.Modify_Timestamp = Now(). What does the Me mean? As you can see I am
confused.

Once I understand the code and where to put it, will it update all of the
records that I have modified while the record was open? Do I need to have a
Command Button named Update Record to execute this event each time a record
is modified? Can it be executed automatically when you close the form with
the X or use a Command Button that I already have on each form to go to
another form and will all of the modified records have the date and time
updated in the Modify Timestamp field? Does, or can, the code execute each
time a new record is selected? Am I asking too much of this function? I would
like to automate it as much as possible to prevent operator errors.

 

[Guest]

Open the form in design view.
Press Ctrl+G or View/properties, now you have the properties display
Press on the top left corner of the form, on the properties box you'll have
now the properties of the form
One of the properties, is After Update, on the left of this property you
have a button with three dots, press on it, now choose code builder
Now you are in the code section
Put this line of code in it

Me.dateModifiedFieldName = Now()

Me - stand for the current form
dateModifiedFieldName - change that to the name of the date field
Now() = date() + time()

 

[Guest]

Ofer:

Here is what I put in the code and it still gives me a Compile Error:

Private Sub Form_AfterUpdate()
Me.ModifiedTimestamp = Now()

End Sub

What am I doing wrong? The Compiler highlights the .ModifiedTimestamp part
of the code, which is the name of the field that I want to put the time &
date stamp in. I have triple checked the field name. I have also tried
quotation marks around the field name, removed spaces, added spaces, put my
form's name in place of the Me, etc.

I am running Access 2000 (9.0.6926 SP-3)

 

[Guest]

What is the error message.
Is ModifiedTimestamp its the name of the field in the form, and not in the
table.
when you type me. it should list all the fields in the form, is
ModifiedTimestamp is one of them

 

[Guest]

Ofer:

The error message is:
Compile Error
Method or data member not found

ModifiedTimestamp is the name of the field in the table. I do not have the
field showing on my form beacuse I do not want to show it or allow it to be
modified. Is this possible or do I have to have it on my form for this to
work? I did not have to have Creation Timestamp on my form for it to put the
date and time in the correct field.

Thanks again for your assistance.

 

[Guest]

Create the field in the form, and define it as visible = False.
Give it the same name as the name of the field in the table, and then try it

 

[Guest]

Ofer:

I tried this while I waited for this response. It works (inputs the date &
time into the correct field in my table) only if I use a Command Button that
I created that takes me back to a Start Page form that I have also created.
If I use the X in the upper right corner to close the form I get a warning
message- You can't save this record at this time. Can you only modify one
record at a time? If this is the case then I can live with it or can I put
another Command Button on my form to update and then select another record to
modify?

Sorry that I have taken a lot of your time but I am learning and I do
appreciate your assistance.