Access field based on other filed entry

[ntbrown]

I have a purchase order and inventory database Im working on. It was the standard one available at Microsft for free download ( a template). It works fine, but I need acouple modifications.

I have two different price levels for my suppliers. In other words some of my suppliers who provide me with large amount of product, receive a better price. What I need to happen is when the supplier is selected at the top of the Purchase Order form, I need the pricing for that supplier to automatically show up for each product selected in the subform.

RIght now, (as it had originally been from Microsoft) it doesnt even put the unit price in, I have to manually type it in after choosing a product. I need that to automatically put the price based on the product selected. And also to put the price according to my type of supplier. (I added a column in my suppliers table to indicate which pricing method they are paid)

Any thought, suggestions, and help is appreciated.

[JConsulting]

I have a purchase order and inventory database Im working on. It was the standard one available at Microsft for free download ( a template). It works fine, but I need acouple modifications.

I have two different price levels for my suppliers. In other words some of my suppliers who provide me with large amount of product, receive a better price. What I need to happen is when the supplier is selected at the top of the Purchase Order form, I need the pricing for that supplier to automatically show up for each product selected in the subform.

RIght now, (as it had originally been from Microsoft) it doesnt even put the unit price in, I have to manually type it in after choosing a product. I need that to automatically put the price based on the product selected. And also to put the price according to my type of supplier. (I added a column in my suppliers table to indicate which pricing method they are paid)

Any thought, suggestions, and help is appreciated.

Let's do this in pieces.

First, where are you storing the price level? is it available on the main form?

in order to find your price you'll need the product or productId associated with it. You'll need to be able to look at the product table and determine what the price level is.

So let's say you have your tables built correctly.

in the after update event of your Supplier combo you might do something like so

[code=vba]

me.mypricefield = DLookup("Price","ProductTable","ProductID=" & me.productID & " and PriceLevel=" & me.PriceLevel & ")"

[\code]

this assumes you have in your tables the fields needed to look up the price. The source table in this instance could be a query that would return this data relationship.
Hope it helps out.
J

[JustJim]

So let's say you have your tables built correctly.

Microsoft built this, what are the chances???

Just (joking) Jim