If problem

[mpm32]

I have a DB where I want to create an audit trail. I found some code on this site posted by Bob Stubbs. It works where I have a form and if a field gets changed, it writes to the table. However, I am having trouble getting it to work for more than one field. If 2 or more fields get changed, I want them all to be written to the audit table. I think the problem is with my If statement. Once it gets to a true value, it stops there. Instead of having this on the forms before update event, should I have each If statment on each fields before update event?

Private Sub Form_BeforeUpdate(Cancel As Integer)

If lastname.OldValue <> lastname Then
        strResult = WriteAuditRecord("EmployeeData", "LastName", PersonnelNo, lastname.OldValue, lastname)
ElseIf firstname.OldValue <> firstname Then
        strResult = WriteAuditRecord("EmployeeData", "FirstName", PersonnelNo, firstname.OldValue, firstname)
ElseIf Employeegrp.OldValue <> Employeegrp Then
        strResult = WriteAuditRecord("EmployeeData", "Employeegrp", PersonnelNo, Employeegrp.OldValue, Employeegrp)
ElseIf Status.OldValue <> Status Then
        strResult = WriteAuditRecord("EmployeeData", "Status", PersonnelNo, Status.OldValue, Status)
ElseIf Eesubgroup.OldValue <> Eesubgroup Then
        strResult = WriteAuditRecord("EmployeeData", "Eesubgroup", PersonnelNo, Eesubgroup.OldValue, Eesubgroup)
ElseIf Position.OldValue <> Position Then
        strResult = WriteAuditRecord("EmployeeData", "Position", PersonnelNo, Position.OldValue, Position)
ElseIf JobTitle.OldValue <> JobTitle Then
        strResult = WriteAuditRecord("EmployeeData", "JobTitle", PersonnelNo, JobTitle.OldValue, JobTitle)
            End If
End Sub

 

[rjoubert]

You need a separate If...End If block for each field...

Private Sub Form_BeforeUpdate(Cancel As Integer)

If lastname.OldValue <> lastname Then
        strResult = WriteAuditRecord("EmployeeData", "LastName", PersonnelNo, lastname.OldValue, lastname)
End If

If firstname.OldValue <> firstname Then
        strResult = WriteAuditRecord("EmployeeData", "FirstName", PersonnelNo, firstname.OldValue, firstname)
End If

If Employeegrp.OldValue <> Employeegrp Then
        strResult = WriteAuditRecord("EmployeeData", "Employeegrp", PersonnelNo, Employeegrp.OldValue, Employeegrp)
End If

If Status.OldValue <> Status Then
        strResult = WriteAuditRecord("EmployeeData", "Status", PersonnelNo, Status.OldValue, Status)
End If

If Eesubgroup.OldValue <> Eesubgroup Then
        strResult = WriteAuditRecord("EmployeeData", "Eesubgroup", PersonnelNo, Eesubgroup.OldValue, Eesubgroup)
End If

If Position.OldValue <> Position Then
        strResult = WriteAuditRecord("EmployeeData", "Position", PersonnelNo, Position.OldValue, Position)
End If

If JobTitle.OldValue <> JobTitle Then
        strResult = WriteAuditRecord("EmployeeData", "JobTitle", PersonnelNo, JobTitle.OldValue, JobTitle)
End If

End Sub

 

[mpm32]

I figured it out, it works when I enter each statement into each fields Before Update property.

Thanks anyway.

 

[Golom]

Private Sub Form_BeforeUpdate(Cancel As Integer)

If lastname.OldValue <> lastname Then
   strResult = WriteAuditRecord("EmployeeData", "LastName", PersonnelNo, lastname.OldValue, lastname)
End If

If firstname.OldValue <> firstname Then
   strResult = WriteAuditRecord("EmployeeData", "FirstName", PersonnelNo, firstname.OldValue, firstname)
End If

If Employeegrp.OldValue <> Employeegrp Then
   strResult = WriteAuditRecord("EmployeeData", "Employeegrp", PersonnelNo, Employeegrp.OldValue, Employeegrp)
End If

If Status.OldValue <> Status Then
   strResult = WriteAuditRecord("EmployeeData", "Status", PersonnelNo, Status.OldValue, Status)
End If

If Eesubgroup.OldValue <> Eesubgroup Then
   strResult = WriteAuditRecord("EmployeeData", "Eesubgroup", PersonnelNo, Eesubgroup.OldValue, Eesubgroup)
End If

If Position.OldValue <> Position Then
   strResult = WriteAuditRecord("EmployeeData", "Position", PersonnelNo, Position.OldValue, Position)
End If

If JobTitle.OldValue <> JobTitle Then
   strResult = WriteAuditRecord("EmployeeData", "JobTitle", PersonnelNo, JobTitle.OldValue, JobTitle)
End If

End Sub

 

[mpm32]

Thanks both of you so it looks like it would work in the forms before update if I block it like that.

Another quick question;

When the function captures the Current User it is not capturing the environ username. So for example if I make a change it captures "Admin" for the ChangedBy field. I would like to capture the network ID. I know about environ("username") but where would I put it in this code?

Function WriteAuditRecord(strTableName As String, strFieldName As String, _
    strRecordKey As String, strOldValue As String, strNewValue As String)

Dim strLogDate As String
Dim strSQL As String

strLogDate = Format$(Now, "dd/mm/yyyy")

'----------------------------------------------------
'- Fix any single quote characters in strings, as   -
'- these cause an SQL error                         -
'----------------------------------------------------
    strOldValue = FixSingleQuote(strOldValue)
    strNewValue = FixSingleQuote(strNewValue)
    strRecordKey = FixSingleQuote(strRecordKey)
    
'----------------------------------------------------
'- Write the audit record                           -
'----------------------------------------------------
    DoCmd.SetWarnings (False)
    
    strSQL = "INSERT INTO tblAuditTrail (TableName, FieldName, RecordKey, OldValue, NewValue, ChangeDate, ChangedBy) "
    strSQL = strSQL & "VALUES ('" & strTableName & "', '" & strFieldName & "', '" & strRecordKey
    strSQL = strSQL & "', '" & strOldValue & "', '" & strNewValue & "', '" & strLogDate & "', '" & CurrentUser & "')"
        

    DoCmd.RunSQL (strSQL)

    DoCmd.SetWarnings (True)

End Function

 

[rjoubert]

Just replace CurrentUser with environ("username")

 

[rjoubert]

strSQL = strSQL & "', '" & strOldValue & "', '" & strNewValue & "', '" & strLogDate & "', '" & environ("username") & "')"

 

[mpm32]

Thanks again! I would have tried that - eventually. You just saved me a lot of time.