# Access 2007 Audit Log - Change tracking



## mgatof (May 22, 2012)

I am trying to create an audit log on an access database to track changes to records on a form. Whenever a change is made I would like access to record in a separate table what the value was before and what it was after for any field in the form. Could you help? I really appreciate your time.


----------



## AlbertMC2 (Jul 15, 2010)

Hi

See these 2 sites:
How to create an audit trail of record changes in a form in Access 2000

and

Access/VBA Tutorials - Creating an Audit Trail


----------



## mgatof (May 22, 2012)

the first link is exactly what I need, but it does not seem to work. I have tried using similar code from others websites but nothing seems to work. Is there a setting that needs to be changed? Also will the first link work in 2007? Thank you for your help.

Max


----------



## AlbertMC2 (Jul 15, 2010)

Yes it should work in 2007 though I haven't tried. Will see if I can try later.

When you say that it does not work what does it do, or not do?


----------



## AlbertMC2 (Jul 15, 2010)

Hi

I have just tried it in 2007 and it does work.
Attached is the test database I created.
The database puts the Audit Trail's update memo field on the form so obviously you just have to create a new table and rather save this field in that table.

If you need help with that please let me know.


----------



## mgatof (May 22, 2012)

My last question is how do I: 

1. make it so it does not record every blank cell even when I do not change them

2. have it record before and after values (if this is more difficult I do not really need this functionality

Thank you again for your help.

Max


----------



## mgatof (May 22, 2012)

I would also like to include changes to number fields in the log


----------



## AlbertMC2 (Jul 15, 2010)

Hi

What do you mean by:
1. make it so it does not record every blank cell even when I do not change them
and
2.I would also like to include changes to number fields in the log

To have it record before and after values try using the following code by swapping the section of code on the MS website (link I gave above) with the following code that corresponds to the same section:

```
' If control had previous value, record previous value.
ElseIf IIf(IsNull(C.Value), "", C.Value) <> C.OldValue Then
    MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
                C.Name & "==previous value==" & C.OldValue & " ==New Value==" & C.Value
End If
```
Note this code checks and records the following controls:
TextBoxes, ComboBoxes, ListBoxes and OptionGroups

It only records fields that are changed. 
It also depends on how your "number field" is displayed on the form. Usually it is a text or combobox which should be recorded then.


----------



## mgatof (May 22, 2012)

right now even if i do not change a field, but it is blank, it gets recorded as previous cell value was blank


----------



## AlbertMC2 (Jul 15, 2010)

Hi

Try this (once again only swapping the corresponding section):

```
' Skip Updates field.
            If C.Name <> "Updates" Then
    
            ' If control was previously Null, record "previous
            ' value was blank."
                If (IsNull(C.OldValue) Or C.OldValue = "") Then
                    If Not (IsNull(C.Value) Or C.Value = "") Then
                        MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
                        C.Name & "==previous value was blank==New Value==" & C.Value
                    End If
                ' If control had previous value, record previous value.
                ElseIf IIf(IsNull(C.Value), "", C.Value) <> C.OldValue Then
                    MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
                    C.Name & "==previous value==" & C.OldValue & " ==New Value==" & C.Value
                End If
            End If
```


----------



## mgatof (May 22, 2012)

one last question, I would also like to run a macro, 
"Last Modified" on beforeupdate as well but there is only space for =AuditTrail() how would I do this


----------



## AlbertMC2 (Jul 15, 2010)

I don't know if it will work but maybe at the end of the audit trail function use a 
DoCmd.RunMacro

Or run the macro on the BeforeUpdate event and then at the end of the macro run the function Audittrail


----------

