Highlighting when a particular field has changed

I know that you can easily have a Last Updated field that can record the date a change was made to a particular record.

But is it possible to record when a change is made to a particular field?

posible, but not that simple.

you would need a new field to record when the specific field is updated.

then add a lookup recordset to look up the value that is being passed for that filed on the update. if the lookup recordset count returns 1, then the value is not changed, don't change the date for the tracking field. if the look up recordset count returns 0, the value has changed, so update the date in the tracking field to todays date.

if you need help setting this up, we could help through a premiere support appointment:
http://www.webassist.com/premier_request.php

Thanks Jason - it is a bit of a pain, but basically have a client wanting to know when changes were made, and who made them, on a per field basis. So if there are 20 fields, by the sounds of it there would need to be two extra fields (date and username) for each to record that.

I hate stuff like this where a client insists its something that must be easy to do, because the database must somehow log changes.

yeah, you would need the 2 extra fields for each column, and a lookup recordset for each also.

Would there be a way of doing it with an additional table to somehow log the changes? The extra fields might be completely impractical if it needs two extra fields plus a recordset for each column?

yes, you could create a spirit table with a column to record the name of the field changed, and the date, but it still needs a recordset to determine if changed.

OK - so for example if I have a table:

Lodges

LodgeID
Lodge_Name
Lodge_Summary
Lodge_Details

I'd need another related table:

Lodge_Updates

UpdateID
LodgeID
Field_Updated
Date_Updated
Updated_By

And the way it would work is that when the update button is pressed it calls a recordset which somehow compares the new value of each field with the old value, and if its not the same inserts a record in the Lodge_Updates table?

Yeah, that sounds about right.

Thanks Jason - this is definitely something that I think will need to get done if possible. I'm going to have a look at SQL triggers, but its new to me, so its quite likely I'll come back to this as some paid support.