When Saving a record - Selected Changes into a memo Field 1

[darinmc]

When a person makes changes to an employees record, is there a way of getting ONLY the changed fields and placing them into a memo field When Saving the record ?
Lets call the memo field (empChanges)

I would concatenate them, not sure if the label caption would be included. Would be preferable.

Thx
Darin

 

[Remou]

This may be of interest:

http://support.microsoft.com/kb/197592

You will also find a FAQ on auditing, as far as I remember.

 

[fneily]

You can do something like this:
Create a global variable to hold the changes. Goto VBA(ALT + F11) and on the left, open Modules. You should see a Module1. If not, do a Insert - Module. Type in at the top below the Option line: Global holdchanges As String

Then on the AfterUpdate event of a control you want to record the change, put
Private Sub ControlName_AfterUpdate()
holdchanges = holdchanges & " " & Me![ControlName].Value
End Sub

Then on the BeforeUpdate event of the form put:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me![MemoName].Value = holdchanges
holdchanges = " "
End Sub

So, basically, the variable stores all the changes, then right before the record is saved, that line is written to the memo field. Now this will not keep past changes, they're overwritten. But you did not ask that.

There's probably an easier way. So wait for more responses.

 

[darinmc]

Thx to both of you.. I did the procedure from the microsoft site and seemed quite easy.
However, on the website it says:

"In the BeforeUpdate event of the form, type =AuditTrail(). "

Instead, I did it VB and wrote

Private Sub Form_BeforeUpdate(Cancel As Integer)
AuditTrail
End Sub

Is this ok?

My form seems to be a bit slower to open!!!!!

Should this be???

Thx
Darin

 

[darinmc]

I just Redid the module, Instead of Naming it Audit, I called it AuditTrail

Private Sub Form_BeforeUpdate(Cancel As Integer)
AuditTrail
End Sub

Beginning Module code

Option Compare Database

Option Explicit

Function AuditTrail()
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 " & Now() & " BY " & Forms!frmSwitchboard!tName & "---"

I get a compile error:
expected variable or procedure, not module

Whats wrong?

 

[Remou]

You must have different names for modules and the procedures they contain. It is often a good idea to add a few letters before the name of the module say mod, bas, or mdl.

 

[darinmc]

I have got it working well now, using the AuditTrail module from Microsoft.

Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup
' Skip Updates field.
If C.Name <> "tempUpdates" Then

' If control was previously Null, record "previous
' value was blank."
'If IsNull(C.Value) Or C.Value = "" <> C.OldValue Then
If IsNull(C.OldValue) Or C.OldValue = "" Then
'MyForm!tempUpdates = MyForm!tempUpdates & Chr(13) & _
Chr(10) & C.Name & " was - To (" & C.Value & ")"

' If control had previous value, record previous value.
ElseIf IIf(IsNull(C.Value), "", C.Value) <> C.OldValue Then
MyForm!tempUpdates = MyForm!tempUpdates & Chr(13) & Chr(10) & _
C.Name & " - Changed from (" & C.OldValue & ") - To (" & Nz(C.Value, "-") & ")"
'MyForm!tempUpdates = Chr(13) & Chr(10) & _
C.Name & "Prev Value was " & C.OldValue & " - New Value IS " & Nz(C.Value, "NoNe")
End If
End If
End Select
Next C

The first part of the
If IsNull(C.OldValue) Or C.OldValue = "" Then
'MyForm!tempUpdates = MyForm!tempUpdates & Chr(13) & _
Chr(10) & C.Name & " was - To (" & C.Value & ")"
is ok, but I dont want it to report every field thats blank.
I would like it to show only the controls that have been changed.. JUST LIKE the elseif IIF part... the only problem, if a field is null, its not showing that it was changed

If IsNull(C.OldValue) Or C.OldValue = "" Then
                    'MyForm!tempUpdates = MyForm!tempUpdates & Chr(13) & _
                    Chr(10) & C.Name & " was - To (" & C.Value & ")"
                    
                ' If control had previous value, record previous value.
                ElseIf IIf(IsNull(C.Value), "", C.Value) <> C.OldValue Then
                    MyForm!tempUpdates = MyForm!tempUpdates & Chr(13) & Chr(10) & _
                    C.Name & " - Changed from (" & C.OldValue & ") - To (" & Nz(C.Value, "-") & ")"
                    'MyForm!tempUpdates = Chr(13) & Chr(10) & _
                    C.Name & "Prev Value was " & C.OldValue & " - New Value IS " & Nz(C.Value, "NoNe")
                End If

Hope i'm clear with my question lol, got an enormous black eye and cant read too well

Hope u can help
Thx
Darin

 

[darinmc]

I made the modification on the if Statement, similar to the original IIF part, and it now shows ALL the changed records..

The idea behind all this code is to Have an audit trail of all changes. The TempUpdates is for purposes of Printing a report of LAST changes to put in the Employees file...

Option Compare Database

Option Explicit

Function AuditTrail()
On Error GoTo Err_Handler
    
    Dim MyForm As Form, C As Control, xName As String
    Set MyForm = Screen.ActiveForm

MyForm!tempUpdates = Null

    'Set date and current user if form has been updated.
'    MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
    "--- CHANGES MADE ON " & Now() & " BY " & Forms!frmSwitchboard!tName & "---"

    MyForm!tempUpdates = MyForm!tempUpdates & Chr(13) & Chr(10) & _
    "--- CHANGES MADE ON " & Now() & " BY " & Forms!frmSwitchboard!tName & "---"
' & Chr(13) & Chr(10)
    '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 <> "tempUpdates" Then
    
            ' If control was previously Null, record "previous
            ' value was blank."
                'If IsNull(C.OldValue) Or C.OldValue = "" Then
                'BELOW = NEW and works
                'On a NEW record, THIS part shows ALL changes that were
                'Null or ""
                If IIf(IsNull(C.OldValue), "", C.OldValue) <> C.Value Then
                    MyForm!tempUpdates = MyForm!tempUpdates & Chr(13) & _
                    Chr(10) & C.Name & " Was (-) To (" & C.Value & ")"
                    
                ' If control had previous value, record previous value.
                ElseIf IIf(IsNull(C.Value), "", C.Value) <> C.OldValue Then
                    MyForm!tempUpdates = MyForm!tempUpdates & Chr(13) & Chr(10) & _
                    C.Name & " - Changed from (" & C.OldValue & ") - To (" & Nz(C.Value, "-") & ")"
                    'MyForm!tempUpdates = Chr(13) & Chr(10) & _
                    C.Name & "Prev Value was " & C.OldValue & " - New Value IS " & Nz(C.Value, "NoNe")
                End If
            End If
        End Select
    Next C

MyForm!Updates = MyForm!tempUpdates & Chr(13) & Chr(10) & MyForm!Updates

TryNextC:
    Exit Function
      
Err_Handler:
    If Err.Number <> 64535 Then
        MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " & Err.Description
    End If
    Resume TryNextC
End Function

I WOULD LIKE TO TAKE THIS A STEP FURTHER IF YOU COULD HELP

I have used Tags before.. Would it be possible to Include an If statement if a tag was, eg Bank or Name, then I could use an insert into another table to notify payroll that there are records to update?
I would put them to a simple table, Registration No, Y/N, DateStamp and memo field (Holding Temp Info). Think I can build the insert part
BUT
not sure about the tag part???

would it be something like this at the end of the module? ----

MyForm!Updates = MyForm!tempUpdates & Chr(13) & Chr(10) & MyForm!Updates
if c.tag = "Bank" Or c.tag = "Name" Then
Insert ...
end if

Thx
Darin