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

Microsoft AccessVisual Basic Classic

Last Comment

bfuchs

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

crystal (strive4peace) - Microsoft MVP, Access

10/5/2016

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

bfuchs

10/5/2016

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

crystal (strive4peace) - Microsoft MVP, Access

10/5/2016

yes

bfuchs

10/5/2016

ASKER

That worked, thank you!

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy