DLookup Help Needed

  • Thread starter Thread starter Jody
  • Start date Start date
J

Jody

I am trying to autofill a form based on a drop down menu. I want the price
and description of an item to autofill. I have looked through all the
posting, tried them, and still cannot get the darn think to work. Here is
what I have:

=DLookUp("[ContainerSize]","Chemicals","[ProductName] = ' " & [ProductName]
& "'")

I am trying to look up the Price, in the Chemicals table, based on the
ProductName selected in the current form.

Any help would be appreciated.
 
I am trying to autofill a form based on a drop down menu. I want the price
and description of an item to autofill. I have looked through all the
posting, tried them, and still cannot get the darn think to work. Here is
what I have:

=DLookUp("[ContainerSize]","Chemicals","[ProductName] = ' " & [ProductName]
& "'")

I am trying to look up the Price, in the Chemicals table, based on the
ProductName selected in the current form.

Any help would be appreciated.
Click to expand...

Is the price stored in the field called ContainerSize??? Because
that's what your DLookUp is returning.

You say that it doesn't work; that's not much help here, because it
doesn't tell us HOW it "doesn't work". Do you get an error message, no
data, wrong data (e.g. the containersize instead of the price!), or
what?
 
Jody

If you are saying that you have a table with ProductName, Price and
Description, use a query against that table to return all three.

Then, in your form, add a combobox that uses the query. Since the combobox
will only "hold" (display) one of them, add a couple text boxes to hold the
other two and use an AfterUpdate procedure for the combobox to "fill" them,
something like:

Me!txtPrice = Me!cboProduct.Column(1)
Me!txtDescription = Me!cboProduct.Column(2)

Your syntax may vary ... use your controls' names ... check Access HELP for
use of .Column(n) (which uses zero-based counting).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Sorry, containersize should be price.

It doesn't give any data at all. It leaves the text box empty.

John W. Vinson/MVP said:
I am trying to autofill a form based on a drop down menu. I want the price
and description of an item to autofill. I have looked through all the
posting, tried them, and still cannot get the darn think to work. Here is
what I have:

=DLookUp("[ContainerSize]","Chemicals","[ProductName] = ' " & [ProductName]
& "'")

I am trying to look up the Price, in the Chemicals table, based on the
ProductName selected in the current form.

Any help would be appreciated.
Click to expand...

Is the price stored in the field called ContainerSize??? Because
that's what your DLookUp is returning.

You say that it doesn't work; that's not much help here, because it
doesn't tell us HOW it "doesn't work". Do you get an error message, no
data, wrong data (e.g. the containersize instead of the price!), or
what?
Click to expand...
 
Are you getting some error when you try?

I am trying to autofill a form based on a drop down menu. I want the price
and description of an item to autofill. I have looked through all the
posting, tried them, and still cannot get the darn think to work. Here is
what I have:

=DLookUp("[ContainerSize]","Chemicals","[ProductName] = ' " & [ProductName]
& "'")

I am trying to look up the Price, in the Chemicals table, based on the
ProductName selected in the current form.

Any help would be appreciated.
Click to expand...
Lynn Trapp
www.ltcomputerdesigns.com
 
It doesn't do anything. Also, Containersize should be price. I messed up
copying over to the post.

Any ideas on what can be wrong? I have been at this for about 3 days.

Lynn Trapp said:
Are you getting some error when you try?

I am trying to autofill a form based on a drop down menu. I want the price
and description of an item to autofill. I have looked through all the
posting, tried them, and still cannot get the darn think to work. Here is
what I have:

=DLookUp("[ContainerSize]","Chemicals","[ProductName] = ' " & [ProductName]
& "'")

I am trying to look up the Price, in the Chemicals table, based on the
ProductName selected in the current form.

Any help would be appreciated.
Click to expand...
Lynn Trapp
www.ltcomputerdesigns.com
Click to expand...
 
Jody said:
It doesn't do anything. Also, Containersize should be price. I messed
up copying over to the post.

Any ideas on what can be wrong? I have been at this for about 3 days.
Click to expand...

