#Name error when referencing a subform field

[Sandra Walsh]

Hello -

I am going crazy trying to sort out what should be a very straightforward reference to a field on a sub-form. There is an error in the WHERE clause of my query but I can't figure it out.

The hierarchy of my data is as follows:

Trips -> Activities -> Contacts

I have a main form called f_MainTripForm. On this Main form I have a sub-form called f_ActivityDetai ls where the user selects the Company they are visiting from a drop-down named Company_DD with a control source of Company_Listing _ID

The f_ActivityDetai ls sub-form has a tab control in which I have a sub-sub form named f_OtherFirmCont acts on one of the pages

f_MainTripForm
-> f_ActivityDetai ls joined on Trip_ID
->-> f_OtherFirmCont acts joined on Activity_ID


In f_OtherFirmCont acts I have a dropdown named SelectContact_D D that should select only the contacts that have a Company_ID = to the Listing_ID stored in the Company_DD drop-down in f_ActivityDetai ls.


I have this code for the SelectContact_D D drop down:

SELECT 
q_ContactsAtACompany.Cont_LISTING_ID, 
q_ContactsAtACompany.DISPLAY_NM, 
q_ContactsAtACompany.POSITION_NM, 
q_ContactsAtACompany.COMPANY_ID

FROM
q_ContactsAtACompany

WHERE (((q_ContactsAtACompany.COMPANY_ID)=[Forms]![f_MainTripForm]![f_ActivityDetails].[Form]![Company_DD]))

ORDER BY
q_ContactsAtACompany.DISPLAY_NM;

When I open the form, I get a request to Enter Parameter Value.

When I create an unbound field in f_Contacts with the Control Source

=[Forms]![f_MainTripForm]![f_ActivityDetails].[Form]![Company_DD]

I get a #Name error.

When I manually enter the COMPANY_ID in the Parameter Value request, all works fine.

Thanks for any suggestions - I am sure it is something simple to do with the syntax of the WHERE clause.

Sandra

[Sandra Walsh]

I figured it out.

I had just dragged my subforms onto the parent forms without creating a SubForm control, which means that the Name and the SourceObject had the same name.

Such a simple answer - too bad it took me a whole day to figure it out :-)

[Sandra Walsh]

Darn - I spoke too soon. Everything was working properly so I saved and re-opened my form and I am back to square one.



The Control Source of the unbound field within the f_ActivityDetai ls subform is now



=[Forms]![f_MainTripForm]![f_ActivityDetai ls_SFC].[Form]![Company_DD]



f_ActivityDetai ls is the Source Object within the f_ActivityDetai ls_SFC subform control.



f_OtherFirmCont acts is the Source Object within the f_OtherFirmCont acts_SFC subform control.



Thanks for any insight.

Sandra

[NeoPa]

It looks like you've tried hard to post a decently explained question here Sandra. That's always good news.

Why don't you check out Referring to Items on a Sub-Form. Let us know if this helps (or if you need more then explain that instead).

[Sandra Walsh]

Thanks NeoPa -

I have that page open and have been using it to figure out the syntax for the Control.

I believe that my syntax:

[Forms]![f_MainTripForm]![f_ActivityDetails_SFC].[Form]![Company_DD]

Follows your suggestion:

Forms![FormName]![SubFormName].Form![ControlName]

Do you see an error?
Sandra

[NeoPa]

I don't recall if the article mentions that references within SQL cannot use the dot notation. They only work with the bangs (!). Thus, this should probably be :

[Forms]![f_MainTripForm]![f_ActivityDetails_SFC]![Company_DD]

Assuming of course, that all the names used are correct, and I think that's a fair assumption.

This would be in the form :

Forms![FormName]![SubFormName]![ControlName]