Table - After Update question

[BHWATT]

I created a database with two tables. One is an employee list with an indicator - action and action plan fields. I am trying to record a history of when one or all of these fields are changed on a form. So I created a table called History employee list.

In the table employee list I created an after update macro. Basically if indicator is updated then populate the history employee list table with the new indicator and timestamp when it changed. My question is there a way to do this for three fields instead of just one. Something like if updated Indicator or action or action plan then?

 

[MarkK]

I would not do this at the table level. A table is--and should remain as much as possible--a dumb container. Yes, there are table macros, but these are mostly useful to enforce data integrity within or between dumb containers. If you need to provide or enforce business logic it is way easier to manage and maintain in code.

If indicator is updated, it is done by the user starting in the UI. Handle those UI events, validate them, then perform business logic in VBA.

IMO.

 

[BHWATT]

So would you do the same think but in the form before update?

 

[moke123]

My question is there a way to do this for three fields instead of just one.

You could probably iterate through all the fields on the form and test the value against the .oldvalue property.

something like

   Dim ctl As Control

    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Then
            If ctl.Value <> ctl.OldValue Then
                Debug.Print ctl.Name
            End If
        End If
        
    Next ctl

 

[DHookom]

I created a database with two tables. One is an employee list with an indicator - action and action plan fields. I am trying to record a history of when one or all of these fields are changed on a form. So I created a table called History employee list.

In the table employee list I created an after update macro. Basically if indicator is updated then populate the history employee list table with the new indicator and timestamp when it changed. My question is there a way to do this for three fields instead of just one. Something like if updated Indicator or action or action plan then?

There are lots of videos etc if you search the web for “ms access audit changes data macro”. I expect some allow for multiple fields

 

[The_Doc_Man]

To answer more directly, ... yes - the "BeforeUpdate" form event is the place where you would want to record changes.

The reason is that on the form, you have controls with values in them that you want to save in a record, which means they are bound controls. It is a property of bound controls that they have a .Value (what they show now) and .OldValue (what they showed when the current record was broken apart to fill in the bound controls.) With those two values you can compare to see if they changed and can make a log of the changes. If you do this AFTER the update, you lose the previous .OldValue and can't so easily tell what changed.

 

[MarkK]

Basically if indicator is updated then populate the history employee list table with the new indicator and timestamp when it changed.

The indicator IS the history, and it should not be stored in the parent row. Say I have a People table and I want to keep track of their weight over time. Their weight at each measurement should be stored in a related table.

tWeight
WeightID (PK)
PersonID (FK)
Weight (data)
Date (data)

What the weight is, when it was measured, and who it belongs to. This is a discrete and separate object from the Person. This is also a one-to-many relationship, where one person will have many related weight measurements over time.

To find the current weight for a person, filter the table for PersonID, sort the table by Date DESC, and read the weight from the first (the latest) row.
If you store the current weight in the person table, now you have a make-work project trying to find a way to insert a row in a related table to keep an audit of changes, all as a side-effect of an edit. This, as you are experiencing, is way harder to do, and is much less reliable.

 

[gemma-the-husky]

I don't think you can do this with an after update macro. You no longer have .oldvalues once the update has completed. You have to record/audit the changes before the commit, so the beforeupdate event.

You also have to consider carefully what you are trying to do. If a person changes his phone number, do you really need to store the old number? If his payrate changes, then I am sure you do need to store the history, but maybe not as part of a casual edit. Maybe there ought to be more structure. As well as the change, store who changed it, when and why, etc and have a distinct process to do that.

 

[Ken Sheridan]

In the table employee list I created an after update macro. Basically if indicator is updated then populate the history employee list table with the new indicator and timestamp when it changed. My question is there a way to do this for three fields instead of just one. Something like if updated Indicator or action or action plan then?

A distinction needs to be drawn between a record's having been updated and the values of data in the record having actually been changed. The former can occur without the latter. The following modules detect the latter in a bound form.

' module basRecordWillChange

' determines if data in a record edited
' in a form will be changed

Option Compare Database
Option Explicit

