Subform Compare Value to Oldvalue

[CementCarver]

I'm attempting to use Martin Green's audit trail code, but I've run into a snag. I have a form with several tab pages and inside one of the tabs is a subform. During the beforeupdate event, the code fires to check whether any records have been changed.

Can someone see why this object doesn't support this property/method? Where do I find some reference material on subform objects and their properties?

But I'm getting an error "object does not support property or method" when I'm using the control's .oldvalue. Below is my code:

Case "Edit"

Set frmCurrentForm = Forms!frm_EDIT_CURRENT_FULLGROWER!Edit_Grower_Trac eability_Datasheet
For Each ctl In Forms("frm_EDIT_CURRENT_FULLGROWER").Controls("EDI T_GROWER_TRACEABILITY_DATASHEET").Form.Controls
'For Each ctl In Me.sfrmResponses.Form.Controls
TRACE_MEMBER_ID_new = Forms("frm_EDIT_CURRENT_FULLGROWER").Controls("EDI T_GROWER_TRACEABILITY_DATASHEET").Form.Controls("M EMBER_ID").Value
Debug.Print "Is There a new value for Trace Member :" & TRACE_MEMBER_ID_new
'TRACE_MEMBER_ID_OLD = Forms("frm_EDIT_CURRENT_FULLGROWER").Controls("EDI T_GROWER_TRACEABILITY_DATASHEET").Form.Controls("M EMBER_ID").OldValue -------line causing the error....

'Forms!Mainform!Subform1.Form!ControlName
'Me![S-Signings Subform].Form![Signing Status].OldValue


'TRACE_MEMBER_ID_OLD = Forms![Edit_Grower_Traceability_Datasheet].Form![MEMBER_ID].OldValue

If ctl.Tag = "Audit" Then
If ctl = Forms("frm_EDIT_CURRENT_FULLGROWER").Controls("EDI T_GROWER_TRACEABILITY_DATASHEET").Form.Controls("M EMBER_ID") Then
'Debug.Print " Action is = " & UserAction & vbNewLine & "Control Name = " & ctl.Name & vbNewLine & "Control Type = " & ctl.ControlType; vbNewLine & "Control Value = " & ctl.OldValue; vbNewLine & "New Value = " & subcontrolTraceCodeNew; vbNewLine & "Old Value = " & subcontrolTraceCodeOldvalue
If TRACE_MEMBER_ID_new <> TRACE_MEMBER_ID_OLD Then
With rstAudit
.AddNew
![DateTime] = datTimeCheck
![UserName] = strAccountName
'![FormName] = frmCurrentForm
![Action] = UserAction
![Record_ID] = MaxMember_id
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue


![NewValue] = ctl.Value
.Update
'Debug.Print "Current values " & UserAction
End With
'Debug.Print "This is the Spot Now = " & ctl.ControlSource

End If

End If
'nd If
End If
Next ctl

[ItsMe]

Have you tried going to your subform EDIT_GROWER_TRACEABILITY_DATASHEET and looking at the control properties of MEMBER_ID? It needs to be a text box. Double check that this control's edit properties are not locked or disabled. Not sure if this matters but...

Also, the code you posted does not state that this form is a form. And the description of the control has a space between the letter “m” and “e”.

Between “Controls” and “("EDI T_GROWER….” There is not the word "Form" and you have Controls("M EMBER_ID").
Controls("EDI T_GROWER_TRACEABILITY_DATASHEET").Form.Controls("M EMBER_ID").OldValue

Maybe you can double check for typos.

[CementCarver]

I've checked the field MEMBER_ID and it's enabled and a text box. The spaces in my code example do not exist in my original, but probably due to cutting/pasting.

CementCarver

[ItsMe]

Here are some of the ways I go about referencing a control in a subform. I noticed some of the code you pasted here indicates you have some lines commented out. Perhaps the examples below can help you understand how to get the code you pasted to work.




Examples of assigning a value to a control in a subform via VBA.

Forms!frmMainForm.frmSubForm!TextControl.Value = “The value of the textbox named TextControl in the sub form named frmSubForm”
or (if you are in the frmMainForm)
Me.frmSubForm!TextControl.Value = “The value of the textbox named TextControl in the sub form named frmSubForm”
or
Dim frmCurrentForm As Form
Set frmCurrentForm = Forms!frmMainForm
frmCurrentForm.frmSubForm!TextControl.Value = “The value of the textbox named TextControl in the sub form named frmSubForm”
or
Dim frmCurrentForm As Form
Set frmCurrentForm = Forms!frmMainForm
Dim ctlControl as Control
Set ctlControl = frmCurrentForm![TextControl]
ctlControl.Value = “The value of the textbox named TextControl in the sub form named frmSubForm”



Example referencing a control in a subform via a string

strMyString = Forms!frmMainForm.frmSubForm![TextControl]

[CementCarver]

Thanks ItsMe, your suggestions worked out fine.

CementCarver