How to have price bands

[Asif Rehman]

Hello

I created a database for invoicing purposes and have 4
tables of many (Products, Customers, Orders, Order
Details) linked to create a form called orders for
invoicing.
In the products table I have a unit price field which
displays the price of an item when selected in the orders
subform which is a datasheet. I would like to have 3
mulitple price bands so depending on where the customer is
located is how much they will have to pay for the
product.

I have created a price band field in the customers table
and three other unit prices in the products table. How do
I get the subform to look at which price band the customer
falls into when they are selected and then display the
price of the product when it is selected in the subform
datasheet.

Any help will be much appreciated.

Kindest Regards

Asif

 

[Andy Korth]

Asif,
I think the way I would approach this is:
In the table that keeps track of the prices, create two
more fields. This way you would have Price1, Price2, and
Price3.

How do I get the subform to look at which price band the
customer falls into when they are selected and then
display the price of the product when it is selected in
the subform datasheet.

Hmm... You can have another field which will have the
text Price1, Price2, or Price3. Then it simply will set
one of those three to the control source of the price
text box.
Depending on how your database is set up this might be a
better solution:
On the open event of the form, use if statements to check
on which price band the customer falls into. Then you can
set the control source as needed.

I should warn you. I don't know what a price band is. I'm
assuming that there are just 3 different prices for each
product, and the prices are different depending on the
customer's location (country)
Like Canadian prices are higher than United States
prices. (Yes, I realize the currency is different)

I hope this helps, if not, clarify and I'll reply
Andy

 

[Andy Korth]

Asif,
I think the way I would approach this is:
In the table that keeps track of the prices, create two
more fields. This way you would have Price1, Price2, and
Price3.

How do I get the subform to look at which price band the
customer falls into when they are selected and then
display the price of the product when it is selected in
the subform datasheet.

Hmm... You can have another field which will have the
text Price1, Price2, or Price3. Then it simply will set
one of those three to the control source of the price
text box.
Depending on how your database is set up this might be a
better solution:
On the open event of the form, use if statements to check
on which price band the customer falls into. Then you can
set the control source as needed.

I should warn you. I don't know what a price band is. I'm
assuming that there are just 3 different prices for each
product, and the prices are different depending on the
customer's location (country)
Like Canadian prices are higher than United States
prices. (Yes, I realize the currency is different)

I hope this helps, if not, clarify and I'll reply
Andy

 

[Asif Rehman]

Thank You Andy for replying.

Yes you are right the price of a product does change
depending on the location of the customer therefore I am
requiring a minimum of 3 price bands.

So if I have three price fields in my products table and
one price field in my orders table I can use IF statements
to check on which price band the customer falls into.

Sorry for being so slow but I am not an expert in this
field so you know what my next question is, how do I write
these if statements.

For example the customer price bands will be warehouse,
local, national and the price will be set accordingly.

Thanks in advance.

Kindest Regards

Asif

 

[Andy Korth]

For example the customer price bands will be warehouse,

local, national and the price will be set accordingly.

Ok, I think I understand better:

You will want something like this:
If [Location] = "warehouse" Then
[Price].ControlSource = Price1
End If
If [Location] = "local" Then
[Price].ControlSource = Price2
End If
If [Location] = "national" Then
[Price].ControlSource = Price3
End If

You will need to put this code in the Code Builder. View
the property window for your form, and click on the '...'
by the AfterUpdate event. Paste that code in there.

This assumes that you have a control (like a text box or
combo box) on your form with the name Location, and
another one named Price. I also assume that Location will
have warehouse, local, or national in it. You can move
the Price1 - Price3 around if you have them set up
differently.

How's that for a start?

Andy

 

[Asif Rehman]

Hello

Thank You again for the help.

I think I only need one more thing, The control location
is on the main form orders and the price field is in the
orders subform.

I have inputed the code into the AfterUpdate of the
subforms properties but nothing as yet is happening in the
price field.

Thank You again you have been a great help.

Kind Regards

Asif

-----Original Message-----

For example the customer price bands will be warehouse,
local, national and the price will be set accordingly.

Ok, I think I understand better:

You will want something like this:
If [Location] = "warehouse" Then
[Price].ControlSource = Price1
End If
If [Location] = "local" Then
[Price].ControlSource = Price2
End If
If [Location] = "national" Then
[Price].ControlSource = Price3
End If

You will need to put this code in the Code Builder. View
the property window for your form, and click on the '...'
by the AfterUpdate event. Paste that code in there.

This assumes that you have a control (like a text box or
combo box) on your form with the name Location, and
another one named Price. I also assume that Location will
have warehouse, local, or national in it. You can move
the Price1 - Price3 around if you have them set up
differently.

How's that for a start?

Andy

-----Original Message-----
Thank You Andy for replying.

Yes you are right the price of a product does change
depending on the location of the customer therefore I am
requiring a minimum of 3 price bands.

So if I have three price fields in my products table and
one price field in my orders table I can use IF statements
to check on which price band the customer falls into.

Sorry for being so slow but I am not an expert in this
field so you know what my next question is, how do I write
these if statements.

For example the customer price bands will be warehouse,
local, national and the price will be set accordingly.

Thanks in advance.

Kindest Regards

Asif

.

 

[Andy Korth]

If you are going to refer to a subform, you need to be
sure that the controls are either on the subform, you you
need to specify the form the control is on.

Forms!mainformname!controlname.ControlSource

Like that.
But you would be getting an error..... for this reason I
suspect that the event AfterUpdate isn't triggering. I'd
make a button that had the same code on the event OnClick-
just to be sure it works.
(I haven't used AfterUpdate myself, but I know that
button will trigger the code.)

Glad to be a help.
Andy

 

[Blaire Comrie]

try this link, it's not free but I think it will help you with your problem

http://www.comriesoftware.net/codewidgets/product.aspx?key=27