I have used the following code from Microsoft Knowledge
base Article - 197592 successfully to record changes to
data on a form using the BeforeUpdate event and putting a
textbox called Updates on the form bound to the field
Updates in the table.

My problem is that it works well on the main form, but
does not seem to work on a subform even though I have
added the Updates field to the table the subform is bound
to and have an Updates textbox on the subform. Is there
some way I can make this work on subforms as well as the
main form?

Function AuditTrail()
On Error GoTo Err_Handler

Dim MyForm As Form, C As Control, xName As String
Set MyForm = Screen.ActiveForm

'Set date and current user if form has been updated.
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
"Changes made on " & Date & " by " & CurrentUser()
& ";"

'If new record, record it in audit trail and exit sub.
If MyForm.NewRecord = True Then
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
"New Record """
End If

'Check each data entry control for change and record
'old value of Control.
For Each C In MyForm.Controls

'Only check data entry type controls.
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox,
acOptionGroup
' Skip Updates field.
If C.Name <> "Updates" Then

' If control was previously Null, record "previous
If IsNull(C.OldValue) Or C.OldValue = "" Then
MyForm!Updates = MyForm!Updates & Chr(13) & _
Chr(10) & C.Name & "--previous value was blank"

' If control had previous value, record previous value.
ElseIf C.Value <> C.OldValue Then
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
C.Name & "==previous value was " & C.OldValue
End If
End If
End Select
Next C

TryNextC:
Exit Function

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

Change the AuditTrail() function so it accepts a reference to the form,
instead of relying on Screen.ActiveForm. That is:
Function AuditTrail(MyForm As Form)

The call it (step 7 of the k.b. article), by setting the form's Before
Update property to exactly this:
=AuditTrail([Form])

If you need to audit deletions as well, see:
Audit Trail - Log changes at the record level
at:
http://members.iinet.net.au/~allenbrowne/AppAudit.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Connie Gerregano" <(E-Mail Removed)> wrote in message
news:2461401c45f8d$b2a742b0$(E-Mail Removed)...
> I have used the following code from Microsoft Knowledge
> base Article - 197592 successfully to record changes to
> data on a form using the BeforeUpdate event and putting a
> textbox called Updates on the form bound to the field
> Updates in the table.
>
> My problem is that it works well on the main form, but
> does not seem to work on a subform even though I have
> added the Updates field to the table the subform is bound
> to and have an Updates textbox on the subform. Is there
> some way I can make this work on subforms as well as the
> main form?
>
> Function AuditTrail()
> On Error GoTo Err_Handler
>
> Dim MyForm As Form, C As Control, xName As String
> Set MyForm = Screen.ActiveForm
>
> 'Set date and current user if form has been updated.
> MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
> "Changes made on " & Date & " by " & CurrentUser()
> & ";"
>
> 'If new record, record it in audit trail and exit sub.
> If MyForm.NewRecord = True Then
> MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
> "New Record """
> End If
>
> 'Check each data entry control for change and record
> 'old value of Control.
> For Each C In MyForm.Controls
>
> 'Only check data entry type controls.
> Select Case C.ControlType
> Case acTextBox, acComboBox, acListBox,
> acOptionGroup
> ' Skip Updates field.
> If C.Name <> "Updates" Then
>
> ' If control was previously Null, record "previous
> If IsNull(C.OldValue) Or C.OldValue = "" Then
> MyForm!Updates = MyForm!Updates & Chr(13) & _
> Chr(10) & C.Name & "--previous value was blank"
>
> ' If control had previous value, record previous value.
> ElseIf C.Value <> C.OldValue Then
> MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
> C.Name & "==previous value was " & C.OldValue
> End If
> End If
> End Select
> Next C
>
> TryNextC:
> Exit Function
>
> Err_Handler:
> If Err.Number <> 64535 Then
> MsgBox "Error #: " & Err.Number & vbCrLf
> & "Description: " & Err.Description
> End If
> Resume TryNextC
> End Function