There are situations where you want to be able to track changes to records in a multi-user database. This page contains articles covering two options: storing when a record was changed and by whom; and storing the old value of a field before it changed. The first option only keeps a record of the last change, and won't tell you what was changed on the form. The second will give you an audit trail, and you can track the full history of changes for any field.
If you are only interested in knowing who created the record, and who changed it, you can do it by adding four fields to the table that underlies the form (and, if the form is based on a query, adding the new fields to the query). The fields are:
|CreateDate||DateTime||Default value =Now()|
To use the fields, you need to add code to the Current and BeforeUpdate events of the form, as shown below:
Private Sub Form_BeforeUpdate(Cancel As Integer) ModBy = Environ("username") ModDate = Now() End Sub Private Sub Form_Current() If Me.NewRecord Then CreateBy = Environ("username") End If End Sub
When you go to a record, the Current event fires. You can check to see if it is a new record and, if so, write the current logged-in user's name to the CreateBy field. CreateDate is filled automatically by setting its default value.
When you save a record, one of the events that fires is BeforeUpdate. This is just before the record is actually saved, so you can write the current user's name and the timestamp to the two Mod fields.
Although this is simple to set up, you need to add the fields to every table / query whose changes you want to track. The second method takes a bit more explaining but centralises all of the logging to a custom table, and allows you to build reports covering the history.
Let's assume that you want to keep a full history of changes to a field, in case you want to restore a value at a later date. This situation requires that you keep multiple records (one for each change that you track) and because of that, it makes sense to create a custom table for the purpose. In this example the log table will be called ztblDataChanges, and it will have these fields:
|RecordID||Number (Long Integer)|
|TimeStamp||DateTime||Default value =Now()|
Most of the fields are text, including the OldValue and NewValue fields. The reason for this is that text fields will accept numeric data, but number fields will not accept text. You can always convert back to the correct data type when you restore the value. The exception is the RecordID, and I have made that a Number because all of my tables use AutoNumber primary keys. If you use any text-based primary keys, make this field text too.
The code uses a custom function which needs to be put into a standard code module. The code is shown below:
Function LogChanges(lngID As Long, Optional strField As String = "") Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim varOld As Variant Dim varNew As Variant Dim strFormName As String Dim strControlName As String varOld = Screen.ActiveControl.OldValue varNew = Screen.ActiveControl.Value strFormName = Screen.ActiveForm.Name strControlName = Screen.ActiveControl.Name Set dbs = CurrentDb() Set rst = dbs.TableDefs("ztblDataChanges").OpenRecordset With rst .AddNew !FormName = strFormName !ControlName = strControlName If strField = "" Then !FieldName = strControlName Else !FieldName = strField End If !RecordID = lngID !UserName = Environ("username") If Not IsNull(varOld) Then !OldValue = CStr(varOld) End If !NewValue = CStr(varNew) .Update End With 'clean up rst.Close Set rst = Nothing dbs.Close Set dbs = Nothing End Function
Of the seven fields that we populate in ztblDataChanges, only one has to be directly passed to the function; the ID or the current record. Everything else is retrieved automatically using Screen.ActiveControl, Screen.ActiveForm, or the Environ function. There is an option to provide the field name where it doesn't match the name of the control.
When populating the fields, there are two If statements to handle exceptions. The first one checks to see whether the second parameter has been provided. If not, the code assumes that the control and field names are the same (usually the case if you add a bound control to a form), and uses the control name to populate the field name. If not, the user-provided field name is used.
If strField = "" Then !FieldName = strControlName Else !FieldName = strField End If
The second checks to see whether an OldValue existed. If not (eg. for a new record, or for an existing record where a field is being populated for the first time), this field is skipped to avoid errors.
If Not IsNull(varOld) Then !OldValue = CStr(varOld) End If
To call the function use either of the following syntax options. Assuming that the form's key field is CustomerID, and the field and control names match, use this syntax:
Private Sub Address1_BeforeUpdate(Cancel As Integer) Call LogChanges(CustomerID) End Sub
If the field and control names do not match, use this instead:
Private Sub txtAddress1_BeforeUpdate(Cancel As Integer) Call LogChanges(CustomerID, "Address1") End Sub
Without going overboard you can track changes to critical fields on any form in your database. The information stored in the table can be used to report on a range of things such as:
Another advantage of this method is that, once the table is created and the code has been placed in a module, you don't have to make structural changes to any other tables in order to track usage.