copying data from another table 1

[elff]

I'm sure this is the most basic of questions, but it's been a long time since I've used Access and I just can't figure this one out. One table I have is to keep data for a cost estimate. I've created a subform for the estimate line items. The table used by the subform has among others the fields: est-materialid, est-materialdesc, est-materialcost, and est-materialrate. The materials table has material-id, material-desc, material-cost, and material-rate. The est-materialid and material-id fields are linked. Because of the changing materials, costs, etc., I actually want to copy the description, cost, and rate and store it in the estimate line item table for each material selected - that way the estimate reflects accurately the cost and rates at the time the estimate was entered. I know how to display the information in my form via the linked nature of the two tables, but I don't know how to save the info into the line item table fields. Thanks for any suggestions.

 

[OnTheFly]

If you use a combo box control for them to select the Material-ID you can also pull the approriate information into the record from this combo box by including the fields in the sql of the combo box row source property. These items can be hidden by making their width 0".

Then, for example, on the after update event of the combo box control you can enter the pertinent information into the record like this.

Your combo box control is named cmbMaterial-ID

Your sql row source for the cmbMaterial-ID is something like
SELECT Material-ID, Material-desc, Material-Cost, Material-rate FROM tblMaterials

Then you can get the data from the combo box like so...

Me!Material-desc=Me!cmbMaterial-ID.column(1)
Me!Material-Cost=Me!cmbMaterial-ID.column(2)
Me!Material-Rate=Me!cmbMaterial-ID.column(3)

Hope this helps.

OnTheFly