Tracking Changes

[Sniper-BoOyA-]

Good morning,

After the disaster with the initials ive decided to let it go for now, and work om something else..

I would like to track changes made to tables using forms.

In order to do that ive created a table named 'ztblDataChanges' with the following properties:

LogID Autonumber PK
FormName txt
ControlName txt
FieldName txt
RecordID Number(Long Integer)
UserName txt
OldValue txt
NewValue txt
TimeStamp Date/Time Default value = Now()

The idea was that all the changes will be saved into this table...

Ive also created a standard module in VBA with the following code:

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

To use the function i made, i tried to call the function using the following syntax

Private Sub Form_BeforeUpdate(Cancel as Integer)
 Call LogChanges
End Sub

But as soon as i change something, close the form, and open the table ztblDataChanges, theres nothing there..

Any ideas?

Thanks !

 

[pbaldy]

I'm surprised you aren't getting an error. Your function has one required parameter, and you aren't providing it when you call it. What is the setting for Error Trapping in the VBA editor, Tools/Options/General? Have you set a breakpoint and followed the code as it executes?