DLookup based on combo box on parent form

[c.kurutz]

Hello everyone. I have a problem with looking up pricing. Here is
what I have so far:

TABLES

tblItems:
itemid
itemdescription
itemunit (each, roll, square foot)
location1price
location2price

tblOrders:
OrderID
JobName
PickUpLocation ( location 1 or location 2)

tblOrderDetails:
OrderID
Item
itemunit
Unit Price

I have a form set up for Orders (parent form) with Order Details as the
subform. The table "tblOrderDetails" is used for the subform. The
"Item" field is setup as a combo box on the form. An after update
procedure has been added to lookup information from the "tblItems"
table. Based on "itemid" it will fill in itemdescription, itemunit and
unit price. This all works fine.

The problem I have is picking the right price. If the "pick up
location" on the parent form is set to "location1", I need the unit
price on the subform to pull "location1pricing" from tblItems. If the
pick up location is set to "location 2", I need the unit price to pull
"location2pricing".

The code I have to pull pricing is:
Me.UnitPrice = DLookup("UnitPriceLocation1", "tblItems", "itemno=" &
Me.Description)
Of course, this always pulls in unitpricelocation1 pricing.
I tried an "IIf" with the "DLookup" but couldn't get it to work
properly. I also didn't think this was the best way to go with the
code.

Me.UnitPrice = IIf(Forms![frmquoteinformation].[FOB] =
unitpricelocation1, DLookup("unitpricelocation1", "tblItems", "itemno="
& Me.Description), DLookup("unitpricelocation2", "tblItems", "itemno="
& Me.Description))

This seems to always pull "unitpricelocation2" pricing.

Any help would be appreciated.

[salad]

c.kurutz wrote:

Hello everyone. I have a problem with looking up pricing. Here is
what I have so far:

TABLES

tblItems:
itemid
itemdescription
itemunit (each, roll, square foot)
location1price
location2price

tblOrders:
OrderID
JobName
PickUpLocation ( location 1 or location 2)

tblOrderDetails:
OrderID
Item
itemunit
Unit Price

I have a form set up for Orders (parent form) with Order Details as the
subform. The table "tblOrderDetails" is used for the subform. The
"Item" field is setup as a combo box on the form. An after update
procedure has been added to lookup information from the "tblItems"
table. Based on "itemid" it will fill in itemdescription, itemunit and
unit price. This all works fine.

The problem I have is picking the right price. If the "pick up
location" on the parent form is set to "location1", I need the unit
price on the subform to pull "location1pricing" from tblItems. If the
pick up location is set to "location 2", I need the unit price to pull
"location2pricing".

The code I have to pull pricing is:
Me.UnitPrice = DLookup("UnitPriceLocation1", "tblItems", "itemno=" &
Me.Description)
Of course, this always pulls in unitpricelocation1 pricing.
I tried an "IIf" with the "DLookup" but couldn't get it to work
properly. I also didn't think this was the best way to go with the
code.

Me.UnitPrice = IIf(Forms![frmquoteinformation].[FOB] =
unitpricelocation1, DLookup("unitpricelocation1", "tblItems", "itemno="
& Me.Description), DLookup("unitpricelocation2", "tblItems", "itemno="
& Me.Description))

This seems to always pull "unitpricelocation2" pricing.

Any help would be appreciated.

Can you create a query and use that as the "table source" instead of a
table? You can specify the price to pick up from the query. You might
also want to check to see if FOB is empty/null. The query would select
the itemno,location, and price. If there were prices for both, you'd
get 2 records. Thus if someone selected Loc2 and there was only Loc1
you'd have 1 record and you could create an error check off of that.

[Bob Quintal]

"c.kurutz" <c.******@comcast.net> wrote in
news:11**********************@h76g2000cwa.googlegr oups.com:

Hello everyone. I have a problem with looking up pricing.
Here is what I have so far:

TABLES

tblItems:
itemid
itemdescription
itemunit (each, roll, square foot)
location1price
location2price

tblOrders:
OrderID
JobName
PickUpLocation ( location 1 or location 2)

tblOrderDetails:
OrderID
Item
itemunit
Unit Price

I have a form set up for Orders (parent form) with Order
Details as the subform. The table "tblOrderDetails" is used
for the subform. The "Item" field is setup as a combo box on
the form. An after update procedure has been added to lookup
information from the "tblItems" table. Based on "itemid" it
will fill in itemdescription, itemunit and unit price. This
all works fine.

The problem I have is picking the right price. If the "pick
up location" on the parent form is set to "location1", I need
the unit price on the subform to pull "location1pricing" from
tblItems. If the pick up location is set to "location 2", I
need the unit price to pull "location2pricing".

The code I have to pull pricing is:
Me.UnitPrice = DLookup("UnitPriceLocation1", "tblItems",
"itemno=" & Me.Description)
Of course, this always pulls in unitpricelocation1 pricing.
I tried an "IIf" with the "DLookup" but couldn't get it to
work properly. I also didn't think this was the best way to
go with the code.

Me.UnitPrice = IIf(Forms![frmquoteinformation].[FOB] =
unitpricelocation1, DLookup("unitpricelocation1", "tblItems",
"itemno=" & Me.Description), DLookup("unitpricelocation2",
"tblItems", "itemno=" & Me.Description))

This seems to always pull "unitpricelocation2" pricing.

Any help would be appreciated.

Me.UnitPrice = DLookUp(IIf(Forms![frmquoteinformation].[FOB]=1,
"tblItems","itemno= '" & Me.Description & "'")

--
Bob Quintal

PA is y I've altered my email address.