Your WHERE clause is not matching any records. Did you intend for that
leading space after the quote to be there?
 
Jody said:
It still doesnt work when I get rid of the space.
Click to expand...

Then simplify as a test...

Does this work?

=DLookUp("[ContainerSize]","Chemicals")

If it does then you know the WHERE clause is the problem. Then try...

=DLookUp("[ContainerSize]","Chemicals","[ProductName] = 'SomeValue'")

....where SomeValue is a hard-coded value that you know exists in the table.
If that works then your [ProductName] does not match a value in the table.
 
Okay, they both work. THe first expresion returns the first price in the
Chemicals table.

The second expresion also works for a set value.

I've checked all the names. Everything there looks good. The drop down menu
in the form is set up as a lookup column in the orders table. Is that wrong
to do? These chemical names are so long I am afraid that they will be typed
in wrong, so I thought using the lookup column would be the easiest way to do
this. Could that be causing a problem?
 
Jody

What do you mean by "lookup column"? Is that something you are doing
directly in the table? If so, bear in mind that Access tables are great
places to store data, but are NOT great ways to display it ... that's what
forms are for (and reports for printed display).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jody said:
Okay, they both work. THe first expresion returns the first price in the
Chemicals table.

The second expresion also works for a set value.

I've checked all the names. Everything there looks good. The drop down
menu
in the form is set up as a lookup column in the orders table. Is that
wrong
to do? These chemical names are so long I am afraid that they will be
typed
in wrong, so I thought using the lookup column would be the easiest way to
do
this. Could that be causing a problem?

Jody said:
I am trying to autofill a form based on a drop down menu. I want the
price
and description of an item to autofill. I have looked through all the
posting, tried them, and still cannot get the darn think to work. Here is
what I have:

=DLookUp("[ContainerSize]","Chemicals","[ProductName] = ' " &
[ProductName]
& "'")

I am trying to look up the Price, in the Chemicals table, based on the
ProductName selected in the current form.

Any help would be appreciated.
Click to expand...
Click to expand...
 
In an effort to try something different, I switched the the lookup from Price
to Product Name. When I manually enter the price, the product name will
appear. I used the same expression as I did last time, only I changed the
'Price' and 'ProductName' around.

I would go with this,but the chemicals have mulitple names with the same
price. It seems the drop down menu is the problem here. I would prefer the
user not have to type in the chemical name. ANy suggestions on what to use to
make the drop down menu and the Dlookup work nicely together?
 
If you go into the table and try to add a column, there is Insert, Lookup
(this is the one I used), delete and rename column.

I used the lookup column in the orders table so that the user would not have
to type in the long, hard to spell chemical name. I am sure there is probably
a better way to do this. Can you point me in that direction please?

Jeff Boyce said:
Jody

What do you mean by "lookup column"? Is that something you are doing
directly in the table? If so, bear in mind that Access tables are great
places to store data, but are NOT great ways to display it ... that's what
forms are for (and reports for printed display).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jody said:
Okay, they both work. THe first expresion returns the first price in the
Chemicals table.

The second expresion also works for a set value.

I've checked all the names. Everything there looks good. The drop down
menu
in the form is set up as a lookup column in the orders table. Is that
wrong
to do? These chemical names are so long I am afraid that they will be
typed
in wrong, so I thought using the lookup column would be the easiest way to
do
this. Could that be causing a problem?

Jody said:
I am trying to autofill a form based on a drop down menu. I want the
price
and description of an item to autofill. I have looked through all the
posting, tried them, and still cannot get the darn think to work. Here is
what I have:

=DLookUp("[ContainerSize]","Chemicals","[ProductName] = ' " &
[ProductName]
& "'")

I am trying to look up the Price, in the Chemicals table, based on the
ProductName selected in the current form.

Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Jody

The "lookup" column in tables can cause quite a bit of confusion for both
developers and users. The value that gets stored is the ID value of the
looked-up row, but the looked-up value is what is displayed. Imagine trying
to build a query that looks for, say, "Prince William Sound" when your table
holds "17"?!

