Solved How to use oldValue after updating the record in subform (1 Viewer)

[VBANewBie :)]

Hello Guys , I came across with this code , It is an audit trial code :

   Public Function WriteAudit(frm As Form, lngID As Long) As Boolean
    On Error GoTo err_WriteAudit
    
        Dim ctlC As Control
        Dim strSQL As String
        Dim bOK As Boolean
      
      
      
        bOK = False
        
       DoCmd.SetWarnings False
        
        ' For each control.
        For Each ctlC In frm.Controls
            If TypeOf ctlC Is TextBox Or TypeOf ctlC Is ComboBox Then
                If ctlC.Value <> ctlC.OldValue Or IsNull(ctlC.OldValue) Then
                    If Not IsNull(ctlC.Value) Then
                        strSQL = "INSERT INTO tblAudit ( ID, FieldChanged, FieldChangedFrom, FieldChangedTo, User, DateofHit, FrmName , FrmRcrdSrc  ) " & _
                               " SELECT " & lngID & " , " & _
                               "'" & ctlC.Name & "', " & _
                               "'" & ctlC.OldValue & "', " & _
                               "'" & ctlC.Value & "', " & _
                               "'" & GetUserName_TSB & "', " & _
                               "'" & Now & "' , " & _
                               "'" & M & "', " & _
                               "'" & R & "'"
    
                        'Debug.Print strSQL
                        DoCmd.RunSQL strSQL
                    End If
                End If
            End If
        Next ctlC
        
        WriteAudit = bOK
        
    exit_WriteAudit:
        DoCmd.SetWarnings True
        Exit Function
        
    err_WriteAudit:
        MsgBox Err.Description
        Resume exit_WriteAudit
        
    End Function

It works fine with single forms but not with subforms , When i tried to run the code On Form AfterUpdate Nothing happens , When i tried to use it on subform fields afterupdate it duplicates all the changed values except the last changed one take a look on the following gif

As you saw it appended computer change two times but Warehouse change just one time as it supposed .

So,I have two requests please : -
1- Fix the appending process to just append the entire record one time .
2-How to modify the code to work with not only changes made but also with add record and delete record , For Example my table is TblAudit i will add new field called ChangeDesc if i edit value the ChangeDesc field will contain the word "Update" , if i delete a record it will append it before deleting and ChangeDesc contain "Delete" , If i add a record it will append it and the ChangeDesc will contain "Add" .
Sorry for many requests guys , You will find my sample db attached , Thanks i advance
PS: I posted the same post in other forum because it is urgent for me , Thanks.

 

[bob fitz]

PS: I posted the same post in other forum because it is urgent for me , Thanks.

So are you going to give us a link to the other forum(s) so that we can see if you've received a solution there?

 

[VBANewBie :)]

So are you going to give us a link to the other forum(s) so that we can see if you've received a solution there?

I haven’t here is the link

How to Use OldValue in subforms

I came across with this code , It is an audit trail code : Public Function WriteAudit(frm As Form, lngID As Long) As Boolean On Error GoTo err_WriteAudit Dim ctlC As Control Dim strSQL As ...

stackoverflow.com

 

[arnelgp]

if you have subform, use the Subform's Event (not the main form) to add the audit trail.

 

[VBANewBie :)]

if you have subform, use the Subform's Event (not the main form) to add the audit trail.

Thanks for reply , I Did that not working

Private Sub Form_AfterUpdate()
Audit
End Sub

Could you please edit my attachment as demonstration ?

 

[arnelgp]

use BeforeUpdate not AfterUpdate

 

[VBANewBie :)]

use BeforeUpdate not AfterUpdate

Perfect , Thank you so much it is working now .
What about #2 Request can it be done ?

 

[arnelgp]

here is a demo from someone else.

 

[VBANewBie :)]

Thanks For Help @arnelgp