DLookup Formula

  • Thread starter Thread starter spachick
  • Start date Start date
S

spachick

I'm setting up a form with these two field names.

Item
Price.

I set up a combo box on item. What I would like to do is
that every time I choose an item from the ITEM combo box,
the price of that item will reflect in PRICE.
Is that a DLOOKUP? In Lamen terms (since the help is no
help to me :-))how is the DLOOKUP function created?
 
DLookup is one approach: the other is to base the combo box on a query that
contains both the Item and Price.

In either case, you'll need to put code in the combo box's After Update
event.

Without knowing your table layouts, it's difficult to be able to give
precise instructions.

To use DLookup, you need to provide 3 pieces of information: the name of the
table you're trying to get a value from, the name of the field in that table
that has the value and (optionally) a WHERE clause to limit what's returned.

Assuming you have a table named Price that has a PriceAmount field in it,
and it has an ItemId field associated with each price, and that your Item
combo box is named cboItem, the statement would look something like

DLookup("PriceAmount", "Price", "ItemId = " & Me.cboItem)

This assumes that ItemId is a numeric value. If it's a text value, you need
to put quotes around it in the statement:

DLookup("PriceAmount", "Price", "ItemId = " & Chr$(34) & Me.cboItem &
Chr$(34))

In the other case I mentioned, you can have multiple columns in your combo
box. One value is going to be the bound field: that's what you get returned
when you simply refer to the combo box, as in Me.cboItem above. However, you
can refer to the other values as Me.cboItem.Column(n), where n is the column
number (starting at 0, not 1)
 
thanks so much for your help
for some strange reason it doesn't seem to wor

My table name is ItemsB
The Value I want to see is Price - from the field heading of that nam
The Combo Box is Combo6

Whenever the combo box entry changes, it should reflect the price associated with it
I was with you so far at

DLookup("Price", "ItemsBB", "combo62 = " & Me.combo62)

what does the "&Me mean
Help!!
----- Douglas J. Steele wrote: ----

DLookup is one approach: the other is to base the combo box on a query tha
contains both the Item and Price

In either case, you'll need to put code in the combo box's After Updat
event

Without knowing your table layouts, it's difficult to be able to giv
precise instructions

To use DLookup, you need to provide 3 pieces of information: the name of th
table you're trying to get a value from, the name of the field in that tabl
that has the value and (optionally) a WHERE clause to limit what's returned

Assuming you have a table named Price that has a PriceAmount field in it
and it has an ItemId field associated with each price, and that your Ite
combo box is named cboItem, the statement would look something lik

DLookup("PriceAmount", "Price", "ItemId = " & Me.cboItem

This assumes that ItemId is a numeric value. If it's a text value, you nee
to put quotes around it in the statement

DLookup("PriceAmount", "Price", "ItemId = " & Chr$(34) & Me.cboItem
Chr$(34)

In the other case I mentioned, you can have multiple columns in your comb
box. One value is going to be the bound field: that's what you get returne
when you simply refer to the combo box, as in Me.cboItem above. However, yo
can refer to the other values as Me.cboItem.Column(n), where n is the colum
number (starting at 0, not 1
 
What does "does not work" mean? Do you get an error, or do you get an
answer, but it's not the answer you want?

If you've got a text field on your form named Price, you need to assign it
the value in your code:

Me.Price = Dlookup("[Price]","Inventory","Item = " & Me.combo62)

(I'm assuming Price isn't linked to a field in your form's recordset)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



spachick said:
Okay lets say I have an inventory database in a table called "Inventory".

There are two columns in this table.
Item
Price

Form that is created has a field called "Item" and it is a combo box (combo62)
I also have another field name called Price.

I need to have the Price field update in the form whenever the Item
Click to expand...
associated with it is selected.
From your answer is the correct formula then:

Dlookup("[Price]","Inventory","Item = " & Me.combo62)

If it is, for some reason, it still does not work.
I am SO about to give up.
Click to expand...
 
Okay lets say I have an inventory database in a table called "Inventory"

There are two columns in this table
Item
Pric

Form that is created has a field called "Item" and it is a combo box (combo62
I also have another field name called Price

I need to have the Price field update in the form whenever the Item associated with it is selected

From your answer is the correct formula then

Dlookup("[Price]","Inventory","Item = " & Me.combo62

If it is, for some reason, it still does not work
I am SO about to give up.
 
Back
Top