The standard way of handling "looking ups" is to use forms, not tables. And
to convert that table "lookup column" back to a simple number field (or
whatever matches the ID field in the lookup table -- the table that holds
the values looked-up).

In a form, create a combobox control. For the row source of that combobox,
use a query. In that query, use the lookup table, and get the rowID and the
"looked-up value", in that order.

In the combobox control, set the Bound Column property to 1, and set the
Column Width property to something like "0,2" -- this hides the rowID and
displays the "looked-up value" in the combobox.

Make sure that combobox control refers to the field in your main table that
needs the ID.

Confused yet?!<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jody said:
If you go into the table and try to add a column, there is Insert, Lookup
(this is the one I used), delete and rename column.

I used the lookup column in the orders table so that the user would not
have
to type in the long, hard to spell chemical name. I am sure there is
probably
a better way to do this. Can you point me in that direction please?

Jeff Boyce said:
Jody

What do you mean by "lookup column"? Is that something you are doing
directly in the table? If so, bear in mind that Access tables are great
places to store data, but are NOT great ways to display it ... that's
what
forms are for (and reports for printed display).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jody said:
Okay, they both work. THe first expresion returns the first price in
the
Chemicals table.

The second expresion also works for a set value.

I've checked all the names. Everything there looks good. The drop down
menu
in the form is set up as a lookup column in the orders table. Is that
wrong
to do? These chemical names are so long I am afraid that they will be
typed
in wrong, so I thought using the lookup column would be the easiest way
to
do
this. Could that be causing a problem?

:

I am trying to autofill a form based on a drop down menu. I want the
price
and description of an item to autofill. I have looked through all the
posting, tried them, and still cannot get the darn think to work. Here
is
what I have:

=DLookUp("[ContainerSize]","Chemicals","[ProductName] = ' " &
[ProductName]
& "'")

I am trying to look up the Price, in the Chemicals table, based on the
ProductName selected in the current form.

Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
100% confused, but thank you. I'll try working on what you suggested and let
you know.

Jody

Jeff Boyce said:
Jody

The "lookup" column in tables can cause quite a bit of confusion for both
developers and users. The value that gets stored is the ID value of the
looked-up row, but the looked-up value is what is displayed. Imagine trying
to build a query that looks for, say, "Prince William Sound" when your table
holds "17"?!

The standard way of handling "looking ups" is to use forms, not tables. And
to convert that table "lookup column" back to a simple number field (or
whatever matches the ID field in the lookup table -- the table that holds
the values looked-up).

In a form, create a combobox control. For the row source of that combobox,
use a query. In that query, use the lookup table, and get the rowID and the
"looked-up value", in that order.

In the combobox control, set the Bound Column property to 1, and set the
Column Width property to something like "0,2" -- this hides the rowID and
displays the "looked-up value" in the combobox.

Make sure that combobox control refers to the field in your main table that
needs the ID.

Confused yet?!<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jody said:
If you go into the table and try to add a column, there is Insert, Lookup
(this is the one I used), delete and rename column.

I used the lookup column in the orders table so that the user would not
have
to type in the long, hard to spell chemical name. I am sure there is
probably
a better way to do this. Can you point me in that direction please?

Jeff Boyce said:
Jody

What do you mean by "lookup column"? Is that something you are doing
directly in the table? If so, bear in mind that Access tables are great
places to store data, but are NOT great ways to display it ... that's
what
forms are for (and reports for printed display).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Okay, they both work. THe first expresion returns the first price in
the
Chemicals table.

The second expresion also works for a set value.

I've checked all the names. Everything there looks good. The drop down
menu
in the form is set up as a lookup column in the orders table. Is that
wrong
to do? These chemical names are so long I am afraid that they will be
typed
in wrong, so I thought using the lookup column would be the easiest way
to
do
this. Could that be causing a problem?

:

I am trying to autofill a form based on a drop down menu. I want the
price
and description of an item to autofill. I have looked through all the
posting, tried them, and still cannot get the darn think to work. Here
is
what I have:

=DLookUp("[ContainerSize]","Chemicals","[ProductName] = ' " &
[ProductName]
& "'")

