Remove For Each..Next (1 Viewer)

[tabitha]

I have a procedure I want to run each time a control box is updated, but the code I have is a For Each because it was made to run only once at the Form's After Update event. But when I do that, it doesn't pick up the change.

Anyone know how to properly remove a For Each - while still capturing the correct control being updated?

Sub AuditTrail(frm As Form, recordid As Control)
  Dim ctl As Control
  Dim varBefore As Variant
  Dim varAfter As Variant
  Dim strControlName, msg As String
  Dim strSQL As String
  'On Error GoTo ErrHandler

  For Each ctl In frm.Controls     ''''''''This is what I want to remove, 
          ''''''but still have it capture the modified control
    With ctl
    Select Case .ControlType
        Case acCheckBox, acComboBox, acListBox, acOptionButton, acOptionGroup, acTextBox
      If (.Value <> .OldValue) Or ((Not IsNull(.OldValue) _
      And IsNull(.Value))) Or ((IsNull(.OldValue) And Not IsNull(.Value))) Then
        varBefore = .OldValue
        varAfter = .Value
        strControlName = .Name
        strSQL = "INSERT INTO " _
           & "Audit (EditDate, User, RecordID, SourceTable, " _
           & " SourceField, BeforeValue, AfterValue) " _
           & "VALUES (Now()," _
           & cDQ & Environ("username") & cDQ & ", " _
           & cDQ & recordid.Value & cDQ & ", " _
           & cDQ & frm.RecordSource & cDQ & ", " _
           & cDQ & .Name & cDQ & ", " _
           & cDQ & varBefore & cDQ & ", " _
           & cDQ & varAfter & cDQ & ")"
        Debug.Print strSQL
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
      End If
    End Select
    End With
  Next
  Set ctl = Nothing
  Exit Sub

'ErrHandler:
  'MsgBox Err.Description & vbNewLine _
   '& Err.Number, vbOKOnly, "Error"
End Sub

 

[gemma-the-husky]

just

set ctl = controls("yourcontrolname")

then you can leave the rest unchanged, and remove the loop.

 

[tabitha]

I want to use the same code for multiple controls, so would just changing the function name to AuditTrail(frm As Form, ctl As Control, recordid As Control), then when I call it: AuditTrail(MyForm,MyControl,[ID]) suffice? Even though that means having to change it on every After Update event?

 

[arnelgp]

convert it into a function and call it in each control's before update event.

=AuditTrail(Me.Form, [ctrlName])

Public Function AuditTrail(frm As Form, ctl As Control)
    '  Dim ctl As Control
    Dim varBefore As Variant
    Dim varAfter As Variant
    Dim strControlName, msg As String
    Dim strSQL As String
    'On Error GoTo ErrHandler

    With ctl
        Select Case .ControlType
        Case acCheckBox, acComboBox, acListBox, acOptionButton, acOptionGroup, acTextBox
            If (.value <> .OldValue) Or ((Not IsNull(.OldValue) _
                And IsNull(.value))) Or ((IsNull(.OldValue) And Not IsNull(.value))) Then
                varBefore = .OldValue
                varAfter = .value
                strControlName = .Name
                strSQL = "INSERT INTO " _
                & "Audit (EditDate, User, RecordID, SourceTable, " _
                & " SourceField, BeforeValue, AfterValue) " _
                & "VALUES (Now()," _
                & cDQ & Environ("username") & cDQ & ", " _
                & cDQ & ctl.value & cDQ & ", " _
                & cDQ & frm.RecordSource & cDQ & ", " _
                & cDQ & .Name & cDQ & ", " _
                & cDQ & varBefore & cDQ & ", " _
                & cDQ & varAfter & cDQ & ")"
                Debug.Print strSQL
                DoCmd.SetWarnings False
                DoCmd.RunSQL strSQL
                DoCmd.SetWarnings True
            End If
    End Select
    End With
    Exit Function

'ErrHandler:
  'MsgBox Err.Description & vbNewLine _
   '& Err.Number, vbOKOnly, "Error"
End Function

 

[The_Doc_Man]

If the control has focus at the time it is being updated manually, you can determine its name with Screen.ActiveControl.Name

If the control is being updated behind the scenes by code and some other control has focus, this won't work.

 

[tabitha]

@arnelgp
I'm a little confused; I'm not sure why you changed the recordid to ctl - I need both. The recordid is the employee's ID, and the ctl is the name of the control that is being changed (ie Address1). So

 & cDQ & ctl.value & cDQ & ", " _

still needs to be

& cDQ & recordid.value & cDQ & ", " _

and the ctl is what gives us the varBefore and varAfter.

Unless I'm wrong, can't I just leave that as recordid, and just adding ctl as Control to the function name should work?

 

[tabitha]

If the control has focus at the time it is being updated manually, you can determine its name with Screen.ActiveControl.Name

If the control is being updated behind the scenes by code and some other control has focus, this won't work.

So where it says "With ctl
Select Case ..."
I could replace that with "With Screen.ActiveControl
Select Case ..." ?

 

[arnelgp]

ooooops, sorry, missed that

=AuditTrail(Me.Form, [ctrlName], [recordIDControl])

Public Function AuditTrail(frm As Form, ctl As Control, recordid as Control)
    '  Dim ctl As Control
    Dim varBefore As Variant
    Dim varAfter As Variant
    Dim strControlName, msg As String
    Dim strSQL As String
    'On Error GoTo ErrHandler

    With ctl
        Select Case .ControlType
        Case acCheckBox, acComboBox, acListBox, acOptionButton, acOptionGroup, acTextBox
            If (.value <> .OldValue) Or ((Not IsNull(.OldValue) _
                And IsNull(.value))) Or ((IsNull(.OldValue) And Not IsNull(.value))) Then
                varBefore = .OldValue
                varAfter = .value
                strControlName = .Name
                strSQL = "INSERT INTO " _
                & "Audit (EditDate, User, RecordID, SourceTable, " _
                & " SourceField, BeforeValue, AfterValue) " _
                & "VALUES (Now()," _
                & cDQ & Environ("username") & cDQ & ", " _
                & cDQ & recordid.value & cDQ & ", " _
                & cDQ & frm.RecordSource & cDQ & ", " _
                & cDQ & .Name & cDQ & ", " _
                & cDQ & varBefore & cDQ & ", " _
                & cDQ & varAfter & cDQ & ")"
                Debug.Print strSQL
                DoCmd.SetWarnings False
                DoCmd.RunSQL strSQL
                DoCmd.SetWarnings True
            End If
    End Select
    End With
    Exit Function

'ErrHandler:
  'MsgBox Err.Description & vbNewLine _
   '& Err.Number, vbOKOnly, "Error"
End Function

 

[tabitha]

Great! That did it!! Thanks!!