[AccessD] Audit Record Changes

On 25 Feb 2003 at 15:29, gregg steinbrenner wrote:

> Every time someone makes a change I keep a copy of the old record in a clone
> table.  An example table would look as follows
> 
> EmpID Name		Address  		Salary  	DateChanged
> 102	Gregg		123 Somewhere	50000		1/1/03
> 102	Gregg		111 Elsewhere	50000		2/2/03
> 102	Greggs	111 Elsewhere 	55000		3/3/03
> 
> I would like to create a query (if possible) or code that would create a
> second table that shows me what field, changed from what, to what, on what
> day as follows...
> 
> Field		Old Value		New Value		DateChanged
> Address	123 Somewhere	111 Elsewhere	2/2/03
> Name		Gregg			Greggs		3/3/03
> Salary	50000			55000			3/3/03
> 
> Any ideas?
> 
> Is it possible to do this without listing every field name in the code?
> 
> Humbly yours,
> 

Something like:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
Dim changed As String
For Each ctl In Me.Controls
  ' only check the text boxes and combo boxes on the form
  If Left(ctl.Name, 3) = "txt"  or Left(ctl.Name, 3) =  "cbo" Then
    If ctl.OldValue <> ctl.Value Then 
     call logit(mid$(ctl.Name,4),ctl.OldValue,ctl.Value)
    End If
  End If
Next
End Sub

Sub logit(ControllName As string, OldValue as String, NewValue as String)
Dim strSQL As String
strSQL = "Insert into Audit (FieldChanged,OldValue,NewValue,CDate)"
strSQL = strSQL & "  select '" & FieldChanged & "','" & OldValue & "','"
strSQL = strSQL & NewValue & "',Date()"
DoCmd.SetWarnings False
DoCmd.RunSQL sqlstring
DoCmd.SetWarnings True
End Sub




-- 
Stuart McLachlan
Lexacorp Ltd
Application Development,  IT Consultancy
http://www.lexacorp.com.pg