I am trying to look up the Price, in the Chemicals table, based on the
ProductName selected in the current form.

Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Okay. The Query/combobox works nicely together. When I try to use DLookup
though to try and fill a textbox for Price, Part Number(this is text) and
Container Size, I get: #error. I can get Dlookup to work if I type in the
value myself, but when I try to link it to something like the combo box, I
get errors.
I tried basing the Dlookup on the ProductName
=DLookUp("[ContainerSize]","Chemicals","[ProductName]='" &
Forms!poll!ProductName)

I tried basing it on the categoryID associated with the ProductName
=DLookUp("[ContainerSize]","Chemicals","[CategoryID]='" &
Forms!poll!CategoryID)

Both give the dreaded #error. Ugghh!!!



Jody said:
100% confused, but thank you. I'll try working on what you suggested and let
you know.

Jody

Jeff Boyce said:
Jody

The "lookup" column in tables can cause quite a bit of confusion for both
developers and users. The value that gets stored is the ID value of the
looked-up row, but the looked-up value is what is displayed. Imagine trying
to build a query that looks for, say, "Prince William Sound" when your table
holds "17"?!

The standard way of handling "looking ups" is to use forms, not tables. And
to convert that table "lookup column" back to a simple number field (or
whatever matches the ID field in the lookup table -- the table that holds
the values looked-up).

In a form, create a combobox control. For the row source of that combobox,
use a query. In that query, use the lookup table, and get the rowID and the
"looked-up value", in that order.

In the combobox control, set the Bound Column property to 1, and set the
Column Width property to something like "0,2" -- this hides the rowID and
displays the "looked-up value" in the combobox.

Make sure that combobox control refers to the field in your main table that
needs the ID.

Confused yet?!<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jody said:
If you go into the table and try to add a column, there is Insert, Lookup
(this is the one I used), delete and rename column.

I used the lookup column in the orders table so that the user would not
have
to type in the long, hard to spell chemical name. I am sure there is
probably
a better way to do this. Can you point me in that direction please?

:

Jody

What do you mean by "lookup column"? Is that something you are doing
directly in the table? If so, bear in mind that Access tables are great
places to store data, but are NOT great ways to display it ... that's
what
forms are for (and reports for printed display).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Okay, they both work. THe first expresion returns the first price in
the
Chemicals table.

The second expresion also works for a set value.

I've checked all the names. Everything there looks good. The drop down
menu
in the form is set up as a lookup column in the orders table. Is that
wrong
to do? These chemical names are so long I am afraid that they will be
typed
in wrong, so I thought using the lookup column would be the easiest way
to
do
this. Could that be causing a problem?

:

I am trying to autofill a form based on a drop down menu. I want the
price
and description of an item to autofill. I have looked through all the
posting, tried them, and still cannot get the darn think to work. Here
is
what I have:

=DLookUp("[ContainerSize]","Chemicals","[ProductName] = ' " &
[ProductName]
& "'")

I am trying to look up the Price, in the Chemicals table, based on the
ProductName selected in the current form.

Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
If you are using a query to feed a combobox, could you "add" the additional
columns of [Price], [Part Number] and [Container Size] to that query? If
so, you could then use something like the following to set values for
textboxes after the combobox updates:

Me!txtPrice = Me!cboYourCombobox.Column(2)
Me!txtPartNumber = Me!cboYourCombobox.Column(3)
...

Beware! The .Column() property is zero-based ... the count starts with
Column(0) as the FIRST column from your query.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jody said:
Okay. The Query/combobox works nicely together. When I try to use DLookup
though to try and fill a textbox for Price, Part Number(this is text) and
Container Size, I get: #error. I can get Dlookup to work if I type in the
value myself, but when I try to link it to something like the combo box, I
get errors.
I tried basing the Dlookup on the ProductName
=DLookUp("[ContainerSize]","Chemicals","[ProductName]='" &
Forms!poll!ProductName)

I tried basing it on the categoryID associated with the ProductName
=DLookUp("[ContainerSize]","Chemicals","[CategoryID]='" &
Forms!poll!CategoryID)

