Tracking data changes in Access

[decent_boy]

Hi,

I have downloaded below codes from
Tracking data changes in Access

Function LogChanges(lngID As Long, Optional strField As String = "")
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim varOld As Variant
    Dim varNew As Variant
    Dim strFormName As String
    Dim strControlName As String
    
    varOld = Screen.ActiveControl.OldValue
    varNew = Screen.ActiveControl.Value
    strFormName = Screen.ActiveForm.Name
    strControlName = Screen.ActiveControl.Name
    Set dbs = CurrentDb()
    Set rst = dbs.TableDefs("ztblDataChanges").OpenRecordset
    
    With rst
        .AddNew
        !FormName = strFormName
        !ControlName = strControlName
        If strField = "" Then
            !FieldName = strControlName
        Else
            !FieldName = strField
        End If
        !RecordID = lngID
        !UserName = Environ("username")
        If Not IsNull(varOld) Then
            !OldValue = CStr(varOld)
        End If
        !NewValue = CStr(varNew)
        .Update
    End With
    'clean up
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
End Function

Private Sub txtAddress1_BeforeUpdate(Cancel As Integer)
    Call LogChanges(CustomerID, "Address1")
End Sub

As per given instruction on this web site I created table but I don't understand that how should I call/run this function in my multiple tables. Please guide me how could it be done in a better way.

 

[Micron]

You don't call that from within tables. Tables should not be interacted with like spreadsheets are. That site seems to assume that one would realize a form is needed by explaining the use of Screen.ActiveControl. They should say so because not everyone is going to understand that.

The BeforeUpdate event of the control (you're showing that control to be called txtAddress1) calls the logging code. If you have a lot of form controls that need this, that's a lot of events but would be necessary if you really need to pinpoint what was altered. If you can get by with just adding a record when something is changed then you could just write that record to another table using the form BeforeUpdate event (so one event). However, you'd have to read the records in a query to figure out the changes. Note - if your controls are not bound you cannot use the OldValue property so be aware of that.

 

[decent_boy]

Yes, I want to get by with just adding a record when something is changed I have multiple forms and tables and I want that when any change is made by any user in tables/forms so this event would record in logchange table.

Would you please suggest any better way to do this because I am new in access and I am in learning stage.

 

[Micron]

There may not be a better way. It depends on exactly what you want from this. If just to flag an altered record then could simply use form BeforeUpdate but you will not know what the changes are since whatever is currently in the form is what will get saved. However, given the code you posted it seems you want a full blown audit of the records, so you need a solution like the one you copied. That example only illustrates based on one field. As far as I can tell - say this is about customer info and you have 4 fields you want to audit and I alter all 4 of them. You will get 4 records for this customer, each one with a different field showing the before and after values.

This is hardly a newbie exercise but if you've got the time to research and decide what method fits your needs and have the time to learn and experiment then you might end up where you want to be. I'd advise against picking the first solution you find, but moreover, you need to understand exactly what the solution does.

If I had several fields to audit I might use the form BeforeUpdate event. I'd set a Tag property value for the ones I care about and in that event, loop over form controls looking for those with the Tag. If their OldValue doesn't equal the current value I could write the old values to the audit table all at once, but just for the ones that changed.

Again, OldValue only applies to bound controls. If your form is not bound, then it's even more complicated.
HTH

 

[decent_boy]

Ok thank so much for guiding me and I search form event before update which told.
I will try my best to learn it and try to understand bound control before doing this

 

[welshgasman]

Perhaps read some of these links?

audit trail vba access - Google Search

 

[jackd]

You ay get some insight from this thread and the sample data macro and database.