By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,326 Members | 1,899 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,326 IT Pros & Developers. It's quick & easy.

Access field based on other filed entry

P: 1
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.
Aug 14 '07 #1
Share this Question
Share on Google+
2 Replies


JConsulting
Expert 100+
P: 603
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
Aug 15 '07 #2

JustJim
Expert 100+
P: 407
So let's say you have your tables built correctly.
Microsoft built this, what are the chances???

Just (joking) Jim
Aug 15 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.