Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    7

    Question Unanswered: Dlookup not working in subform

    Dlookup works in form, but not in subform.

    In the form called Item Subform , I have a combo box that uses both fields from the table L-Property-ClassList (see below). The name of the combo box is PropertyClassNumb (I made it different from the field name in the table for my own clarity).

    I have a text box on the form Item Subform that is called PropertyClassDesc (again, I made it different from the field name in the table for my own clarity).
    In it’s Control Source I have:
    =DLookUp("[L-Property-ClassList].[PropertyClassName]","L-Property-ClassList","[L-Property-ClassList].[PropertyClassNumber]=" & [Forms]![Item Subform]![PropertyClassNumb])

    Table L-Property-ClassList
    PropertyClassNumber (primary key number) 1,2,3,etc
    PropertyClassName (text) Currency, Jewelry, Motor Vehicles, etc.

    When I open Item Subform , the PropertyClassDesc displays perfectly. When I change PropertyClassNumber, the PropertyClassDesc updates correctly.

    Next, I added Item Subform as a subform to another form called Incident. When I open the form Incident (with Item Subform as it's subform), PropertyClassDesc shows only #Name! in the subform area.
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Posts
    142
    Try this,

    [Forms]![Incident]![Item Subform].[form]![PropertyClassNumb]

    To reference a subform control within a form using a domain aggregate or in any SQL in controls, etc. you need to drill down to the subform reference explicitly.
    KC

  3. #3
    Join Date
    Jan 2004
    Posts
    7
    Thanks for the reply.

    I tried it, but now I get #Name? in both the form and in the subform.

    Let me make sure I understand where to put the change
    [Forms]![Incident]![Item Subform].[form]![PropertyClassNumb]

    I opened the Item Subform form modified the text box called PropertyClassDesc. Before in the Control Source I had:
    =DLookUp("[L-Property-ClassList].[PropertyClassName]","L-Property-ClassList","[L-Property-ClassList].[PropertyClassNumber]=" & [Forms]![Item Subform]![PropertyClassNumb])

    Using your suggestion above, I changed it to =DLookUp("[L-Property-ClassList].[PropertyClassName]","L-Property-ClassList","[L-Property-ClassList].[PropertyClassNumber]=" & [Forms]![Incident]![Item Subform].[form]![PropertyClassNumb].)

    Note: when I viewed my form, then came back into design mode, the Control Source was changed to
    =DLookUp("[L-Property-ClassList].[PropertyClassName]","L-Property-ClassList","[L-Property-ClassList].[PropertyClassNumber]=" & [Forms]!Incident![Item Subform].Form!PropertyClassNumb)--ACCESS took the square brackets away from [PropertyClassNumb]

  4. #4
    Join Date
    Jan 2004
    Posts
    7
    In searching the internet, I found suggestions on using ME to indicate that a control is on the current form. I tried using this for the subform, but that isn't working either.

  5. #5
    Join Date
    Feb 2009
    Posts
    1
    It needs to read this way

    =DLookUp("[PropertyClassName]","L-Property-ClassList","[PropertyClassNumber]=[Forms]![Incident]![Item Subform].[Form]![PropertyClassNumb]")

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •