Problem with Audit code for Null/Blank records - SOLVED

[StuLux]

I am using the following code (adapted from the Microsoft article on Access Audit trail).

The code writes values to text boxes (txtMeasure, txtOldData, txtNewData) on the form and then the query "qAudit" uses the values from these text boxes to append to an audit table. A record is not created when the existing value is Blank or Null, I have tried many combinations of code to get this to happen but am failing somewhere? My latest attempt is indicated below.

Function Audit()
On Err GoTo Err_Handler

    Dim myForm As Form, ctl As Control
    Set myForm = Screen.ActiveForm
    
'Check each data entry control for change and record old value of Control
    For Each ctl In myForm.Controls
'Only check data entry type controls.
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
'Skip fields used to collate changes
        If ctl.Name <> "txtMeasure" Or ctl.Name <> "txtOldData" Or _
        ctl.Name <> "txtNewData" Then
        
        myForm!txtMeasure = ctl.Name 'Applys the name of the ctl to txtMeasure

'If control had previous value, record previous value
            If ctl.Value <> ctl.OldValue Then
                myForm!txtOldData = ctl.OldValue

If IsNull(ctl.OldValue) Or ctl.OldValue = "" Then myForm!txtOldData = "Blank" 'This is the line I am trying to get to work

        myForm!txtNewData = ctl.Value 'Applys new value to txtNewData

'Runs the qAudit append query to create a new record in tblAUDIT
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qAudit"
        DoCmd.SetWarnings True
            End If
        End If
        End Select
    Next ctl

trynextctl:
    Exit Function

Err_Handler:
    If Err.Number <> 64535 Then
        MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " & Err.Description
    End If
    Resume trynextctl:
End Function

 

[mdmilner]

Awesome - I wish I could claim it was my diabolical plan to make you work a bit. Really, I slacked off and failed to give you a completely well thought out solution.

I'm really glad you were able to figure out the logic you needed to make it work. It actually is much better when you churn through it and do it yourself - much better understanding of what it's actually doing and it's a lot like the old "give a man a fix" vs "teach a man to fish" analogy.

Mike

 

[Derhum17]

Bumped because I have a question on this.

I took StuLux's modification and have it working great, however, I noticed that it does not capture changes made to controls on subforms, only those changes made to a main form.

I tried the following changes to see if it would remedy the problem:

Set myForm = Screen.ActiveForm.OnGotFocus

and

Set myForm = Screen.ActiveForm.SetFocus

However, neither of these worked. Anyone have an idea of how to modify the function to accomplish this?

Thanks in advance for any advice.