[AccessD] Audit Record Changes

Your code worked like a dream.... thanks Stuart!

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Stuart McLachlan
Sent: Tuesday, February 25, 2003 5:19 PM
To: gregg steinbrenner; accessd at databaseadvisors.com
Subject: Re: [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

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com