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

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

"sweeberry" wrote:

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

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" <(E-Mail Removed)> wrote in message
news:ABDBF491-90E5-4875-88B7-(E-Mail Removed)...
> 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])

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:ABDBF491-90E5-4875-88B7-(E-Mail Removed)...
> > 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])
>
>

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.

--
Steve Schapel, Microsoft Access MVP


"sweeberry" <(E-Mail Removed)> wrote in message
news:1BF41E2B-1A0A-4DCA-88A7-(E-Mail Removed)...
> 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".
>

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" wrote:

> 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.
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
> "sweeberry" <(E-Mail Removed)> wrote in message
> news:1BF41E2B-1A0A-4DCA-88A7-(E-Mail Removed)...
> > 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".
> >
>
>
>

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, Microsoft Access MVP


"sweeberry" <(E-Mail Removed)> wrote in message
news:B13EBA14-1392-420E-99DF-(E-Mail Removed)...
> 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.
>

Sweeberry,

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

--
Steve Schapel, Microsoft Access MVP


"sweeberry" <(E-Mail Removed)> wrote in message
news:B13EBA14-1392-420E-99DF-(E-Mail Removed)...
> 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.
>

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" wrote:

> 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" wrote:
>
> > 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" <(E-Mail Removed)> wrote in message
> > news:ABDBF491-90E5-4875-88B7-(E-Mail Removed)...
> > > 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,

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" <(E-Mail Removed)> wrote in message
news:E5421D50-4C12-4CBE-8397-(E-Mail Removed)...
> 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])