Error: can't find the field ' ' referred to in your expression 1

[colinrharris]

I am getting the following error in Access 2007 when I open a form or move to another record in that form:
FormName can't find the field 'FieldName' referred to in your expression.
FormName is the form that is open and FieldName is actually the name of a subform - not a field.
I have searched through expressions, macros and code and cannot find the error.
The form works OK after the error is cleared.
Is there a way of locating the expression that is causing the error message rather than manually looking for it?

 

[TheAceMan1]

How are ya colinrharris . . .

Sounds like an improper [blue]subform reference[/blue].

Post any code in the forms [blue]On Current[/blue], [blue]On Load[/blue], or [blue]On Current[/blue] events.

See Ya! . . . . . .

Be sure to see thread181-473997

 

[colinrharris]

Thanks

This is attached to the On Current for the subform. There is no code attached to these events on the main form:

Sub Form_Current()

Dim ParentDocName As String

On Error Resume Next
ParentDocName = Me.Parent.Name

If Err <> 0 Then
GoTo Form_Current_Exit
Else
On Error GoTo Form_Current_Err
Me.Parent![Contacts Subform].Requery
End If

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit

End Sub

 

[TheAceMan1]

colinrharris . . .

Be aware: In a form/subform combination, the [blue]subform(s) open first![/blue] Your attempting to access the mainform which isn't available yet, as the subform is still opening (mainform not open yet).

Your initially (on open) going to have to set [blue]ParentDocName[/blue] from the mainform.

BTW: If [blue]Me.Parent![Contacts Subform].Requery[/blue] executes, I'd expect you to get stuck in a loop, as each requery causes the [blue]On Current[/blue] event to fire.

Also [blue]Me.Parent.Name[/blue] is the [blue]name of the mainform[/blue]. Are you sure this is what you want? I expect a control is sought here. Let me know.

Also is [blue][Contacts Subform][/blue] a seperate subform, or one and the same?

Anyway . . . to fix this:
[ol][li]In the [blue]Declaration Section[/blue] of the subform, copy/paste the following:

[blue]Private flgOpen As Boolean[/blue]

[/li]
[li]Copy/paste the following, overwriting the code in the [blue]On Current[/blue] event:

[blue]   Dim ParentDocName As String

   If flgopen Then
     ParentDocName = Me.Parent.Name
   Else
      flgopen = True
   End If[/blue]

[/li]
[li]In the same module, copy/paste the following routine:

[blue]Public Sub OnCurExt()
   Call Form_Current
End Sub[/blue]

[/li]
[li]Finally, in the [blue]On Load[/blue] event of the mainform, copy/paste the following:

[blue]   [[purple][b]yourSubformName[/b][/purple]].Form.OnCurExt[/blue]

[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[colinrharris]

The code was created automatically by dragging the sub form into the main form and also by dragging two other forms into the subform, creating nested subforms. All this worked perfectly until yesterday.
I really just gave myself the answer: I deleted all the existing code, deleted the subform from the mainform and re-created it. It all works perfectly again.
Thanks very much for your advice

 

[colinrharris]

I spoke too soon. The form now works perfectly until I close and re-open it, when it asks for a parameter value: Forms!CustomerRecords!CompanyID (CustomerRecords is the name of the main form and CompanyID is the primary key and related field. Having said that, it then opens correctly. On moving to another record, the same thing happens.
Strange - this has always worked for me in the past.
I am not good at VBA so be gentle

 

[colinrharris]

OK. I have had a go at interpreting your instructions but have obviously got it wrong. This is the code that is attached to the ON Current event on the subform:

Private flgOpen As Boolean
Private Sub Form_Current()
Dim CustomerRecords As String

If flgOpen Then
CustomerRecords = Me.CustomerRecords.Name
Else
flgOpen = True
End If
End Sub

Public Sub OnCurExt()
Call Form_Current
End Sub

This is entered in the ON Load event of the mainform:

[SiteSubform].Form.OnCurExt

The main form is called CustomerRecords and the subform is called SiteSubform. There are two subforms within SiteSubform but they are working OK.

When I open the main form, the following error message appears:

Customer Records can't find the object 'SiteSubform'.
If 'SiteSubform' is a new macro or macro group, make sure you have saved it and that you have typed its name correctly.

I am not sure what Customer Records, as mentioned in the error message, is as the main form is called CustomerRecords (without a space). There is no table, query or form called Customer Records.

All appears to work correctly after that except for a parameter value for SiteID is asked for a few times before correctly moving to only some records. SiteID is an autonumber field and is the many side of a relationship with a table called Company.

Can you throw any more light on this?

Thanks