Auto Date/Time whenever a record changes

  • Thread starter Thread starter Hiking
  • Start date Start date
H

Hiking

Hello everyone,

I have created a date field and a time field in table tblRunningTasks.
I also have a form called frmRunningTasks.

I would like to have the two above-mentioned fields auto-populate
whenever a change is made to any of the other fields in form
frmRunningTasks.

I'm p'tty certain I go to IfDirty in the Events tab of the form
properties, and there I tried using expressions to point to the
tblRunningTasks table and to the Date field, and then saying that this
table/field is equal to Today(), but was getting error saying it could
not find the table (this was at work, I could get the details tomorrow).

Be that as it may, should I not be trying to do this with Expressions
and using Macros instead? Either way, through Expressions or through
Macros, can someone suggest the proper syntax, as I'm fairly certain the
source of my problems was incorrect syntax?

Thank you.


Richard
 
Could you combine the date and time field into one field named (say)
UpdatedOn?

You can then update the field through the BeforeUpdate event of the form:

1. Open the form in design view

2.Open the Properties box (View menu.)
Make sure the title of the Properites sheet reads "Form" so you are looking
at the properties of the form, not of a text box.

3. On the Event tab of the Properties box, set Before Update to:
[Event Procedure]

4. Click the Build button (...) beside this.
Access opens the code window.

5. Between the "Private Sub..." and "End Sub" lines, enter:
Me.[UpdatedOn] = Now()

If you really must use 2 separate fields, the code would be:
Me.UpdateDate = DateValue(Now())
Me.UpdateTime = TimeValue(Now())
 
Hello everyone,

I have created a date field and a time field in table tblRunningTasks.
Click to expand...

I'd really recommend using a single Date/Time field. Access stores
date and time data as a Double Float count of days and fractions of a
day (times); if you have separate fields you'll need extra code to
combine them.
I also have a form called frmRunningTasks.

I would like to have the two above-mentioned fields auto-populate
whenever a change is made to any of the other fields in form
frmRunningTasks.
Click to expand...

The simplest way is to put a textbox, txtWhenUpdated say, on the Form
(it can be invisible if you want) bound to the (single) date/time
field; use the Form's BeforeUpdate event with code like

Private Sub Form_BeforeUpdate()
<put any validation code here>
<if the record is OK to save then>
Me!txtWhenUpdated = Now
End Sub

Now() is a builtin function which returns the current date and time.

John W. Vinson[MVP]
 
Hiking said:
Be that as it may, should I not be trying to do this with Expressions
and using Macros instead?
Click to expand...

I think you should be trying to do this at the engine level.

Authorized users may not always use your form to connect to
the database (e.g. Excel, VBA, etc) so implementing the 'timestamp'
mechanism in only one front end application is unsafe (especially if
this is for audit purposes).

Consider a simplified example. Say you wanted to maintain a 'date
inserted' column for a table:

CREATE TABLE MyTable (
key_col INTEGER NOT NULL UNIQUE,
data_col INTEGER NOT NULL,
effective_date DATETIME DEFAULT NOW() NOT NULL
);

Obviously you don't want the effective_date column to be written to
directly e.g. a user could accidentally (or otherwise) enter a far
future date; let's also say this metadata need not be exposed either,
so you wrap it in a view/query:

CREATE VIEW MyFakedTable
AS
SELECT key_col, data_col
FROM MyTable;

Revoke all permissions from the table and grant them to the view/query.


Users can now insert into the view/query and the default 'timestamp'
gets automatically applied:

INSERT INTO MyFakedTable (key_col, data_col) VALUES (1, 1);

Of course, you are more likely to want a 'last modified' date i.e. the
effective_date column is additionally maintained for updates. For
Access, a stored proc/parameterized query is required e.g.

CREATE PROCEDURE SetMyTableData (
arg_key_col INTEGER,
arg_data_col INTEGER
) AS
UPDATE MyTable
SET data_col = arg_data_col,
effective_date = NOW()
WHERE key_col = arg_key_col;

Obviously you would now revoke update permissions from the view/query.

Jamie.

--
 
Thanks a lot for the help guys!