' arrays for storing values from recordsets
Dim aOldVals(), aNewVals()

Public Sub StoreCurVals(frm As Form)

    Dim ctrl As Control
    Dim n As Integer
   
    ' loop through controls
    ' and store in new array
    ReDim Preserve aOldVals(frm.Controls.Count)
    For Each ctrl In frm.Controls
        If ctrl.Tag = "IsBound" Then
            aOldVals(n) = ctrl.Value
            n = n + 1
        End If
    Next ctrl
  
End Sub

Public Sub StoreProposedVals(frm As Form)

    Dim ctrl As Control
    Dim n As Integer
   
    ' loop through controls
    ' and store in new array
    ReDim Preserve aNewVals(frm.Controls.Count)
    For Each ctrl In frm.Controls
        If ctrl.Tag = "IsBound" Then
            aNewVals(n) = ctrl.Value
            n = n + 1
        End If
    Next ctrl

End Sub

Public Function RecordWillChange() As Boolean

   Dim n As Integer, intlast As Integer
   Dim var As Variant
   Dim aOld(), aNew()
  
   intlast = UBound(aOldVals) - 1
  
   ' loop through array of original values
   ' and store in new array
   ReDim Preserve aOld(UBound(aOldVals))
   For Each var In aOldVals()
       aOld(n) = var
       n = n + 1
   Next var
  
   n = 0
  
   ' loop through array of edited values
   ' and store in new array
   ReDim Preserve aNew(UBound(aOld))
   For Each var In aNewVals()
       aNew(n) = var
       ' if any value has changed then return True
       If (IsNull(aNew(n)) And Not IsNull(aOld(n))) _
           Or (Not IsNull(aNew(n)) And IsNull(aOld(n))) _
           Or aNew(n) <> aOld(n) Then
          
           RecordWillChange = True
           Exit For
       End If
       n = n + 1
   Next var
  
End Function
' module ends

' module basChangedRecord

' determines if data in a record edited
' in a form has actually been changed

Option Compare Database
Option Explicit

' arrays for storing values from recordsets
Dim aOldVals(), aNewVals()

' Boolean variable to restrict openig of report to button om form
Public blnOpenedFromButton As Boolean

Public Sub StoreOldVals(rst As DAO.Recordset)

   ' store values of current row in array
   aOldVals = rst.GetRows()
  
End Sub

Public Sub StoreNewVals(rst As DAO.Recordset)
  
   ' store values of edited row in array
   aNewVals = rst.GetRows()

End Sub

Public Function RecordHasChanged() As Boolean

   Dim n As Integer, intlast As Integer
   Dim var As Variant
   Dim aOld(), aNew()
  
   intlast = UBound(aOldVals) - 1
  
   ' loop through array of original values
   ' and store in new array
   ReDim Preserve aOld(UBound(aOldVals))
   For Each var In aOldVals()
       aOld(n) = var
       n = n + 1
   Next var
  
   n = 0
  
   ' loop through array of edited values
   ' and store in new array
   ReDim Preserve aNew(UBound(aOld))
   For Each var In aNewVals()
       aNew(n) = var
       ' if any value has changed then return True
       If (IsNull(aNew(n)) And Not IsNull(aOld(n))) _
           Or (Not IsNull(aNew(n)) And IsNull(aOld(n))) _
           Or aNew(n) <> aOld(n) Then
          
           RecordHasChanged = True
           Exit For
       End If
       n = n + 1
   Next var
  
End Function



Public Function ColumnWillChange(lngContactID, strColumnName As String, varColumnVal As Variant, dtmDateTimeStamp As Date) As Boolean

    Dim varNextDateTimeStamp As Variant
    Dim strCriteria As String
   
    strCriteria = "ContactID = " & lngContactID & " And DateTimeStamp > #" & Format(dtmDateTimeStamp, "yyyy-mm-dd hh:nn:ss") & "#"
    varNextDateTimeStamp = DMin("DateTimeStamp", "qryContactsAudit", strCriteria)
   
    If Not IsNull(varNextDateTimeStamp) Then
        strCriteria = "ContactID = " & lngContactID & " And DateTimeStamp = #" & Format(varNextDateTimeStamp, "yyyy-mm-dd hh:nn:ss") & "#"
        If DLookup(strColumnName, "qryContactsAudit", strCriteria) <> varColumnVal Then
            ColumnWillChange = True
        End If
    End If
   
