dlookup in subform

[confused]

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.

 

[Steve Schapel]

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

 

[confused]

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.

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

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.

 

[Steve Schapel]

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.

 

[confused]

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.

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

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.

 

[confused]

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

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.

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

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]

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.