I have searched for quite some time how to track changes in a database and i could not find what i am really looking for.
I have a database that has 52 tables,some of them are linked each other others not.so i would like to track any changes made to any table.
i have managed to do it for one table but it's not what i am looking for.
What i did,is to create a form based on one table(in my case it's status) and a table named Audit(this one will contain the changes made in the table which the form is based on)
on the Before Update event of the from i pasted this code
Code of TrackChanges function
This code works really good and i get all the changes in the Audit table
My problem is ,Is there a way to trackchanges made in all tables without making 52 forms??
Thank you for your help
I have a database that has 52 tables,some of them are linked each other others not.so i would like to track any changes made to any table.
i have managed to do it for one table but it's not what i am looking for.
What i did,is to create a form based on one table(in my case it's status) and a table named Audit(this one will contain the changes made in the table which the form is based on)
on the Before Update event of the from i pasted this code
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Call TrackChanges(Me, StatusNumber)
End Sub
Code of TrackChanges function
Code:
Sub TrackChanges(frm As Form, recordid As Control)
'Track changes to data.
'recordid identifies the primary key field's corresponding
'control in frm, in order to identify the record.
Dim cDQ As String
cDQ = """"
Dim ctl As Control
Dim varBefore As Variant
Dim varAfter As Variant
Dim strControlName As String
Dim strSQL As String
On Error GoTo ErrHandler
'Get changed values.
For Each ctl In frm.Controls
With ctl
If .ControlType = acTextBox Then
If .Value <> .OldValue Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name
'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "Audit (EditDate, User, RecordID, SourceTable, " _
& " SourceField, BeforeValue, AfterValue) " _
& "VALUES (Now()," _
& cDQ & Environ("username") & cDQ & ", " _
& cDQ & recordid.Value & cDQ & ", " _
& cDQ & frm.RecordSource & cDQ & ", " _
& cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ & ")"
'View evaluated statement in Immediate window.
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If
End If
End With
Next
Set ctl = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description & vbNewLine _
& Err.Number, vbOKOnly, "Error"
End Sub
My problem is ,Is there a way to trackchanges made in all tables without making 52 forms??

Thank you for your help