You can create a pseudo transaction log that will work for Access databases. I have created a procedure that should work for any form. I stick the function TransactionLog into a module or class module, then call it from the form's before update event. This will track all changes, sending the data to a log called tblLog.
In order to make this work you really need to create Access security so that you can reference the CurrentUser function that will obviously give you the logon of the current user.
If you need any further assistance, please feel free to ask.
Included in tblLog is the Primary Key value, old value of control changed, new value of control changed, user changing the record and a time stamp (default value of now()) that records the exact date/time the record was changed.
If you are using more than text box fields on the form you will need to reference that in the code when you loop through all the form's controls.
In the code below I am calling the transaction log function and plugging in two parameters, (me or current form, and "AutoID" which is the primary key of that form's table recordset)
Private Sub Form_BeforeUpdate(Cancel As Integer)
Call TransactionLog(Me, "AutoID")
End Sub
Public Function TransactionLog(ByVal frm As Form, ByVal strPrimaryKey As String) As Boolean
'Setup error handling
On Error GoTo TransactionLog_Err
'Declare DAO database objects
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim ctl As Control
Dim strSQL As String
'Instantiate DAO database objects
Set db = CurrentDb
Set rst = Me.RecordsetClone
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.OldValue <> ctl.Value Then
'Create dynamic query to update transaction log with changes
strSQL = "INSERT INTO tblLog (AutoID, OldValue, NewValue,User) " & _
"VALUES (" & rst(strPrimaryKey) & ",'" & ctl.OldValue & "','" & ctl.Value & "',CurrentUser());"
'Use a transaction in case you need to rollback on error
DBEngine.BeginTrans
'Update the transaction log
db.Execute strSQL
'All is well, update the database
DBEngine.CommitTrans
End If
End If
Next ctl
TransactionLog_Exit:
'Dereference DAO Objects
Set rst = Nothing
Set db = Nothing
Exit Function
TransactionLog_Err:
DBEngine.Rollback
MsgBox Err.Number & vbCrLf & "An error has occurred, rollback changes"
Resume TransactionLog_Exit
End Function