End Function
' Module ends

As it stands the modules' functions detect that code will change or has changed in any control in the form tagged as "IsBound". In your case you could tag only the controls whose values you wish to examine.

An example of the use of the module is the following Contacts form's event procedure:

Private Sub Form_BeforeUpdate(Cancel As Integer)

 On Error GoTo Err_Handler

    Const MESSAGE_TEXT = "Data has changed.  Save record?"
    Dim strSQL As String
   
    If Not Me.NewRecord Then
        ' store unsaved values of bound controls in array
        StoreProposedVals Me
       
        ' if data in controls has changed
        ' confirmation to save record
        If RecordWillChange() Then
             ' insert copy of unedited current record into audit table
            strSQL = "INSERT INTO ContactsAudit(ContactID,FirstName,LastName,Dob,DateTimeStamp,UpdatedBy,AuditDateTimeStamp)" & _
                " SELECT ContactID,FirstName,LastName,Dob,DateTimeStamp,UpdatedBy,#" & Format(Now(), "yyyy-mm-dd hh:nn:ss") & "# FROM ContactsAudited" & _
                " WHERE ContactID = " & Me.ContactID
               
            If MsgBox(MESSAGE_TEXT, vbQuestion + vbYesNo, "Confirm") = vbNo Then
                Cancel = True
                Exit Sub
            Else
                CurrentDb.Execute strSQL
               
                ' requery subform
                Me.sfcContactsAudit.Requery
               
                ' timestamp record
                Me.DateTimeStamp = Now()
                Me.UpdatedBy = GetUser()
                ' call form's Current event procedure
                Form_Current
            End If
        Else
            ' timestamp record
            Me.DateTimeStamp = Now()
            Me.UpdatedBy = GetUser()
        End If
    End If
Exit_Here:
   Exit Sub
  
Err_Handler:
   MsgBox Err.Description, vbExclamation, "Error"
   Resume Exit_Here

End Sub

This inserts a row into an audit table if data has actually changed. The values inserted include the current user name, obtained by calling the Windows API GetUserName function, and a date/time stamp, returned by the VBA Now() function.

 

[Pat Hartman]

I would use the AfterUpdate event to trigger the log. That way, you are certain that the change to the record was actually applied. If you create the log in the BeforeUpdate event, you may create log records for rows that were never actually updated.

The tricky part is you no longer have the .OldValue available once you get to the AfterUpdate event. It has been replaced with whatever was the last saved value.

If the logging is triggered ONLY when a change to a certain field or fields is made, then you have to create a "note to self" in the BeforeUpdate event. You check the relevant fields and compare their .Value with their .OldValue and set a flag which you then check in the AfterUpdate event. If the flag is set, create the log and reset the flag. Be careful with how you compare the .Value with .OldValue if null is allowed for any field. I always concatenate a ZLS to the field to avoid a more complicated expression.

If Me.Fld1 & "" <> Me.Fld1.OldValue & "" Then 
    CreateLog = True
End If

 

[theDBguy]

My question is there a way to do this for three fields instead of just one. Something like if updated Indicator or action or action plan then?

If you stick with data macros, I don't think there is a way to do what you're asking. In any case, even if there was a way to do that, I am not sure how it would apply to your situation. It looks like your macro checks if a field is updated and then logs the new value in the audit table. So, if you were able to check whether any of the fields were updated, how will you then decide which new value to log?

If Updated(Field1 OR Field2 OR Field3) Then
CreateRecord(for which one?)

So, if you don't have a lot of fields to check, you might be stuck with just checking each one. Or, depending on your audit log table structure, perhaps just log each value whether it was changed or not (this eliminates checking if the value was updated) and perhaps you can simply create a form to display the change trend. Just a thought...