I have an items table that lists all items purchased for all orders made. The Items table is linked to an Orders table which I linked to a data entry form. Everytime I enter a new order, a subform allows me to enter new items into the Items table.

The Items table creates new record for every item I enter on an order. Sometimes, an order has the same kind items as an old order but the items still get saved as unique records (since I have to track the serial #'s and other unique fields). To alleviate errors in data entry I wanted to make a lookup table for the description, part # and manufacturer fields of my Item table.

In other words, when I enter a new item in my form, I want it to allow me to use a combo box lookup field for the Description, Part # and Manufacturer. That way if I enter a part by part #, it will pull the corresponding Description and Manufacturer info from the lookup table and if I enter a part by description, it will automatically input the part # and mfg from my lookup table.

I made a new table to be used as a lookup table called Unique Parts. I only added the three fields I was interested in being able to look up: Description, Manufacturer and Part #.

I know that I can use a lookup field for any of the 3 fields and make it three columns but instead of the 2 extra columns being displayed in the combo box pull-down, I want them input into their corresponding fields on my table. That way if I don't know the part # for the item but I know the description, I can just type in the description and have the part # input into its field based on the part # that corresponds to the description from my lookup table.

Sorry if my description is confusing. If anyone gets what I am trying to do and can give some advice, please do. If anything is unclear I can try to elaborate.

Hi,

   You shouldn't be storing the same information in more than one table.

It sounds like you have:

tblUniqueParts
UniquePartsID - PK
Description
Part#
Manufacturer

tblItems
ItemsID - PK
UniquePartsID - FK
Description
Part#
Manufacturer

   What you really want is...

tblUniqueParts
UniquePartsID - PK
Description
Part#
Manufacturer

tblItems
ItemsID - PK
UniquePartsID - FK


   Then when you need Description, Part# and Manufacturer, you can create a query to bring in that data from the look up table.

   If you insist on storing this extra data, then in the form, on the Before Insert Event, you want to add code to paste the values from the look up table based on the FK (which is the PK in the look up table). There is no need to do this, since the data is there when you need it.

HTH



mmcdonal

Thanks for the repsonse. I think I get what you are saying I should do but I may need to do more reading up to get it to work. I have learned the basics but making action and update queries is something I still need to learn. Looks like for now I'll have to have a sloppy table with duplicates.