troubleshooting Question

Saving history changes to sub form

Avatar of bfuchs
bfuchsFlag for United States of America asked on
Microsoft AccessVisual Basic Classic
4 Comments1 Solution184 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Top Expert 2015

The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros