Populating a field based on a combobox value.

[Kozbot]

In my QC database I have a table of products, then a table of the production lots. Each product has multiple production lots. the lot numbers are text based on the date of the run and machine they were run on. For example lot number 130425M71 was run on the 04/25/13 on machine 7.

My QC techs will be using the database to enter QC data for multiple products and lots at the same time. I do not want the QC tech to have to reenter the lot number for each entry.

basically when the QC selects the product from the combobox, I want the most recent lot number entered for for THAT product. So if the QC tech selects PE-500, the most recent lot number for that product auto populates. if the other QC tech selects PE-900, the most recent lot number for THAT product autopopulates. The product table is related to the lot number table via one to many.

How can I accomplish this?

 

[boblarson]

I do not want the QC tech to have to reenter the lot number for each entry.

The correct fix for this is to have the correct table structure. If the same exact data will be added for each record, that means that there should be another table above the current one which has those fields and then those fields removed from the current table.

So, just a simple example.

I have Orders and OrderDetails

Orders
OrderID - Autonumber (PK)
OrderDate
CustomerID
ShipDate

OrderDetails
OrderDetailsID - Autonumber (PK)
OrderID - Long Integer (FK)
ProductID
Price


And then Orders is used as a main form and Order Details is used in a subform which then because of the Master/Child links on OrderID it automatically enters that ID for that record.

The similar set up would be for your situation.

 

[Kozbot]

Let me clarify

The form the product and lot number are being entered into is based on a different table "QCInfo", which stores all the test results for that lot number. So the product table has one to many with the lot numbers table, which has one to many with the QCInfo table. There will multiple QC info entries for each lot. The issue arises when switching back and forth between lots.