Last Modified Date in ACCESS

[luvbite38]

Hi,

I have a table and a field called Last Modified Date. I want somehow to automatically update the if value changes in that row.

Example

ID Fruits Last Modified Date
1 Apple 17/10/2010
2 Apple 17/10/2010
3 Apple 17/10/2010
4 Apple 17/10/2010
5 Apple 17/10/2010
6 Apple 17/10/2010

If someone makes some change Apple to Banana in second row on 18/10/2010 and changes Apple to Pineapple to 5th entry on 19/10/2010, the last modified Date changes should automatically change to 18/10/2010 against ID 2 and 19/10/2010 against 5th ID and Table should look like as follow

ID Fruits Last Modified Date
1 Apple 17/10/2010
2 Banana 18/10/2010
3 Apple 17/10/2010
4 Apple 17/10/2010
5 Pineapple 19/10/2010
6 Apple 17/10/2010

I am using Access 2010 and the changes will NOT be made on form. User will go into table to change anything.

Any suggestions pls..... I dunt knw if I need an event which triggers if value changes, update the date to NOW()) or something.

Thanks in advance.

Kindest ever Regards,

 

[Joe4]

I am using Access 2010 and the changes will NOT be made on form. User will go into table to change anything.

This is generally a very bad idea. It is generally considered bad database design to have users update the underlying tables directly. There is too much potential for bad things to happen. Well designed databases are usually Form driven, where you can control exactly what the users can see and do to your data.

Using Forms, you can place Event Triggers that automatically run VBA code when the users update something, like you want. I do not know a way to do that if they update the tables directly. I do not even know if it is possible. Even if it is, it probably isn't nearly as easy as it to do using Forms.

 

[SydneyGeek]

I agree with Joe. Build forms for your interface.
However, you can create a Data Macro in 2010 that will update a field when the record changes.
Advantages: It will run wherever that data is updated (form, query, table)
Disadvantage: Not compatible with any earlier version.

Denis

 

[Joe4]

However, you can create a Data Macro in 2010 that will update a field when the record changes.

That's pretty cool new feature!
I haven't seen much of Access 2010 yet.

 

[luvbite38]

Thanks a million for your feedback.

I can completely understand where you coming from. Actually I am updating someone else's database and she asked me to stick to this format.... Do you by any chance know how to write the macro in 2010 to do the job for me pls.......

All your feedback is much much much appreciated.

 

[SydneyGeek]

I haven't used them personally -- I do all my development on 2007.
However, look at these links:
http://office.microsoft.com/en-us/access-help/create-a-data-macro-HA010378170.aspx
http://msdn.microsoft.com/en-us/library/ff973807.aspx

Denis

 

[luvbite38]

Thank you SydneyGeek...