Re: DLookup based on combo box on parent form

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.


.

Relevant Pages

  • Re: DLookup based on combo box on parent form
    ... itemunit ... The table "tblOrderDetails" is used ... will fill in itemdescription, itemunit and unit price. ... need the unit price to pull "location2pricing". ...
    (comp.databases.ms-access)
  • Re: Electrical supply sanity check...
    ... A good string of nylon will pull in a rope to pull in what not. ... It's on the cusp of 400 or 500 MCM copper, and I'm probably just going 500 to be sure. ... They can, but they won't, or they price it so high that I won't, comes to the same thing in the end). ...
    (rec.crafts.metalworking)
  • Re: Just looking
    ... Is the price right? ... I just want to keep my options open and pull the ... trigger at the right time. ... This is just a practice run. ...
    (rec.motorcycles)
  • Re: Price on a specific date change
    ... "SHIPP" wrote in message ... >I have finally gotten the query to pull the correct pricing date. ... instead of pulling the DateTimeField I want to pull the price. ...
    (microsoft.public.access.queries)
  • Re: Help please with a lookup problem
    ... My form sheet "DATA Sheet" has the same three columns. ... list of matching types but it also needs to pull in the price as well. ...
    (microsoft.public.excel.worksheet.functions)