Price Levels

I have an order entry form that runs off of a query. It started off with
just using the unit price and it works fine. But now my boss is wanting it
to reflect the correct price for the customer's price level. I started by
adding Price level in my customer table to reflect which price level they are
on. then I added the price levels in my items table. (There are 10 price
levels total). But after that I am drawing a blank on how to pull them in to
my form. If at all possible, I would like it to be a combo box that brings
up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200805/1

Use the Form Current event to set the value of the combo box. Use a DLookup
to the customer table to determine the price level, then assign that value to
the combo box:

Me.cboPriceLevel = DLookup("[PriceLevel]", "tblCustomer", "[CustID] = "
& Me.txtCustID)
--
Dave Hargis, Microsoft Access MVP


"shannaj via AccessMonster.com" wrote:

I have an order entry form that runs off of a query. It started off with
just using the unit price and it works fine. But now my boss is wanting it
to reflect the correct price for the customer's price level. I started by
adding Price level in my customer table to reflect which price level they are
on. then I added the price levels in my items table. (There are 10 price
levels total). But after that I am drawing a blank on how to pull them in to
my form. If at all possible, I would like it to be a combo box that brings
up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200805/1

I can think of several approaches based on your formulation of price level.
Do you use a base price and then level 1 is a percent reduced and level 5 a
percent increased or all are discrete prices?
Using the percent method the customer would have a field for level. Then
use a multiplier factor for raise or lower price
Level % Reduce Multiplier
0 0 1
1 1.00% 0.99 =1-0.01
2 2.00% 0.98 =1-0.02
3 3.00% 0.97 =1-0.03
4 4.00% 0.96 =1-0.04
5 5.00% 0.95 =1-0.05
10 10.00% 0.9 =1-0.1
15 15.00% 0.85 =1-0.15
-1 101.00% -0.01 =1-1.01
-2 102.00% -0.02 =1-1.02
-3 103.00% -0.03 =1-1.03
-4 104.00% -0.04 =1-1.04
-5 105.00% -0.05 =1-1.05
-10 110.00% -0.1 =1-1.1
-15 115.00% -0.15 =1-1.15
The formula would be one minus the percent stated in decimal form.
Discrete prices will require a translation table.
--
KARL DEWEY
Build a little - Test a little


"shannaj via AccessMonster.com" wrote:

I have an order entry form that runs off of a query. It started off with
just using the unit price and it works fine. But now my boss is wanting it
to reflect the correct price for the customer's price level. I started by
adding Price level in my customer table to reflect which price level they are
on. then I added the price levels in my items table. (There are 10 price
levels total). But after that I am drawing a blank on how to pull them in to
my form. If at all possible, I would like it to be a combo box that brings
up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200805/1

I try this and it gives me an error: compile error: Syntax error

Klatuu wrote:
Use the Form Current event to set the value of the combo box. Use a DLookup
to the customer table to determine the price level, then assign that value to
the combo box:

Me.cboPriceLevel = DLookup("[PriceLevel]", "tblCustomer", "[CustID] = "
& Me.txtCustID)
I have an order entry form that runs off of a query. It started off with
just using the unit price and it works fine. But now my boss is wanting it
[quoted text clipped - 5 lines]
up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?

--
Message posted via http://www.accessmonster.com

Me.cboPriceLevel = DLookup("[PriceLevel]", "tblCustomer", "[CustID] = " &
Me.txtCustID)

The syntax, as posted, is correct, but there are some assumptions.
The combo box name is cboPriceLevel
The field name that stores the price level is named PriceLevel and is a
numeric field.
The table name is tblCustomer
The name of the table's primary key field is CustID
There is a text box control on the form named txtCustID boud to the field
CustID in the form's recordset.
The code is in the form's code module.

Please check these things.
If you are still having a problem, please copy/paste the code from your
module, so I can see if I missed something

--
Dave Hargis, Microsoft Access MVP


"shannaj via AccessMonster.com" wrote:

I try this and it gives me an error: compile error: Syntax error

Klatuu wrote:
Use the Form Current event to set the value of the combo box. Use a DLookup
to the customer table to determine the price level, then assign that value to
the combo box:

Me.cboPriceLevel = DLookup("[PriceLevel]", "tblCustomer", "[CustID] = "
& Me.txtCustID)
I have an order entry form that runs off of a query. It started off with
just using the unit price and it works fine. But now my boss is wanting it
[quoted text clipped - 5 lines]
up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?

--
Message posted via http://www.accessmonster.com

You pointed out a couple of problems that I can see. One, My field name that
stores that price level name is a text field because I was not sure if it was
going to be looking up anything. I will change that to numeric. Also, I had
no CustomerID in my form because it is a subform with just the orderdetails
based from a query. So I guess you could say I have many things wrong. But I
have just been clueless on where to even start.

