Access 2007: Record update timestamps

[HopyMSU]

Hi all,

I've been fighting with this one and can't find any information on it.
I have a feeling it was easier to do this with Access 2003, but maybe
not. If anyone can help me out, I'd appreciate it.

In Access 2007, I have a form where if I edit any (or specific) fields
I want it to stamp the current date and time in a separate field. So,
for example:

I have a "Notes" field. If I edit this field, I want it to stamp the
time in a "DateModified" field at the bottom of the form. I have tried
to use onDirty, AfterUpdate, BeforeUpdate with expressions as well as
VBA code, but I can't get it to work.

Thanks in advance!
Andy

 

[Larry Daugherty]

In the BeforeUpdate event of the Form:

Me!DateModified = Now()

HTH

 

[Todos Menos [MSFT]]

it's not called a timestamp

it's called a DATETIME column

 

[HopyMSU]

What's the difference?

it's not called a timestamp

it's called a DATETIME column

 

[HopyMSU]

Larry,

Thanks, I tried this by got an error message when I move off the
record that I edited (and nothing appears in the DateModified field).
The error says MS Access can't find the object 'Me!DateModified =
Now()'

I also tried it as VB code, and still nothing (no error there, but
just nothing happens).

 

[Albert D. Kallal]

Thanks, I tried this by got an error message when I move off the

record that I edited (and nothing appears in the DateModified field).
The error says MS Access can't find the object 'Me!DateModified =
Now()'

I also tried it as VB code, and still nothing (no error there, but
just nothing happens).

The example posted is VB code.....

So, you have to open the form in design mode, and select the form..and
display the forms properties sheet....

Make sure you enter the event code via the above process...

Me!DateModified = Now()

There should not be extra quotes as you had..

so, the full code listing should look like:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me!DateModified = Now()

End Sub

Of course, you DO NOT have to enter the Private sub..e.tc, but only the one
line...

Try compiling the code before you run it. the above assumes you created a
column in your table, and for purposes of testing, make sure there is a
control on the form that displays the datemodifed field...

 

[HopyMSU]

Albert,

Thanks. I think I am incredibly stupid as I still can't get it to
work. Here is what I have done.

1. Created a table with a few basic controls including:

DateModified

2. Create a form. Open the form in design mode and go to the form's
properties. Where it says:

Before Update

I go into the code builder and write in the code that you and Larry
have spelled out:

Me!DateModified = Now()

3. Debug - Compile, close VB

4. In the Before Update field in the form's properties it says:

[Event Procedure]

Now what??? When I just type:

Me!DateModified = Now()

directly into the property sheet for the form, close and run it, I get
an error. When I have the code in VB (as seen above), NOTHING happens
and the date doesn't display in the DateModified control/field on the
form.

Sorry to be an Access loser, any help is STILL appreciated!

Andy

 

[Todos Menos [MSFT]]

they are different DATA TYPES

Date/Time = Date and Time

TimeStamp = (from books online)

timestamp
timestamp is a data type that exposes automatically generated binary
numbers, which are guaranteed to be unique within a database.
timestamp is used typically as a mechanism for version-stamping table
rows. The storage size is 8 bytes.

Remarks
The Transact-SQL timestamp data type is not the same as the timestamp
data type defined in the SQL-92 standard. The SQL-92 timestamp data
type is equivalent to the Transact-SQL datetime data type.

A future release of Microsoft® SQL Server™ may modify the behavior of
the Transact-SQL timestamp data type to align it with the behavior
defined in the standard. At that time, the current timestamp data type
will be replaced with a rowversion data type.

Microsoft® SQL Server™ 2000 introduces a rowversion synonym for the
timestamp data type. Use rowversion instead of timestamp wherever
possible in DDL statements. rowversion is subject to the behaviors of
data type synonyms. For more information, see Data Type Synonyms.

In a CREATE TABLE or ALTER TABLE statement, you do not have to supply
a column name for the timestamp data type:

CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, timestamp)

If you do not supply a column name, SQL Server generates a column name
of timestamp. The rowversion data type synonym does not follow this
behavior. You must supply a column name when you specify rowversion.

