Auto Date/Time whenever a record changes

[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

 

[Allen Browne]

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())

 

[John Vinson]

Hello everyone,

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

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.

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]

 

[Richard]

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.

 

[Richard]

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.

 

[John Vinson]

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

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

John W. Vinson[MVP]

 

[Richard]

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.

 

[John Vinson]

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?).

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]

 

[Richard]

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.