Problem with screen.activeform

[Stuart Jack via AccessMonster.com]

Hi

I have a main form with a subform containing fields of data, I am trying to
create an audit trail system using the code below from the microsoft kb on
audit trail but as the code uses the screen.activeform method and my data
field are on the subform it says it cannot find the 'updates' field which it
is supposed to insert the changes to.

Code-------------

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
' value was blank."
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 IIF(IsNull(C.Value),"",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

end ------------------------

kb website link.

"How to create an audit trail of record changes in a form in Access 2000"
http://support.microsoft.com/?kbid=197592

can anyone suggest how I might get it to look at the subform and not the main
form?

Thanks

Stuart

 

[Guest]

You must use subform's Form property, like this:

Forms!YourMainForm!YourSubform.Form.[your property]

E.g.:
If MyForm!MySubform.Form.NewRecord = True Then
MyForm!MySubform.Form!Updates = etc...

--
Luiz Cláudio C. V. Rocha
Coordenador de Projetos FórumAccess
São Paulo - Brasil
MVP Office
http://www.msmvps.com/officedev

 

[Douglas J. Steele]

Note, too, that depending on how you added the subform to the main form, the
name of the subform control on the parent form may be different than the
name of the form being used as the subform. You need the name of the subform
control:

Forms!YourMainForm!SubformControlOnParentForm.Form.[your property]


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

You must use subform's Form property, like this:

Forms!YourMainForm!YourSubform.Form.[your property]

E.g.:
If MyForm!MySubform.Form.NewRecord = True Then
MyForm!MySubform.Form!Updates = etc...

--
Luiz Cláudio C. V. Rocha
Coordenador de Projetos FórumAccess
São Paulo - Brasil
MVP Office
http://www.msmvps.com/officedev

Hi

I have a main form with a subform containing fields of data, I am trying
to
create an audit trail system using the code below from the microsoft kb
on
audit trail but as the code uses the screen.activeform method and my data
field are on the subform it says it cannot find the 'updates' field which
it
is supposed to insert the changes to.

Code-------------

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
' value was blank."
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 IIF(IsNull(C.Value),"",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

end ------------------------

kb website link.

"How to create an audit trail of record changes in a form in Access 2000"
http://support.microsoft.com/?kbid=197592

can anyone suggest how I might get it to look at the subform and not the
main
form?

Thanks

Stuart

 

[Stuart Jack via AccessMonster.com]

Hi Luiz

Thanks for the info, I will look at how my subform is named/referrenced etc.

Thanks again

Stuart

You must use subform's Form property, like this:

Forms!YourMainForm!YourSubform.Form.[your property]

E.g.:
If MyForm!MySubform.Form.NewRecord = True Then
MyForm!MySubform.Form!Updates = etc...

Hi

[quoted text clipped - 70 lines]

Stuart