A table can have only one timestamp column. The value in the timestamp
column is updated every time a row containing a timestamp column is
inserted or updated. This property makes a timestamp column a poor
candidate for keys, especially primary keys. Any update made to the
row changes the timestamp value, thereby changing the key value. If
the column is in a primary key, the old key value is no longer valid,
and foreign keys referencing the old value are no longer valid. If the
table is referenced in a dynamic cursor, all updates change the
position of the rows in the cursor. If the column is in an index key,
all updates to the data row also generate updates of the index.

A nonnullable timestamp column is semantically equivalent to a
binary(8) column. A nullable timestamp column is semantically
equivalent to a varbinary(8) column.

 

[Albert D. Kallal]

directly into the property sheet for the form

No, don't do the above..

4. In the Before Update field in the form's properties it says:

[Event Procedure]

Yes, the above is correct. To view, or modify the code, simply click on that
property, and then
you will see the [...] button appear. (a button with 3 dots inside will
appear when you click on the [Event Produce] text. However, you do NOT want
to modify the actual text that says [Event Procedure] -- if you do, then
your code will NOT run). So, it is this button you must click on to view, or
write code for a event.

When I have the code in VB (as seen above), NOTHING happens
and the date doesn't display in the DateModified control/field on the
form.

The modified date/time will not display in the control until you close the
form, or move to the NEXT record. the "before update" event ONLY fires if
you actually modify some other fields on the form. So, you actually will
only be typing in the one line of code in the code editor, but ms-access
will fill the surrounding code with the start/end of the procedure for you.

So, you should see something like:

Private Sub Form_BeforeUpdate(Cancel As Integer)


End Sub


And, then when you type in the one line of code, you will see:


Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.DateModified = Now()

End Sub


Also, you for your case, start using the dot "." in place of the bang "!"

Hence use:

Me.DateModified = Now()

Does the code compile now? after you type in the above line of code, then go
debug-compile.

You can then use the save button on the tool bar..and then close the code
editor.

You then should save your form, and then close it. now re-open the form
(regular, NON design)...and you can then test if the code runs. If you don't
modify any fields/data on the record you are viewing..then the datemodifed
field you have will not change. So, you have to either move to another
record, and move back, or close the form..and re-open it.

 

[HopyMSU]

Albert,

Thanks. Again, I tried this exactly as you described in existing and
new databases and still... nothing! Can't get it to record the date/
time in a specified control BeforeUpdate. I am about 95% sure I am
writing the code, compiling, and attempting to run it correctly.

Not sure what to do -- are there bits of info that would help one
troubleshoot this situation.

Thanks again,
Andy

When I have the code in VB (as seen above), NOTHING happens
and the date doesn't display in the DateModified control/field on the
form.

The modified date/time will not display in the control until you close the
form, or move to the NEXT record. the "before update" event ONLY fires if
you actually modify some other fields on the form. So, you actually will
only be typing in the one line of code in the code editor, but ms-access
will fill the surrounding code with the start/end of the procedure for you.

So, you should see something like:

Private Sub Form_BeforeUpdate(Cancel As Integer)

End Sub

And, then when you type in the one line of code, you will see:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.DateModified = Now()

End Sub

Also, you for your case, start using the dot "." in place of the bang "!"

Hence use:

Me.DateModified = Now()

Does the code compile now? after you type in the above line of code, then go
debug-compile.

You can then use the save button on the tool bar..and then close the code
editor.

You then should save your form, and then close it. now re-open the form
(regular, NON design)...and you can then test if the code runs. If you don't
modify any fields/data on the record you are viewing..then the datemodifed
field you have will not change. So, you have to either move to another
record, and move back, or close the form..and re-open it.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)

No, don't do the above..

4. In the Before Update field in the form's properties it says:

[Event Procedure]

Yes, the above is correct. To view, or modify the code, simply click on that
property, and then
you will see the [...] button appear. (a button with 3 dots inside will
appear when you click on the [Event Produce] text. However, you do NOT want
to modify the actual text that says [Event Procedure] -- if you do, then
your code will NOT run). So, it is this button you must click on to view, or
write code for a event.

 

[HopyMSU]

Figured it out... it was definitely an 'uh-duh' moment. Forgot to
enable VBA! Oops.

Thanks all for the help!

Andy