Auto-update date field

[Pharma Down]

Is it possible to automatically update a date field in a form record without ever clicking in to that date field, but only because you have altered something else on that individual form record or an embedded, linked subform record?
eg1. Staff details record - a) create a record for Bob Smith and 'Last Updated' field automatically inserts date b) amend record to say 'Robert Smith' and 'Last Updated' field changes to show new date, even though you never touched that date field.
eg2. Risk register - main form record contains name, details, and 'Last Updated'; embedded subform allows you to add individual records of actions taken for each risk. I want the 'Last Updated' field to update each time I add an action, without amending the 'parent' risk record.

Thanks

 

[pbaldy]

Sure, in either a control or the form's after update event:

Me.TextboxName = Date()

 

[Pharma Down]

Sorry - I'm a pharmacist... I dabble with Access enough to create something that works by fluke rather than though skill or knowledge!

If I click on the date field in the form in design view, with the Properties box open, where do I type what you have written... and do I need to update it in the table as well?

 

[pbaldy]

It would go here:

http://www.baldyweb.com/FirstVBA.htm

As long as the textbox is bound to the field in the table, it will save automatically.

 

[Pharma Down]

Sorry... again. I obviously have my stupid hat on.
So, I have a form that has many fields, including a date field. I want to type in any field except for the date field but have the date field update automatically because I typed in one of the other fields. Lets say there are four fields, called 'txtbox1', 'txtbox2', 'picklist1' and 'lastupdate'; following your instructions above, would I add the code to the After Update bit for 'lastupdate' or all the other fields, and would I use Me.lastupdate = Date() or the other field names?

To make things more complicated, could a date field on a form be updated by entering data only on a subform?

 

[pbaldy]

Your code is fine, as it's the lastupdate field you want to set. I'd use the form's after update event, as that will catch changes to any field (otherwise it would be the other 3 fields).

 

[Pharma Down]

That's brilliant! Thank you.
Would it still work for the subform, or would I need to enter code in the 'After Update' event for the subform which read something like: mainform.lastupdate = Date() ?

Cheers

 

[pbaldy]

You'd need similar code in the subform's after update event. Here's how to refer to the main form:

http://www.mvps.org/access/forms/frm0031.htm

 

[Pharma Down]

Wow. Thank you all!
I built the database in Access 2003, so I'll assume that the comments about 'older versions' applies here!

Cheers

Andy