Klatuu wrote:
Me.cboPriceLevel = DLookup("[PriceLevel]", "tblCustomer", "[CustID] = " &
Me.txtCustID)

The syntax, as posted, is correct, but there are some assumptions.
The combo box name is cboPriceLevel
The field name that stores the price level is named PriceLevel and is a
numeric field.
The table name is tblCustomer
The name of the table's primary key field is CustID
There is a text box control on the form named txtCustID boud to the field
CustID in the form's recordset.
The code is in the form's code module.

Please check these things.
If you are still having a problem, please copy/paste the code from your
module, so I can see if I missed something

I try this and it gives me an error: compile error: Syntax error

[quoted text clipped - 9 lines]
up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200805/1

To help, I need to know a bit about your configuration.
First, I assume you have the invoice header and client info in the main form
and the line item details are in the subform, but what I need to know is how
the price is calculated. If you put an unbound combo on the main form as I
described, you could use the value of the combo in the extended price
calculation.

How is the discount carrired? As a percentage off list, or is it a separate
price list for each client?
--
Dave Hargis, Microsoft Access MVP


"shannaj via AccessMonster.com" wrote:

You pointed out a couple of problems that I can see. One, My field name that
stores that price level name is a text field because I was not sure if it was
going to be looking up anything. I will change that to numeric. Also, I had
no CustomerID in my form because it is a subform with just the orderdetails
based from a query. So I guess you could say I have many things wrong. But I
have just been clueless on where to even start.

Klatuu wrote:
Me.cboPriceLevel = DLookup("[PriceLevel]", "tblCustomer", "[CustID] = " &
Me.txtCustID)

The syntax, as posted, is correct, but there are some assumptions.
The combo box name is cboPriceLevel
The field name that stores the price level is named PriceLevel and is a
numeric field.
The table name is tblCustomer
The name of the table's primary key field is CustID
There is a text box control on the form named txtCustID boud to the field
CustID in the form's recordset.
The code is in the form's code module.

Please check these things.
If you are still having a problem, please copy/paste the code from your
module, so I can see if I missed something

I try this and it gives me an error: compile error: Syntax error

[quoted text clipped - 9 lines]
up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200805/1

Originally the price was calculated by looking up the unit price in my Items
table. When I found that they wanted the other 9 price levels included, it
has just thrown me off. There are 10 different prices for each Item in the
table, and different customers are set to different price levels.

Klatuu wrote:
To help, I need to know a bit about your configuration.
First, I assume you have the invoice header and client info in the main form
and the line item details are in the subform, but what I need to know is how
the price is calculated. If you put an unbound combo on the main form as I
described, you could use the value of the combo in the extended price
calculation.

How is the discount carrired? As a percentage off list, or is it a separate
price list for each client?
You pointed out a couple of problems that I can see. One, My field name that
stores that price level name is a text field because I was not sure if it was
[quoted text clipped - 25 lines]
up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200805/1

Then I think you will need to modify the subform's record source to be a
query that can include the price by customer.
Since I don't know your table structures, I can't give you a specicif
answer, but basically, you should be able to find a price in a query. You
need to have your price table set up so it has fields for the client and the
item. Then you can use a query for the subform's record source that joins
the price list table on the combination of item and client.
--
Dave Hargis, Microsoft Access MVP


"shannaj via AccessMonster.com" wrote:

Originally the price was calculated by looking up the unit price in my Items
table. When I found that they wanted the other 9 price levels included, it
has just thrown me off. There are 10 different prices for each Item in the
table, and different customers are set to different price levels.

Klatuu wrote:
To help, I need to know a bit about your configuration.
First, I assume you have the invoice header and client info in the main form
and the line item details are in the subform, but what I need to know is how
the price is calculated. If you put an unbound combo on the main form as I
described, you could use the value of the combo in the extended price
calculation.

How is the discount carrired? As a percentage off list, or is it a separate
price list for each client?
You pointed out a couple of problems that I can see. One, My field name that
stores that price level name is a text field because I was not sure if it was
[quoted text clipped - 25 lines]
up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200805/1

I totally get what you are saying, but I can not figure out what field to put
in the price table to link the two together. Here is what my ItemList
contains. Any suggestions?

ItemID
ItemDescription
Active
UnitPrice
PriceLevel2
PriceLevel3
PriceLevel4
PriceLevel5
PriceLevel6
PriceLevel7
PriceLevel8
PriceLevel9
PriceLevel10
SalesAcct

Klatuu wrote:
Then I think you will need to modify the subform's record source to be a
query that can include the price by customer.
Since I don't know your table structures, I can't give you a specicif
answer, but basically, you should be able to find a price in a query. You
need to have your price table set up so it has fields for the client and the
item. Then you can use a query for the subform's record source that joins
the price list table on the combination of item and client.
Originally the price was calculated by looking up the unit price in my Items
table. When I found that they wanted the other 9 price levels included, it
[quoted text clipped - 15 lines]
up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200805/1