Date and time

[Guest]

How do I set up an automatic date and time field in Access which updates upon
changes to the record?

 

[Douglas J. Steele]

You can't. The only way you can get that capability is to put logic into the
form's BeforeUpdate event to change the date/time field.

 

[John Vinson]

On Sun, 6 Feb 2005 17:23:02 -0800, "Yasas de Silva" <Yasas de

How do I set up an automatic date and time field in Access which updates upon
changes to the record?

You'll need to do all changes to the data using a Form. Table
datasheets have no usable events.

Given this, just add a date/time field to the table, let's call it
ChangeTimestamp, and use VBA code in the Form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel as Integer)
<any record validation code here>
Me!ChangeTimestamp = Now
End Sub

John W. Vinson[MVP]

 

[onedaywhen]

You'll need to do all changes to the data using a Form. Table
datasheets have no usable events.

Given this, just add a date/time field to the table, let's call it
ChangeTimestamp, and use VBA code in the Form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel as Integer)
<any record validation code here>
Me!ChangeTimestamp = Now
End Sub

Isn't the assumption that all changes to the data *will* be done 'using
a Form' wishful thinking? The front end shouldn't be used in place of
database constraints. How about a CHECK constraint to ensure the date
amended is always current? e.g.

CREATE TABLE TEST (
key_col INT NOT NULL PRIMARY KEY,
data_col VARCHAR(255) NOT NULL,
amended_date DATETIME DEFAULT NOW() NOT NULL,
CHECK (amended_date = NOW()))
;

I did wonder if this should have a bit of tolerance e.g.

CHECK (TIMESERIAL(0,0,2) > CDATE(ABS(amended_date - NOW())))

Any thoughts?

Jamie.

--

 

[John Vinson]

Isn't the assumption that all changes to the data *will* be done 'using
a Form' wishful thinking? The front end shouldn't be used in place of
database constraints. How about a CHECK constraint to ensure the date
amended is always current? e.g.

That would be nice if Access supported CHECK constraints, Jamie.

Sorry, we're not using SQL/Server here. This is a different program
with different syntax. Triggers would be nice too.

John W. Vinson[MVP]

 

[onedaywhen]

That would be nice if Access supported CHECK constraints, Jamie.

Sorry, we're not using SQL/Server here. This is a different program
with different syntax. Triggers would be nice too.

Man, are you in for a shock <g>. OK, deep breath...

You know the native underlying data engine to the MS Access UI is Jet?
Since Access2000, this has been Jet 4.0. Well, Jet 4.0 supports CHECK
constraints. It took until Acces2003 for the UI to catch up with the
data engine: Access2003 may be put into the ironically titled 'ANSI
mode' to allow Jet 4.0 syntax to be natively used in a SQL window. In
previous versions of MS Access, one had to use the OLE DB provider.
AFAIK, the only way to do this in the UI was to use ...

.... ADO. Yes, the dreaded word. I guess MS Access 'power' users will
always prefer DAO and I'll never know why [aside: Matt Curland accuses
DAO of tainting VBA's reputation, in Advanced VB6 p110: "Data objects,
such as DAO, provide another example of poor teardown behavior. DAO has
Close methods that must be called in the correct order, and the objects
must be released in the correct order as well (Recordset before
Database, for example). This single poor object model behavior has led
to the misconception that VB leaks memory unless you explicitly set all
the local variables to nothing at the end of a function."]. Let's face
facts: if there are some useful features that can only be accessed via
an ADO connection, surely it's not too much trouble to navigate to the
Visual Basic Editor, set a reference to ADO and type in the Immediate
Window:

CurrentProject.Connection.Execute _
"ALTER TABLE Orders ADD CONSTRAINT freight_is_positive" & _
" CHECK (Freight > 0);"

Of course, one must be aware of the features introduced five years ago
to be able to use them. I suggest this MSDN article as a starting
point:

Description of the new features that are included in Microsoft Jet 4.0
http://support.microsoft.com/default.aspx?scid=kb;en-us;275561

But don't bother looking for triggers, you won't find them <g>.

Jamie.

--