I had created two fields 'cause I thought it would be easier to program
something that would work (I'm fairly noob to VB programming). It's working
beautifully with the Date/Time stamp in the one field now.

I also didn't mention the sub-form, wanted to keep the question here simple
for starters. Now the problem I have is that if I enter/change data in the
sub-form, my date/time stamp doesn't happen.

But the prob may be resolved when I figure out how to implement
SomeDayWhen's suggestions.

I want to audit changes not so much for security reasons, but more to
re-find records that I would have modified within a certain time period.

I'll just keep hacking away with what you've given me for now and re-post
later with newest obstacles (there always seems to be some).

Thanks again all.
 
Thanks Jamie,

But I don't know enough about Access to even know where to start with this.
I can figure out the code and how to modify it, but not certain from where to
start this whole procedure. And yes, I do want to keep track of the
last-modified date.

I basically want to know the last time/date that any particular record was
changed.
 
I think you should be trying to do this at the engine level.
Click to expand...

That would be nice if he was using SQL/Server. Jet doesn't support
table triggers (yet).

John W. Vinson[MVP]
 
From what I've read now, looks like I can't do it Jamie's way, since I most
certainly am not using SQL Server.

Now, my main form's (frmRunningTasks) data source is tblRunningTasks, and
the subform's data source is tblIncidents (yes, all in the same database).
The method Allan Browne suggested is working just dandy whenever I modify any
data from the tblRunningTasks table, but if I modify anything in the subform,
I would like to have the timestamp field, located in the tblRunningTasks
table, to update here as well. I just don't know the proper syntax (probably
an Update function?).

I'll take suggestion along the lines of your code as well John... though I
haven't had the chance to try that code as yet, I am going to, and if it
works as well/the same as the code I have now, I'll prob'ly adopt the latter
code for its possible extra versatility.

How do I now get this to work from the subform, time-stamping to the same
field (contained within the main form)?

Thanks again.
 
Now, my main form's (frmRunningTasks) data source is tblRunningTasks, and
the subform's data source is tblIncidents (yes, all in the same database).
The method Allan Browne suggested is working just dandy whenever I modify any
data from the tblRunningTasks table, but if I modify anything in the subform,
I would like to have the timestamp field, located in the tblRunningTasks
table, to update here as well. I just don't know the proper syntax (probably
an Update function?).
Click to expand...

You can "cheat" a bit: in the AfterUpdate event of the Subform, put
code like

Private Sub Form_AfterUpdate()
Parent!txtWhenModified = Now
End Sub


Or - maybe better - keep a WhenModified field in each table, and
record when THAT table was modified. Modifying a record in
tblIncidents doesn't really change any data in tblRunningTasks, and -
to my mind - it would not be valid to record that the data in
tblRunningTasks was changed on such and such a date, when it *WASN'T*
- the data in a *different* table was changed then.

John W. Vinson[MVP]
 
John said:
That would be nice if he was using SQL/Server. Jet doesn't support
table triggers (yet).
Click to expand...

Triggers are only one way of achieving the OP's objective.

The absence of triggers from Access/Jet is no excuse for not
implementing business logic at the data engine level. The example here,
a 'last modified' date, is metadata and it seems to me its maintenance
should be the responsibility of the database rather than the front end.

The approach for the 'last modified' date I detailed above uses a VIEW
(which Jet does support) and SQL PROCEDURESs (which Jet does support),
with judicious use of permissions, to ensure the date is properly
maintained, all at the engine level (SQL keywords in uppcase).

The majority of my SQL Server triggers are integrity constraints that
could be implemented in Jet as table-level CHECK constraints
(functionality that Jet supports and SQL Server does not) or CASCADE in
declarative referential integrity (functionality in which Jet is
superior when compared to SQL Server).

Triggers in Jet/Access would be great, though. I particularly
appreciate SQL Server's INSTEAD OF triggers, which can be used to
facilitate modifications to a VIEW that would otherwise be
un-updatable. I think, though, a prerequisite for Jet would be SQL
procedures (note lowercase) that can execute multiple SQL statements,
using transactions, etc.

Despite my scepticism, the Access 2007 team seems to have fixed some
Jet 4.0 bugs so maybe there is hope yet...

Jamie.

--
 
Hello again John,

I basically have the following code inserted in my main form
(frmRunningTasks):


Private Sub Form_Dirty(Cancel As Integer)

Me!AutoRTasksLastModDate = Now()

End Sub

Private Sub subTask_History_Exit(Cancel As Integer)

Me!AutoIncidentsLastModDate = Now()

End Sub


These work fine, if I modify anything from within the main form, the
AutoRTasksLastModDate field is updated, and if I modify data in the subform,
the AutoIncidentsLastModeDate field is updated as well with the Now()
date/time stamp.

This date/time stamping is working splendid as long as I'm working from
within the Form view, but the only problem I'm having now is that I often
want to do data entry/modification from within the Datasheet view. In
Datasheet view, I have a "plus/minus" column at the very left, onto which I
click to open and close the sub-form view. The:

Private Sub subTask_History_Exit(Cancel As Integer)

Me!AutoIncidentsLastModDate = Now()

End Sub

code is causing problems... I am able to click on the plus sign to open the
sub-form, and click again to close it, but if I open the sub-form and make
any change to the data in the sub-form, I am now unable to close the
sub-form. I can change from Datasheet view to Form view, and then change
back again to Datasheet view in order to close the subform, but then this is
not an ideal solution, and this way I also lose position within the datasheet
view, it brings me back to the first record.

What can I do so's I can open/close the sub-form from within Datasheet view
even when changing data?

Any suggestions appreciated. Thanks.
 
Back
Top