Tracking change and forms

[Grek]

Hi,

I'm using the code from this guy to track the updates made on each field of my database (I want to know who changed what and when)
Seems to be working great. My databse is attached.

The table tbl_ChangeTracker has the below fields:
ID
FormName
MyTable
MyField
MyKey (--> this one seems to be the UniqueID of my main table)
ChangedOn
FieldName
FieldOldValue
FieldNewValue


UserChanged
CompChanged

After a few updates made for testing, the table looks like this:

IDFormNameMyTableMyFieldMyKeyChangedOnFieldNameField_OldValueField_NewValueUserChangedCompChanged1F_New_Non_Hedged_Share_ClassT_NewLaunchesUniqueID401-Nov-11Master Fund NameFunds of client 1Fundsvc of client 1gregLUX-P1232F_New_Non_Hedged_Share_ClassT_NewLaunchesUniqueID401-Nov-11Share Class NamecvxgregLUX-P1233F_New_Non_Hedged_Share_ClassT_NewLaunchesUniqueID401-Nov-11Share Class BasiscgregLUX-P123

Question: In a form, what would be the easiest way to reflect next to each field who made the latest update and when?

For Instance, I have a field called "Form X to be sent" which is a text box field. When I tick the checkbox once the task is done, I'd like to see a text appearing next to it saying "Task performed by UserChanged on ChangedOn"

Was thinking of a text box whith some kind of formula saying "Look for Field MyKey in tbl_ChangeTracker and return values for UserChanged and ChangedOn where FieldName = mycheckboxname

Issue 1: I'm not sure how to build that kind of query with the access expression builder

Issue 2: I think that it would not work since I could have 10 lines of changes for the same field. Field MyKey would still reflect my unique identifier but the changes would be listed using different ID numbers in tbl_ChangeTracker. So to solve this would need my query to look for the most recent update only --> additional difficulty

Any thoughts on that?

Many thanks for you help

Greg

[alansidman]

Your file can only be opened by those forum users with Access 2010. Suggest you post it as a 2003 or earlier version if you want maximum users to look at it.





Alan

[Grek]

Hi Alan,
How can I do that? Looks like access 2010 doesn't give me the choice of the format when I click "Save database as..."
Thanks,

[alansidman]

Hopefully, someone will post in. I can't help you as I only have 2007 and earlier. Try to google Access 2010 Save as Earlier version. Here is the first item I found
http://office.microsoft.com/en-us/ac...010341553.aspx

[Grek]

Thanks for the link.
I did some research but unfortunately when trying to save the file under an earlier version of access I get a msg saying that I can't because of some specific feature of my database :/