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.