At record save, scan through and compare old values to new

[twgonder]

I may have asked something similar before, for a different reason, but I can't seem to find it, at least for doing like this:

As a record in a form is saved, I would like to loop through all the SOURCE fields (not the controls)
to find fields that have changed from when the record was first loaded.



How can I loop through all the source fields?
Is there some way to get the old value with some Access property or am I better to make a "clone" of the source record
when it's first loaded, to get around other problems that might pop up?

Then I need to find the values that changed, and based on this I'll be creating change log records.

[June7]

Data controls have an OldValue property which you can use to compare to Value property.

If you want an audit trail log, review http://allenbrowne.com/AppAudit.html

[twgonder]

It looks to have lost one of my replies again:

Anyways, thanks June7 for the link, I'll check it out.
The problem with using controls, there are some record fields that change without having a control on the form.

[June7]

Fields don't have OldValue property. What causes field data to change if not by user edit in control? Must be some code. Maybe have that code compare current field value to the new data before field is changed and act appropriately. Otherwise, need a control even if it is hidden from users.

[twgonder]

I saw this in June7's link, at the top:
(Note: Access 2010 contains Data Access Macros (effectively triggers), — a better way to create an audit trail if you use the new database format.)

Does anyone know where and how this works, or a writeup?

Note: I found this, but I'm not up on Marcos and I'm not sure this is what I would want, since I want to log all changes more efficiently, but I'll poke at it and see if I can get VBA to work with this. But I'm still not sure of the of the syntax for the basic loop to check all fields.

After Update macro event | Microsoft Learn

[June7]

I've never used any macros but I expect you could have appropriate DataMacro call the appropriate VBA function.

I am guessing you could have a If Updated("some field name") Then for every field.

[twgonder]

@June7 post# 4

Yes, code is changing non control fields in the record.

I think that an old post stated, as you did, that there isn't an oldvalue for fields. Confirmed with this:

Attachment 50596

So, with that, maybe there is a way to "clone" the record in the Form_Current procedure?

[June7]

Yes, can make copy of record into another table or open a recordset filtered to specific record when form opens. Still don't see how that helps compare change of non-control fields. Unless revised data is committed to table for comparison with temp table or recordset, otherwise what would you compare with?

[Welshgasman]

Would recordsetclone be useful?
I have seen where people walk a recordsetclone yet use the form controls and wonder why something is not being updated in the form?

[June7]

I just did a test. Opened recordset and print field and textbox values, edit record on form and commit to table, print values again. The recordset value is change. Same result with RecordsetClone. Whatever comparison you want to do has to be before record is committed.

Sub test()
Dim rs As DAO.Recordset
DoCmd.OpenForm "Holidays"
Set rs = Forms!Holidays.RecordsetClone 
'Set rs = CurrentDb.OpenRecordset("Holidays")
'Set rs = Forms!Holidays.Recordset
Forms!Holidays.tbxHolName = "test"
Debug.Print rs(1)
Debug.Print Forms!Holidays!HolName
Debug.Print Forms!Holidays.tbxHolName
DoCmd.RunCommand acCmdSaveRecord
Debug.Print rs(1)
Debug.Print Forms!Holidays!HolName
Debug.Print Forms!Holidays.tbxHolName
End Sub

[CJ_London]

Depends on why you want to know and what you want to do with the information but consider creating an ADO disconnected recordset from your form recordset on load. Then compare the two again on before update.

if it is to create an audit log, consider a data macro

[orange]

Some info on data macros here. Sample on page 2.