DLookup Problem Within A Subform

S

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])
 
S

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]")
 
S

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?

Steve Schapel said:
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


sweeberry said:
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])
 
S

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.
 
S

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 :)
 
S

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?
 
S

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?
 
S

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


sweeberry said:
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?

Steve Schapel said:
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


sweeberry said:
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])
 
S

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.
 
D

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 Schapel said:
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


sfisher973 said:
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])
 
S

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 Schapel said:
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


sfisher973 said:
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])
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top