How to loop through & get changed fields?

[tigersden]

Hi,
I have set up an audit table based on Allen Brownes example. How can can loop through this table & compare each field & return the differnces between them. There are 12 fields which could contain different data. So I would need to compare each of these based on the RiskRef field which identifies the master record.
I hope this makes sense.
Thanks in advance.
Tim

 

[Trevil]

I was unable to find 'Allen Brown''s example in this forum, or via Google, so I don't know what you are citing.

Assuming you have an 'audit' table that mirrors some other table, and the audit table only contains records that differ by one or more fields, you could use a query to look for differences. i.e.

SELECT tblAudit.OwnerKey, tblAudit.SomeData
FROM tblAudit INNER JOIN tblMember ON tblAudit.Key1 = tblMember.Key1
WHERE (((tblAudit.OwnerKey)<>[tblMember]![OwnerKey])) OR (((tblAudit.SomeData)<>[tblMember]![SomeData]));

Or you could do this through code if one or more fields prevent comparison through the query (or could be null, etc.)



Code: Where the vision is often rudely introduced to reality!

 

[tigersden]

Hi Trevil,
Thanks for replying here is the link to the code

http://allenbrowne.com/AppAudit.html

Basically you duplicate the table to be audited & the whole record is saved twice before edited & after edited. Now each record could have multiple entries in the audited table.
What I was hoping to do was build a report based on the master records primary key then with this list in chronological order all the changes for that record but only for the fields which have been edited. Ineach of audit records.
Thanks for looking
Tim

 

[Trevil]

Hello Tim,
I took a brief look at the code, and it is similar in functionality to what I have seen before. Regarding the report you want, if you thought you have enough width on a page to show the 12 fields, one option would be to have 12 columns, but only show the data in a column if it differs from the data in the prior record.

It appears you can get all the data you need from the audit table. The only trick is you would only want the first 'before image' and not any subsequent ones. The report fields could use the 'hide duplicates' setting to only show changes.

Is this kinda sorta what you were thinking?
Wayne

Code: Where the vision is often rudely introduced to reality!

 

[tigersden]

Hi Trevil,
Thaks for the tips.
From what you have explained it is exactly what I was hoping to do, before I got totally lost!
Thanks for you time.
Tim