Bob Bedell
bobbedell15 at msn.com
Mon Jun 21 18:47:54 CDT 2004
Hi David, You could play with adding a memo field to your form's record source called "Updates", add a textbox control to your form thats bound to "Updates" (you can hide this field if you like), add =AuditTrailX() as a function call in your form's Before Update event, then add the following module to your project. Wish I could remember where this came from but i can't. The code iterates through all the data entry controls in your form's controls collection, recording old and new values for only those fields that have changed. Function AuditTrailX() On Error GoTo Err_Handler Dim MyForm As Form, C As Control, xName As String Set MyForm = Screen.ActiveForm 'Set date and current user if form has been updated. MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _ "Changes made on " & Date & " by " & CurrentUser() & ";" 'If new record, record it in audit trail and exit sub. If MyForm.NewRecord = True Then MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _ "New Record """ End If 'Check each data entry control for change and record 'old value of Control. For Each C In MyForm.Controls 'Only check data entry type controls. Select Case C.ControlType Case acTextBox, acComboBox, acListBox, acOptionGroup ' Skip Updates field. If C.Name <> "Updates" Then ' If control was previously Null, record "previous ' value was blank." If IsNull(C.OldValue) Or C.OldValue = "" Then MyForm!Updates = MyForm!Updates & Chr(13) & _ Chr(10) & C.Name & "--previous value was blank" ' If control had previous value, record previous value. ElseIf C.Value <> C.OldValue Then MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _ C.Name & "==previous value was " & C.OldValue End If End If End Select Next C TryNextC: Exit Function Err_Handler: If Err.Number <> 64535 Then MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " & Err.Description End If Resume TryNextC End Function Bob >From: David Emerson <davide at dalyn.co.nz> >Reply-To: Access Developers discussion and problem >solving<accessd at databaseadvisors.com> >To: accessd at databaseadvisors.com >Subject: [AccessD] Recording field changes >Date: Tue, 22 Jun 2004 08:43:34 +1200 > >Group, > >I have a client that needs to record all changes to the data on a field by >field basis (they don't want to record whole records if there is a change - >just the fields that changed (old and new values)). My initial though is >to put code in every field that records the value on entry, compares this >on exit and records the change if they values are different. > >Because there are several hundred fields involved I was wondering if there >was any easy way of doing the job without adding code to every field. > >Regards > >David Emerson >Dalyn Software Ltd >25 Cunliffe St, Churton Park >Wellington, New Zealand >Ph/Fax (04) 478-7456 >Mobile 027-280-9348 > >-- >_______________________________________________ >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com _________________________________________________________________ MSN Movies - Trailers, showtimes, DVD's, and the latest news from Hollywood! http://movies.msn.click-url.com/go/onm00200509ave/direct/01/