Solved

Saving history changes to sub form

Posted on 2016-10-05
4
47 Views
Last Modified: 2016-10-05
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

0
Comment
Question by:bfuchs
  • 2
  • 2
4 Comments
 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
ID: 41831061
Screen.ActiveForm.Name refers to the main form.  In the parameters, create something like
f as form

Open in new window

and in the code, refer to f.  If this is in code behind a form, you can just send Me for f
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41831069
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
0
 
LVL 19
ID: 41831073
yes
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 41831082
That worked, thank you!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question