how to audit changes to a form

[muneermohd9690]

I have an access database for employees.certa in users from manager level has access to this databse. I want to log any changes (add new,edit,delete ) to the databse using vba code.the users will be using forms to do the edits. I have tried using the below code,but the problem is that the below code works only for a standalone form. My forms contain sub forms and navigation forms.

[muneermohd9690]

Option Compare Database
 
Public Function auditchanges(recordid As String, useraction As String)
On Error GoTo auditerror
Dim DB As Database
Dim rst As Recordset
Dim clt As Control
 
Set DB = CurrentDb
Set rst = DB.OpenRecordset("select * from audittrail", adOpenDynamic)
userlogin = getuserlogon()
Select Case useraction
    Case "new"
        With rst
            .AddNew
            ![DateTime] = Now()
            ![username] = userlogin
            ![FormName] = Screen.ActiveForm.Name
            '![FormName] = Screen.ActiveForm.ActiveControl.Name
            ![Action] = useraction
            ![recordid] = Screen.ActiveForm.Controls(recordid).Value
            .Update
        End With
 
    Case "delete"
        With rst
            .AddNew
            ![DateTime] = Now()
            ![username] = userlogin
            ![FormName] = Screen.ActiveForm.Name
            '![FormName] = Screen.ActiveForm.ActiveControl.Name
            ![Action] = useraction
            ![recordid] = Screen.ActiveForm.Controls(recordid).Value
            .Update
        End With
 
    Case "edit"
         'For Each clt In Screen.ActiveForm.Controls
         For Each clt In Screen.ActiveForm.ActiveControl.Form
            If (clt.ControlType = acTextBox Or clt.ControlType = acComboBox) Then
                If Nz(clt.Value) <> Nz(clt.OldValue) Then
                    With rst
                        .AddNew
                        ![DateTime] = Now()
                        ![username] = userlogin
                        '![FormName] = Screen.ActiveForm.Name
                        ![FormName] = Screen.ActiveForm.ActiveControl.Form.Name
                        ![Action] = useraction
                        '![recordid] = Screen.ActiveForm.Controls(recordid).Value
                        '![recordid] = Screen.ActiveForm.ActiveControl.Form(recordid).Value
                        ![recordid] = Screen.ActiveForm.ActiveControl.Form(recordid).Value
                        ![FieldName] = clt.ControlSource
                        ![OldValue] = clt.OldValue
                        ![NewValue] = clt.Value
                        .Update
                    End With
                End If
             End If
        Next clt
 
End Select
rst.Close
DB.Close
Set rst = Nothing
Set DB = Nothing
auditerror:
    'MsgBox Err.Number & ":" & Err.Description, vbCritical, "Error"
    Exit Function
 
End Function
 
 

[muneermohd9690]

link to download the database

https://www.dropbox.com/s/kaw7j17bhe...111.accdb?dl=0