Trying to create a lookup

[tomneale]

Hi everyone,

I am trying to create a database for work, and something has got me completely stumped. I've watched youtube videos, googled it and I just can't get it to work. I want one field in the table to lookup that field, find it in another table, and return the associated value so I can create a calculated field in that table.



1. so I have a table called Purchase Orders. In this table I have Purchase Order No. and then lookups from other tables e.g. Product>Blueberry, Supplier>Supplier A, Country of Origin>Argentina



2. In Purchase orders, I have a field called "Subproduct" this is basically product format, I deal in say 10x125g Punnets, or 10kg bulk boxes.



3. In the Subproduct table, I imported an excel where I have the subproduct name, and then it's associated weight in kg.


4. I want it so that when I select subproduct in Purchase orders, it will lookup that subproduct in the Subproduct table, and return the Weight KG value, and put it in Weight Per Box in Purchase Orders.

[pbaldy]

Is this what you're looking for?

http://www.baldyweb.com/Autofill.htm

[tomneale]

I think so but that is referring to forms, I just want it in a field.

I'll put it simply.

One table BAGNAMES:

Bag Name : Weight
1.5KGBAG 1.50
2.5KGBAG 2.50

Next Table Orders:

Customer : Qty : BagName : Weight
Joe 1 (Lookup from BAGNAMES)1.5KGBAG (Autofill/Calculation/Lookup whatever it's called voilà) 1.50

Select the bag name, *poof* Weight field automatically fills.

[pbaldy]

Most of us would recommend not working directly in tables. You have little control and no events to use code from. You can use a lookup field for one piece of data, but most of us would recommend against it:

http://www.theaccessweb.com/lookupfields.htm