Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

asked on

Saving history changes to sub form

Hi Experts,

I have the following code that is meant to keep history of all changes done to records, the code works to all forms including popup forms, however for sub forms it doesn't,

Wondering how can I modify that so it would work for all type of forms?

Public Function SaveHistoryChanges(Optional sTable As String = "", Optional sField As String = "", Optional vValue As Variant = "", Optional lID As Long = 0)
    Dim sSQl As String, cNtrl As Control
    If Len(sTable) = 0 Then sTable = Screen.ActiveForm.Name
    If lID = 0 Then lID = Nz(Screen.ActiveForm.ID, 0)
    
    On Error Resume Next
    For Each cNtrl In Screen.ActiveForm.Controls
        If (TypeOf cNtrl Is TextBox Or TypeOf cNtrl Is ComboBox Or TypeOf cNtrl Is CheckBox Or cNtrl.ControlType = 107) Then
            If Nz(cNtrl.Value) <> Nz(cNtrl.OldValue) And cNtrl.Visible = True And cNtrl.Enabled = True Then
                If Not IsNull(cNtrl.OldValue) And Not (TypeOf cNtrl Is CheckBox) Then
                    If Nz(tLookup("ID", "HistoryChanges", "TableName = '" & sTable & "' and FieldName = '" & cNtrl.ControlSource & "' and TableID = " & lID), 0) = 0 Then
                        sSQl = "Insert into HistoryChanges (TableName, FieldName, Value, TableID)"
                        sSQl = sSQl & " Values ('" & sTable & "','" & cNtrl.ControlSource & "','" & cNtrl.OldValue & "'," & lID & ")"
                        CurrentProject.Connection.Execute sSQl
                    End If
                End If
                sSQl = "Insert into HistoryChanges (TableName, FieldName, Value, Initial, TableID)"
                sSQl = sSQl & " Values ('" & sTable & "','" & cNtrl.ControlSource & "','" & cNtrl.Value & "','" & GetInitial & "'," & lID & ")"
                CurrentProject.Connection.Execute sSQl
                
            End If
        End If
    Next

End Function

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs

ASKER

Hi,

Do you mean I should change for the following?

Public Function SaveHistoryChanges(Optional sTable As String = "", Optional sField As String = "", Optional vValue As Variant = "", Optional lID As Long = 0, [b]Optional f As Form)[/b]
    Dim sSQl As String, cNtrl As Control
    If Len(sTable) = 0 Then sTable = Screen.ActiveForm.Name
    If lID = 0 Then lID = Nz(Screen.ActiveForm.ID, 0)
    
[b]    If f Is Nothing Then
        Set f = Screen.ActiveForm
    End If[/b]
    
    On Error Resume Next
    For Each cNtrl In [b]f.Controls[/b]
        If (TypeOf cNtrl Is TextBox Or TypeOf cNtrl Is ComboBox Or TypeOf cNtrl Is CheckBox Or cNtrl.ControlType = 107) Then
            If Nz(cNtrl.Value) <> Nz(cNtrl.OldValue) And cNtrl.Visible = True And cNtrl.Enabled = True Then
                If Not IsNull(cNtrl.OldValue) And Not (TypeOf cNtrl Is CheckBox) Then
                    If Nz(tLookup("ID", "HistoryChanges", "TableName = '" & sTable & "' and FieldName = '" & cNtrl.ControlSource & "' and TableID = " & lID), 0) = 0 Then
                        sSQl = "Insert into HistoryChanges (TableName, FieldName, Value, TableID)"
                        sSQl = sSQl & " Values ('" & sTable & "','" & cNtrl.ControlSource & "','" & cNtrl.OldValue & "'," & lID & ")"
                        CurrentProject.Connection.Execute sSQl
                    End If
                End If
                sSQl = "Insert into HistoryChanges (TableName, FieldName, Value, Initial, TableID)"
                sSQl = sSQl & " Values ('" & sTable & "','" & cNtrl.ControlSource & "','" & cNtrl.Value & "','" & GetInitial & "'," & lID & ")"
                CurrentProject.Connection.Execute sSQl
                
            End If
        End If
    Next

End Function

Open in new window


then pass me.form to that parameter.

FYI- this is a global module so I can use it from everywhere.

Thanks,
Ben
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

yes
Avatar of bfuchs

ASKER

That worked, thank you!