Tracking Changed records on the table level - NO FORM

[storm75m]

I've searched for an answer to this but I couldn't find the exact answer I need. Hopefully one of you geniuses can help me out...

I have a field in a table that I want to be able to capture the user id and date/time that a particular field has changed. My problem is that this field is on several forms and I don't want to have to add code to every single form that this field is on. I already know the functions to capture the userID and Date/Time, I just need to know where to put my code. I know in SQL you can create a trigger to do this, but is there such a thing in Access? Is there an AfterUpdate Event for a table/field?

 

[FancyPrairie]

I don't believe you can do it. But what you could do is to create a function (ie LogChange) that updates the user id and date/time the field was changed. In the afterupdate Property of each of the forms (or the afterupdate property of the field, whatever) add this: =LogChange()

 

[pullingteeth]

You could also extract the data update function to a class module, and make it public. Then from each of your forms, instead of replicating the data update, just call the function, e.g.:

if MyDataUpdateFunctions.updateMyTable(text32.value, text34.value, ...) > 0 then
...
end if

Take care of the data validation within updateMyTable, and return an error code depending on which element falls outside the validation rules. HTH

 

[storm75m]

Thanks for your help, I didn't think it was possible to do it the way I was thinking, but just thought I would give it a shot. I'm just being lazy, cause this table is used in several applications. I'll probably try Prairie's idea... thanks again.

 

[MichaelRed]

see faq181-291for as function which can be universally used to capture all changes made via forms. Personally, I don't bother with the independent capture of the user info, as it is the smaller part of the issue. If, in my world, you need to know that the uswer changed something, you better know what they changed as well.

Of course, you can skip the meat and just get the user info by abreviating the routine.






MichaelRed