DLookup Problem Within A Subform

[sweeberry]

I have a subform which contains a Unit Price field that I would like to
automatically populate when the Item Number is selected. I have a table
"Products" that contains all of these values. I have a DLookup function that
I thought would generate the output I wanted but when I put it as the control
source for the field I need to be looked up nothing appears in the text box
or #Error appears. Here is what my DLookup looks like:
=DLookup("[Unit Price]", "Products", "[Item Number]" = Forms!Order
Form!Order Details Subform![Item Number])

 

[KARL DEWEY]

Instead of a lookup why not join the Products table i your query for subform?

 

[Steve Schapel]

Sweeberry,

You have the " in the wrong place. Plus you need to enclose the names of
the objects and controls that have spaces in their names with []s. Try it
like this:

=DLookup("[Unit Price]","Products","[Item Number]=[Forms]![Order
Form]![Order Details Subform]![Item Number]")

 

[sweeberry]

Thanks Steve,

Unfortunately this doesn't solve the issue - I am still getting a "#Error"
in the field I want to populate.

I have tried using this same expression as a Macro On Click, and as a VBA
event procedure, and have been unsuccessful. I always either get no result
at all or a "#Error".

Any thoughts?

Sweeberry,

You have the " in the wrong place. Plus you need to enclose the names of
the objects and controls that have spaces in their names with []s. Try it
like this:

=DLookup("[Unit Price]","Products","[Item Number]=[Forms]![Order
Form]![Order Details Subform]![Item Number]")

--
Steve Schapel, Microsoft Access MVP

I have a subform which contains a Unit Price field that I would like to
automatically populate when the Item Number is selected. I have a table
"Products" that contains all of these values. I have a DLookup function
that
I thought would generate the output I wanted but when I put it as the
control
source for the field I need to be looked up nothing appears in the text
box
or #Error appears. Here is what my DLookup looks like:
=DLookup("[Unit Price]", "Products", "[Item Number]" = Forms!Order
Form!Order Details Subform![Item Number])

 

[Steve Schapel]

Sweeberry,

I don't know what you mean about using the expression in a macro or VBA
procedure.

But anyway, the most likely cause of the problem is that the Name of the
control is the same as the name of one of the fields in the form's Record
Source table/query. Change the name of the textbox.

 

[sweeberry]

Steve,

I mean, I have written this same DLookup as a macro and assigned it to occur
On Click for the field I want populated with no result. I have also written
the VBA code as an On_Click event with no result.

And, I have already re-named the text box with no result.

I'm really stumped! I appreciate your help

 

[Steve Schapel]

Sweeberry,

I do not understand the concept of "written this same DLookup as a macro".
What is the macro? Can you tell us the actions you are using?

 

[Steve Schapel]

Sweeberry,

Here's another thought... Is your ItemNumber actually a number? I.e. is
this field a Number data type, or is it Text?

 

[sfisher973]

Steve Shapel introduced other errors. Assumming [Item Number] is a Number
Field, try the following:

=DLookup("[Unit Price]","Products","[Item Number] = " & [Forms]![Order
Form]![Order Details Subform]![Item Number])

--
-Steve

Thanks Steve,

Unfortunately this doesn't solve the issue - I am still getting a "#Error"
in the field I want to populate.

I have tried using this same expression as a Macro On Click, and as a VBA
event procedure, and have been unsuccessful. I always either get no result
at all or a "#Error".

Any thoughts?

Sweeberry,

You have the " in the wrong place. Plus you need to enclose the names of
the objects and controls that have spaces in their names with []s. Try it
like this:

=DLookup("[Unit Price]","Products","[Item Number]=[Forms]![Order
Form]![Order Details Subform]![Item Number]")

--
Steve Schapel, Microsoft Access MVP

I have a subform which contains a Unit Price field that I would like to
automatically populate when the Item Number is selected. I have a table
"Products" that contains all of these values. I have a DLookup function
that
I thought would generate the output I wanted but when I put it as the
control
source for the field I need to be looked up nothing appears in the text
box
or #Error appears. Here is what my DLookup looks like:
=DLookup("[Unit Price]", "Products", "[Item Number]" = Forms!Order
Form!Order Details Subform![Item Number])

 

[Steve Schapel]

Steve,

Yes, you can do it like that, if you like. Or you can do it the way I did
it. Either will be fine. I don't think the suggestion you introduced will
be relevant to Sweeberry's problem.

 

[Douglas J. Steele]

Actually, shouldn't that be

=DLookup("[Unit Price]","Products","[Item Number]=[Forms]![Order
Form]![Order Details Subform].Form![Item Number]")

or

=DLookup("[Unit Price]","Products","[Item Number] = " & [Forms]![Order
Form]![Order Details Subform].Form![Item Number])

I believe Access 2007 (for example) insists on the .Form

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Steve,

Yes, you can do it like that, if you like. Or you can do it the way I did
it. Either will be fine. I don't think the suggestion you introduced
will be relevant to Sweeberry's problem.

--
Steve Schapel, Microsoft Access MVP

Steve Shapel introduced other errors. Assumming [Item Number] is a Number
Field, try the following:

=DLookup("[Unit Price]","Products","[Item Number] = " & [Forms]![Order
Form]![Order Details Subform]![Item Number])

 

[sfisher973]

Hmmm, Sorry Steve, I may have jumped the gun on this... I always resolve the
value asap. I don't think I've actually tried it the other way... I rarely
use Dlookup anyway as it uses CurrentDB and I just about always need CodeDB
for the applications I work on.
--
-Steve

Steve,

Yes, you can do it like that, if you like. Or you can do it the way I did
it. Either will be fine. I don't think the suggestion you introduced will
be relevant to Sweeberry's problem.

--
Steve Schapel, Microsoft Access MVP

Steve Shapel introduced other errors. Assumming [Item Number] is a Number
Field, try the following:

=DLookup("[Unit Price]","Products","[Item Number] = " & [Forms]![Order
Form]![Order Details Subform]![Item Number])