Beyond my skillset.

[fenhow]

Hello,

I am using some code I found on the forum to track changes to my DB.

It works awesome however I was wondering if there is a way I can just write what changed in the mirror table versus the entire record.

Here is the Before Update Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error Resume Next

' some controls may not have the Tag property ,
' hence the resume next

Dim blnCheckDiff As Boolean
Dim ctl As Control

blnCheckDiff = False
For Each ctl In Me.Controls
If ctl.Tag = "Check" And ctl.Value <> ctl.OldValue Then
blnCheckDiff = True
End If
Next
If blnCheckDiff Then
[Date_Edited] = Now()

Else
Cancel = True
End If
End Sub

And here is the After Update Code:

Private Sub Form_AfterUpdate()
Set db = CurrentDb
db.Execute "INSERT INTO [tbl_MINERAL_MineralOwner EDITS] " _
& " SELECT * FROM [tbl_MINERAL_MineralOwner] WHERE " _
& " [tbl_MINERAL_MineralOwner].[MineralOwner_ID]=" & Me![MineralOwner_ID]
Set db = Nothing
End Sub

 

[HiTechCoach]

Hello,

I am using some code I found on the forum to track changes to my DB.

It works awesome however I was wondering if there is a way I can just write what changed in the mirror table versus the entire record.

Here is the Before Update Code:

[COLOR="Blue"]Private Sub Form_BeforeUpdate(Cancel As Integer)
       On Error Resume Next

        ' some controls may not have the Tag property ,
    ' hence the resume next

       Dim blnCheckDiff As Boolean
       Dim ctl As Control

       blnCheckDiff = False
       For Each ctl In Me.Controls
           If ctl.Tag = "Check" And ctl.Value <> ctl.OldValue Then
              blnCheckDiff = True
           End If
       Next
       If blnCheckDiff Then
               [Date_Edited] = Now()
            
       Else
               Cancel = True
       End If
       End Sub[/COLOR]

And here is the After Update Code:

[COLOR="blue"]Private Sub Form_AfterUpdate()
Set db = CurrentDb
         db.Execute "INSERT INTO [tbl_MINERAL_MineralOwner EDITS] " _
         & " SELECT * FROM [tbl_MINERAL_MineralOwner] WHERE " _
         & " [tbl_MINERAL_MineralOwner].[MineralOwner_ID]=" & Me![MineralOwner_ID]
         Set db = Nothing
     End Sub[/COLOR]

Maybe this is closer to what you want.

Audit Trail