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