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 "tblOrderDetail s" 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 "location1prici ng" from tblItems. If the
pick up location is set to "location 2", I need the unit price to pull
"location2prici ng".
The code I have to pull pricing is:
Me.UnitPrice = DLookup("UnitPr iceLocation1", "tblItems", "itemno=" &
Me.Description)
Of course, this always pulls in unitpricelocati on1 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![frmquoteinforma tion].[FOB] =
unitpricelocati on1, DLookup("unitpr icelocation1", "tblItems", "itemno="
& Me.Description) , DLookup("unitpr icelocation2", "tblItems", "itemno="
& Me.Description) )
This seems to always pull "unitpricelocat ion2" pricing.
Any help would be appreciated.
2  2608 
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 "tblOrderDetail s" 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 "location1prici ng" from tblItems. If the
pick up location is set to "location 2", I need the unit price to pull
"location2prici ng".
The code I have to pull pricing is:
Me.UnitPrice = DLookup("UnitPr iceLocation1", "tblItems", "itemno=" &
Me.Description)
Of course, this always pulls in unitpricelocati on1 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![frmquoteinforma tion].[FOB] =
unitpricelocati on1, DLookup("unitpr icelocation1", "tblItems", "itemno="
& Me.Description) , DLookup("unitpr icelocation2", "tblItems", "itemno="
& Me.Description) )
This seems to always pull "unitpricelocat ion2" 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.
"c.kurutz" <c.******@comca st.net> wrote in
news:11******** **************@ h76g2000cwa.goo glegroups.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 "tblOrderDetail s" 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 "location1prici ng" from
tblItems. If the pick up location is set to "location 2", I
need the unit price to pull "location2prici ng".
The code I have to pull pricing is:
Me.UnitPrice = DLookup("UnitPr iceLocation1", "tblItems",
"itemno=" & Me.Description)
Of course, this always pulls in unitpricelocati on1 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![frmquoteinforma tion].[FOB] =
unitpricelocati on1, DLookup("unitpr icelocation1", "tblItems",
"itemno=" & Me.Description) , DLookup("unitpr icelocation2",
"tblItems", "itemno=" & Me.Description) )
This seems to always pull "unitpricelocat ion2" pricing.
Any help would be appreciated.
Me.UnitPrice = DLookUp(IIf(For ms![frmquoteinforma tion].[FOB]=1,
"tblItems","ite mno= '" & Me.Description & "'")
--
Bob Quintal
PA is y I've altered my email address. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: John Hargrove |
last post by:
I am building a database to manage test samples in an environmental
laboratory. I am learning Access as I go and don't know much about
the programming aspects. I hope to make the application user-friendly
for the sample login people by populating form controls. Using the
DLookup function, I am able to populate a form control in one record
using table data. I want to be able to populate multiple records of a
subform control (test...
|
by: Kalvin Schroder |
last post by:
I am fairly new to Access, and am trying to put together an invoice
form. The main form in called InvoiceDetailFm. Source is the table
InvoiceDetail and has invoice number, saleman, and CustID as some of
its fields. I have put together a combobox, named it CustID, and am
trying to use DLookup() to bring the complete Customer Name etc onto
the form using DLookup() function for the fields I need. The combobox
will bring up the CustID in...
|
by: thx606 |
last post by:
Hello,
I'm having problems figuring this one out. I have 2 combo boxes. The
first one the user selects a professor then the second one they select
a course and that works fine. Here is my problem for a third field used
for mailing instructions. I would like to look this up in a seperate
table based off the values entered in the combo boxes. So, if
professor and Course match then that mailing instructon would appear in
the third field....
|
by: favor08 |
last post by:
I have a database and I am using a function that captures the shortname and displays it on the mainMenu screen.
What I want to do is filter the information on the "past due" screen based on the person who has just open the database so that they can see just their items.
The shortname is stored in a table called "TShortNames" The shortname ties to an 4 character "Opid". This same "opid' is in the main "TARA" table that stores all the...
|
by: Patrick A |
last post by:
All,
My form "FRM_Main" contains 2 subforms, each an instance of the form
"FRM_Testators"
The object name of instance 1 of the form is "SFRM_Clients".
The object name of instance 2 of the form is "SFRM_Mirrors".
The form contains (among others) the combo boxes "Rel2Client" and
"Rel2Mir". (Indicating the relationship between the client and the
mirror.
| | |
by: Constantine AI |
last post by:
Hi can anybody help me with this problem?
I have a customer order form with a sales order line subform. The subform contains products ordered by customers, each (wooden) product can come in two different treatment types; golden brown or pressure treated of which are of two different prices. The prices are also determined by the customer type; pub, garden centre or public.
Currenlty on the treatment combo box, i have a dlookup in the code...
|
by: David Wright |
last post by:
Hello Folks
I am using Microsoft Access 2000
I would be grateful if someone could help me with “Dlookup”. I tried various methods of writing Dlookup and various events to trigger it, none of which were successful. I have been able to display the required value in a ‘text box’ which is a little helpful but not ideal.
I have a table called tblLogSheet. The ‘Description’ field of this table uses a lookup table called...
|
by: solargovind |
last post by:
Hello,
I have few problem with Dlookup condition. I need to retrieve next record or previous record based on certain condition. The conditions are set in in the combo box. Here, I am trying to display the records in the text box based on my selection. I have given my selection in the combo box.
For example from the student table, I am trying to display as below...
a). Who are all coming from "Newyork" city in the 7th standard class. ...
|
by: dkotula |
last post by:
Hello,
I have a DB with linked tables to ODBC, and queries based on them. I have a form with 3 cascading combo boxes and tabs containing subforms. Along with the combo boxes on the main form I have a txtJobNum, and a txtVendorNum. The subforms I have, are Job and Vendor. In subform Job, I have txtJobNum, and in subform Vendor I have txtVendorNum. What I did was use columns from the last combo box to populate txtJobNum and txtVendorNum on the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
