Auto Update Date Field on any change in record

[GaeKettle]

Can anyone tell me if there is a way to make a date field update only when a change is made to any field in a record and not when the record is only viewed?

 

[Dugantrain]

If you're viewing the records via a form, you can write some code behind the form's AfterUpdate event.

 

[GaeKettle]

That's a bit advanced for me - can you be a little more specific. I get the general idea but don't know the terminology/code

 

[Dugantrain]

Well, first-off, are you looking at your records in a table/query or are you using a form (basically, a data-entry screen) to view records? What you're trying to do is impossible in a table (in Access anyway), but doable in a form.

 

[GaeKettle]

I am using a form for the data entry which is composed from data from three separate tables and several sub tables. I have set up a query on each table which compares the date of last entry with the date of current entry and fills a further field to tell me whether it has been updated or is new or has not been touched. I need this information for colour coding an Excel spreadsheet which is generated from the database each month. At the moment it relies on remembering to change the date field when I make an amend. I have then set up a query to reset the dates and empty the status field before I start the next month's entries.

 

[Andrew Fergus]

Hi, I'm a little confused. Are you looking for help with setting the status (i.e. updated, new or unchanged) field? Or is that bit under control? Alternatively, are you looking for help with setting the "date of last entry"?

Following on from the previous post about "After Update" and in the absence of specific information, following are some general notes about the "After Update" function. This won't provide the exact answer you are seeking (I don't have the form or variable names) but hopefully it will point you in the right direction.

You can get Access to perform a task (or series of tasks) after a record has been updated through a form by going into the form design, Click Edit -> Select Form, Click View -> Properties -> Event -> tab down to "After Update" and either select a macro (see below) or write some VB code to reset the date (assuming this is what you want to do). Rather than doing this at the form level (i.e. the "Select Form" bit) you can also (or alternatively) set the macro to run after a certain variable or field has been updated by assigning the macro to the specific item or field on the form (select the item, view properties, tab down to after update etc).

VB not being my strength, you can create a new macro that sets the value of the date on the form (for example "=Now()" - without the quotes) by using the "Setvalue" command - enter your variable name, prefixed with the form name, in the box titled "Item" and the value you want into the box titled "Expression".

Post another reply if you can't get it to work.

HTH, Andrew.

 

[GaeKettle]

Thanks Andrew, I think I can work something out from that info. I'll give it a go!

 

[SydneyGeek]

As Andrew mentioned, you can go to the After Update event of a control or the Before Update event of the record.
I'd recommend the latter, unless you are SURE that the particular control will always be edited.
So...
Form Properties, go to Before Update (this event fires every time a record is changed, an djust before it is saved).
Double-click the blank line and you'll see [Event Procedure]. Now click the builder (...) at the right of the line and you'll be in the code screen. In the blank line between the Sub and End Sub statements, type this:
[MyDateField] = Now() 'this will enter the current date and time
Change MyDateField to suit teh name of your field, but keep the square brackets.

Denis

 

[GaeKettle]

Spent a couple of hours trying it out and kept getting error messages - in the end it tuned out that we didn't need the = sign! Used date$() instead of Now(). All working perfectly. Thanks guys
GaeK