Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

dlookup in subform

Discussion in 'Information Technology' started by confused, Jul 28, 2006.

  1. confused

    confused
    Expand Collapse
    Guest

    I am relatively new to this. I created a form with a subform. The form is
    for a transaction table and the subform is a detail table for the
    transaction. I am able to get the dlookup function to work properly on the
    main form to lookup the customers name, address etc. I want to look up the
    description in a products table in the detail section but I get an error.
    If I open the detail form by itself (not as a subform) the dlookup function
    looks up the proper information but as a sub form it errors out.

    Here are the two ways I have tried. Help Please!.
    =DLookUp("[pdesc]","[master]","[master]![item]=forms![detail]![item]")

    =DLookUp("[pdesc]","[master]","[master]![item]=forms![invoice]![detaill]![item]")

    Pdesc is in the Master table (products table) and contains the product
    description. Item is the item number in the master table and detail table to
    identify the product. Inovice is the main form and detail is the subform.

    Thanks!
    Stumped in NJ.
     
  2. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Confused,

    Try it like this...
    =DLookUp("[pdesc]","[master]","[item]=" & [item])

    For a discussion of alternative approaches, see this article...
    http://accesstips.datamanagementsolutions.biz/lookup.htm

    --
    Steve Schapel, Microsoft Access MVP

    confused wrote:
    > I am relatively new to this. I created a form with a subform. The form is
    > for a transaction table and the subform is a detail table for the
    > transaction. I am able to get the dlookup function to work properly on the
    > main form to lookup the customers name, address etc. I want to look up the
    > description in a products table in the detail section but I get an error.
    > If I open the detail form by itself (not as a subform) the dlookup function
    > looks up the proper information but as a sub form it errors out.
    >
    > Here are the two ways I have tried. Help Please!.
    > =DLookUp("[pdesc]","[master]","[master]![item]=forms![detail]![item]")
    >
    > =DLookUp("[pdesc]","[master]","[master]![item]=forms![invoice]![detaill]![item]")
    >
    > Pdesc is in the Master table (products table) and contains the product
    > description. Item is the item number in the master table and detail table to
    > identify the product. Inovice is the main form and detail is the subform.
    >
    > Thanks!
    > Stumped in NJ.
    >
    >
     
  3. confused

    confused
    Expand Collapse
    Guest

    That did not work as a subform nor did it work as a stand alone form. The
    examples in the link seem to relate to forms and none with subforms. I
    tried briefly to create a query but had problems with the ambiguious joins
    and resolved them but then it was not updatable. Should this work in a
    subform or is there a different way to proceed using vba and variables? Any
    ideas. Thanks for your help.
    even more confused.
    "Steve Schapel" <schapel@mvps.org.ns> wrote in message
    news:uepEyqmiGHA.3900@TK2MSFTNGP05.phx.gbl...
    > Confused,
    >
    > Try it like this...
    > =DLookUp("[pdesc]","[master]","[item]=" & [item])
    >
    > For a discussion of alternative approaches, see this article...
    > http://accesstips.datamanagementsolutions.biz/lookup.htm
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > confused wrote:
    >> I am relatively new to this. I created a form with a subform. The form
    >> is for a transaction table and the subform is a detail table for the
    >> transaction. I am able to get the dlookup function to work properly on
    >> the main form to lookup the customers name, address etc. I want to look
    >> up the description in a products table in the detail section but I get an
    >> error. If I open the detail form by itself (not as a subform) the dlookup
    >> function looks up the proper information but as a sub form it errors out.
    >>
    >> Here are the two ways I have tried. Help Please!.
    >> =DLookUp("[pdesc]","[master]","[master]![item]=forms![detail]![item]")
    >>
    >> =DLookUp("[pdesc]","[master]","[master]![item]=forms![invoice]![detaill]![item]")
    >>
    >> Pdesc is in the Master table (products table) and contains the product
    >> description. Item is the item number in the master table and detail table
    >> to identify the product. Inovice is the main form and detail is the
    >> subform.
    >>
    >> Thanks!
    >> Stumped in NJ.
     
  4. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Even more,

    "Did not work" doesn't give me an awful lot to go on. Form or subform
    shouldn't make any difference. In your initial post you said "item is
    the item number", so I assumed it was a number. If it's a Text data
    type field, you will need more like this...
    =DLookUp("[pdesc]","[master]","[item]='" & [item] & "'")

    If you can post the SQL view of the query you tried, I can see if I can
    spot why it is not updateable. Probably because the joining field is
    not unique.

    --
    Steve Schapel, Microsoft Access MVP

    confused wrote:
    > That did not work as a subform nor did it work as a stand alone form. The
    > examples in the link seem to relate to forms and none with subforms. I
    > tried briefly to create a query but had problems with the ambiguious joins
    > and resolved them but then it was not updatable. Should this work in a
    > subform or is there a different way to proceed using vba and variables? Any
    > ideas. Thanks for your help.
    > even more confused.
     
  5. confused

    confused
    Expand Collapse
    Guest

    I am sorry, it is a text field. Unfortunately I need to leave and do not
    have time to change this. The linking fields are not unique and I an
    working on changing the data structure to make it unique, however, I want to
    try this. Thank you very much and sorry for the confusion on the data type.
    I will let you know, hopefully I will have a chance to work on it later this
    evening.
    "Steve Schapel" <schapel@mvps.org.ns> wrote in message
    news:O56TiRziGHA.4504@TK2MSFTNGP03.phx.gbl...
    > Even more,
    >
    > "Did not work" doesn't give me an awful lot to go on. Form or subform
    > shouldn't make any difference. In your initial post you said "item is
    > the item number", so I assumed it was a number. If it's a Text data type
    > field, you will need more like this...
    > =DLookUp("[pdesc]","[master]","[item]='" & [item] & "'")
    >
    > If you can post the SQL view of the query you tried, I can see if I can
    > spot why it is not updateable. Probably because the joining field is not
    > unique.
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > confused wrote:
    >> That did not work as a subform nor did it work as a stand alone form.
    >> The examples in the link seem to relate to forms and none with subforms.
    >> I tried briefly to create a query but had problems with the ambiguious
    >> joins and resolved them but then it was not updatable. Should this work
    >> in a subform or is there a different way to proceed using vba and
    >> variables? Any ideas. Thanks for your help.
    >> even more confused.
     
  6. confused

    confused
    Expand Collapse
    Guest

    Dear steve,
    Thank you very much that worked. I am not sure why however.Could you
    explain it or is there a resource that I could read as to know why that was
    the correct syntax?
    thanks again.
    Not as confused, David
    "confused" <daviddonotspam@officebargains.net> wrote in message
    news:Fn0ig.10199$nV4.3599@trndny03...
    >I am sorry, it is a text field. Unfortunately I need to leave and do not
    >have time to change this. The linking fields are not unique and I an
    >working on changing the data structure to make it unique, however, I want
    >to try this. Thank you very much and sorry for the confusion on the data
    >type. I will let you know, hopefully I will have a chance to work on it
    >later this evening.
    > "Steve Schapel" <schapel@mvps.org.ns> wrote in message
    > news:O56TiRziGHA.4504@TK2MSFTNGP03.phx.gbl...
    >> Even more,
    >>
    >> "Did not work" doesn't give me an awful lot to go on. Form or subform
    >> shouldn't make any difference. In your initial post you said "item is
    >> the item number", so I assumed it was a number. If it's a Text data type
    >> field, you will need more like this...
    >> =DLookUp("[pdesc]","[master]","[item]='" & [item] & "'")
    >>
    >> If you can post the SQL view of the query you tried, I can see if I can
    >> spot why it is not updateable. Probably because the joining field is not
    >> unique.
    >>
    >> --
    >> Steve Schapel, Microsoft Access MVP
    >>
    >> confused wrote:
    >>> That did not work as a subform nor did it work as a stand alone form.
    >>> The examples in the link seem to relate to forms and none with subforms.
    >>> I tried briefly to create a query but had problems with the ambiguious
    >>> joins and resolved them but then it was not updatable. Should this work
    >>> in a subform or is there a different way to proceed using vba and
    >>> variables? Any ideas. Thanks for your help.
    >>> even more confused.

    >
    >
     
  7. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    David,

    Pleased to hear that we made some progress here.

    You are referring to the value of the 'Item' field in the same form as
    the DLookup function is being used. So it's just "itself", whether it's
    a parent form or a subform, it's still just "itself", so no need for any
    reference to the name of the form. Just in case you were referring from
    a subform to the value of a field on the main form, you would use the
    Parent! keyword. And just in case you were referring from the main form
    to the value of a field in the subform, you would just refer directly to
    the subform control, e.g. [NameOfSubform]![Item] (or, as some would
    prefer it, [NameOfSubform].[Form]![Item]). But neither of these is the
    case.

    So, if you put an unbound textbox on the subform, and in its Control
    Source you put:
    =[item]
    and then look at the form view, you will see that the value of the Item
    field in the current record will be shown. Lets say it is 1234. So,
    within the context of that subform, [item] evaluates to 1234. So to
    extend the logic...
    DLookUp("[pdesc]","[master]","[item]='" & [item] & "'")
    evaluates to...
    DLookUp("[pdesc]","[master]","[item]='1234'")
    .... which is exactly what you would put if you wanted to "hard-code" the
    value of the item you were looking up.
    And then, put the = in front of it, so that the DLookup() function
    evaluates to return the corresponding pdesc value.

    Well, not easy to explain, but hope that clarifies to an extent. I
    don't really have any specific references to point you to, but "Access
    2003 Inside Out" by John Viescas is a great general purpose book.

    --
    Steve Schapel, Microsoft Access MVP

    confused wrote:
    > Dear steve,
    > Thank you very much that worked. I am not sure why however.Could you
    > explain it or is there a resource that I could read as to know why that was
    > the correct syntax?
    > thanks again.
    > Not as confused, David
     

Share This Page