Sorry if this is a little long, I'm trying to be a specific as possible. I'm working in Access 2003. I have a database to track the impact of price changes; my reports slice and dice the information in several ways. I have a form where the buyers enter the specific information which saves to the table tbl_Costs. Buyers enter in a part number and then enter several fields - program, customer, quantity and buyer. The buyer also manually enters the before & after price. Each program is unique with a specific quantity. It is possible for more than one program to have the same quantity. Each program also has a customer and a buyer associated with it. A program can have only one customer and one buyer; but each customer and each buyer can have multiple programs.

I have a table set up called tbl_Programs, which contains the program, quantity, customer and buyer columns. Program is the primary key. tbl_Costs has the fields part number, program, quantity, buyer, before price, after price. I have a one-to-many relationship set up between tbl_Programs and tbl_Costs.

I have a form created called frm_Costs. The buyer enters all of the information on this form, which populates tbl_Costs. The frm_Costs record source is tbl_Costs. What I'm trying to do is limit the number of entries the buyer has to make by automatically filling in the quantity, customer & buyer fields based on the program selected. My goal is for the buyer to just enter the part number and program and have the rest fill in.

This is where I've run into problems. I can get the program field to list the programs contained in the tbl_Programs, but I have not been able to get the rest of the fields to populate based on this choice. Is there a way to get the rest of the fields to automatically fill and and also have all of the information from the form save to tbl_Costs?