By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,655 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,655 IT Pros & Developers. It's quick & easy.

#Name error when referencing a subform field

P: 62
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_ActivityDetails 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_ActivityDetails sub-form has a tab control in which I have a sub-sub form named f_OtherFirmContacts on one of the pages

-> f_ActivityDetails joined on Trip_ID
->-> f_OtherFirmContacts joined on Activity_ID

In f_OtherFirmContacts I have a dropdown named SelectContact_DD that should select only the contacts that have a Company_ID = to the Listing_ID stored in the Company_DD drop-down in f_ActivityDetails.

I have this code for the SelectContact_DD drop down:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. q_ContactsAtACompany.Cont_LISTING_ID, 
  3. q_ContactsAtACompany.DISPLAY_NM, 
  4. q_ContactsAtACompany.POSITION_NM, 
  5. q_ContactsAtACompany.COMPANY_ID
  7. FROM
  8. q_ContactsAtACompany
  10. WHERE (((q_ContactsAtACompany.COMPANY_ID)=[Forms]![f_MainTripForm]![f_ActivityDetails].[Form]![Company_DD]))
  12. ORDER BY
  13. 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
Expand|Select|Wrap|Line Numbers
  1. =[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.

Nov 18 '11 #1
Share this Question
Share on Google+
5 Replies

P: 62
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 :-)
Nov 18 '11 #2

P: 62
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_ActivityDetails subform is now


f_ActivityDetails is the Source Object within the f_ActivityDetails_SFC subform control.

f_OtherFirmContacts is the Source Object within the f_OtherFirmContacts_SFC subform control.

Thanks for any insight.

Nov 18 '11 #3

Expert Mod 15k+
P: 31,712
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).
Nov 18 '11 #4

P: 62
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:

Expand|Select|Wrap|Line Numbers
  1. [Forms]![f_MainTripForm]![f_ActivityDetails_SFC].[Form]![Company_DD]
Follows your suggestion:
Expand|Select|Wrap|Line Numbers
  1. Forms![FormName]![SubFormName].Form![ControlName] 
Do you see an error?
Nov 18 '11 #5

Expert Mod 15k+
P: 31,712
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 :

Expand|Select|Wrap|Line Numbers
  1. [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 :

Expand|Select|Wrap|Line Numbers
  1. Forms![FormName]![SubFormName]![ControlName]
Nov 18 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.