#Name error when referencing a subform field

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Sandra Walsh
    New Member
    • Nov 2011
    • 62

    #Name error when referencing a subform field

    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:

    Code:
    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
    Code:
    =[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
    New Member
    • Nov 2011
    • 62

    #2
    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 :-)

    Comment

    • Sandra Walsh
      New Member
      • Nov 2011
      • 62

      #3
      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

      Comment

      • NeoPa
        Recognized Expert Moderator MVP
        • Oct 2006
        • 32634

        #4
        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).

        Comment

        • Sandra Walsh
          New Member
          • Nov 2011
          • 62

          #5
          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:

          Code:
          [Forms]![f_MainTripForm]![f_ActivityDetails_SFC].[Form]![Company_DD]
          Follows your suggestion:
          Code:
          Forms![FormName]![SubFormName].Form![ControlName]
          Do you see an error?
          Sandra

          Comment

          • NeoPa
            Recognized Expert Moderator MVP
            • Oct 2006
            • 32634

            #6
            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 :

            Code:
            [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 :

            Code:
            Forms![FormName]![SubFormName]![ControlName]

            Comment

            Working...