Looking up a record that matches multiple fields to retrieve data for another field.

[zylosan]

I am attempting to create a button on a form that takes data from 2 fields on the form, looks up a matching record, and updates a field in the record the form is linked to.

I have a base table linked to this form tbl_product_testing_data that stores various information about QA tests.

I have a form based on tbl_product_testing_data that displays a number of fields
Test_Number
Bun_Layer
Density_Results

I have a second table called tbl_testing_sets in which test data is stored. It contains the fields
test_type
test_result
bun_test_num
bun_layer

I want to click on a button and activate its on_click event at which point I would like its VBA code to
1. look up a record in the table tbl_testing_sets that matches its bun_test_num = [Test_Number] and bun_layer = [Bun_Layer], and test_type = [constant string of test type].

2. Transfer the test_result field data from the record that has been looked up to the Density_Results field.

3. refresh the displayed form.

This is some example code of what I have so far that works to find a record based on a single field (Tensile_Set) I know this code will not work for what I want to do.

Dim rst_test_set As DAO.Recordset
Set rst_test_set = Application.CurrentDb.OpenRecordset("tbl_testing_s ets", dbOpenDynaset)


rst_test_set.FindFirst "test_set_num = " & Me!Tensile_Set


Me!Tensile_Strength = rst_test_set!test_result




Me.Requery

[zylosan]

This problem has been solved.