Both give the dreaded #error. Ugghh!!!



Jody said:
100% confused, but thank you. I'll try working on what you suggested and
let
you know.

Jody

Jeff Boyce said:
Jody

The "lookup" column in tables can cause quite a bit of confusion for
both
developers and users. The value that gets stored is the ID value of
the
looked-up row, but the looked-up value is what is displayed. Imagine
trying
to build a query that looks for, say, "Prince William Sound" when your
table
holds "17"?!

The standard way of handling "looking ups" is to use forms, not tables.
And
to convert that table "lookup column" back to a simple number field (or
whatever matches the ID field in the lookup table -- the table that
holds
the values looked-up).

In a form, create a combobox control. For the row source of that
combobox,
use a query. In that query, use the lookup table, and get the rowID
and the
"looked-up value", in that order.

In the combobox control, set the Bound Column property to 1, and set
the
Column Width property to something like "0,2" -- this hides the rowID
and
displays the "looked-up value" in the combobox.

Make sure that combobox control refers to the field in your main table
that
needs the ID.

Confused yet?!<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP


If you go into the table and try to add a column, there is Insert,
Lookup
(this is the one I used), delete and rename column.

I used the lookup column in the orders table so that the user would
not
have
to type in the long, hard to spell chemical name. I am sure there is
probably
a better way to do this. Can you point me in that direction please?

:

Jody

What do you mean by "lookup column"? Is that something you are
doing
directly in the table? If so, bear in mind that Access tables are
great
places to store data, but are NOT great ways to display it ...
that's
what
forms are for (and reports for printed display).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Okay, they both work. THe first expresion returns the first price
in
the
Chemicals table.

The second expresion also works for a set value.

I've checked all the names. Everything there looks good. The drop
down
menu
in the form is set up as a lookup column in the orders table. Is
that
wrong
to do? These chemical names are so long I am afraid that they will
be
typed
in wrong, so I thought using the lookup column would be the
easiest way
to
do
this. Could that be causing a problem?

:

I am trying to autofill a form based on a drop down menu. I want
the
price
and description of an item to autofill. I have looked through all
the
posting, tried them, and still cannot get the darn think to work.
Here
is
what I have:

=DLookUp("[ContainerSize]","Chemicals","[ProductName] = ' " &
[ProductName]
& "'")

I am trying to look up the Price, in the Chemicals table, based
on the
ProductName selected in the current form.

Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
I used the lookup column in the orders table so that the user would not have
to type in the long, hard to spell chemical name. I am sure there is probably
a better way to do this. Can you point me in that direction please?
Click to expand...

Yes. Use a lookup - a Combo Box - *ON THE FORM*, not in the table. It
is not necessary to use Microsoft's misdesigned, misleading,
infuriating Lookup Field misfeature in order to put a combo box on a
form!

Users should not be opening table datasheets at all; you have much
more control and flexibility using a form. You're right, users should
certainly not be typing 1,3,8-trimethylxanthine, although I should
probably go drink a solution shortly... but a lookup field is NOT the
best way to solve this problem.
 
I got the combo box in the form now. Now what I need is to figure out how to
use the combo box selection, to populate other fields in the form. DLookup is
not working (It give the #error message). The previous message has the
Dllokup formulas I am trying to use.

Thanks
 
I got the combo box in the form now. Now what I need is to figure out how to
use the combo box selection, to populate other fields in the form. DLookup is
not working (It give the #error message). The previous message has the
Dllokup formulas I am trying to use.
Click to expand...

The cause of the problem is that with a Lookup field - or for that
matter a combo box! - what you see is *not* what you get. The visible
value in the combo box might be "1,4,6-trimethylxanthine" but the
actual VALUE of the combo box is 312 - the unique ID of that chemical.

I don't know just what table you're searching or how, but you need to
understand that what you see and what the computer sees may be
different. You can PROBABLY use DLookUp on the ID field (whatever that
fieldname is, I don't know) to look up the numeric field in the table.
 
Back
Top