Hi - I'm trying to understand how to pull out values from a within related tables. I have a test example below to try and show this problem.
If for example I have 2 tables titled Table1 and Table2. I loop through a particular field in the first table, and with each value, I want to find the record in the second table where this value appears - but then display a different field within that record in table2. Ive tried the code below but it keeps on stopping on the first row. I think I 'm getting confused with excel in vba. I'm new to this and might have done something stupid, but there must be a better way. Should I be using the Dlookup instead? thanks for any help
If for example I have 2 tables titled Table1 and Table2. I loop through a particular field in the first table, and with each value, I want to find the record in the second table where this value appears - but then display a different field within that record in table2. Ive tried the code below but it keeps on stopping on the first row. I think I 'm getting confused with excel in vba. I'm new to this and might have done something stupid, but there must be a better way. Should I be using the Dlookup instead? thanks for any help
Code:
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim my_value As Variant
Set rs = CurrentDb.OpenRecordset("Table1", dbOpenSnapshot, dbReadOnly)
Set rs1 = CurrentDb.OpenRecordset("Table2", dbOpenSnapshot, dbReadOnly)
Do Until rs.EOF
' my value a value in a 'Test1ID' in Table1'
my_value = rs![Test1ID]
Do Until rs1.EOF
If my_value = rs1![Test2ID] Then
' display a value in a different field in a record in Table2 that satisfy the condition
'
Debug.Print rs1![fldtest_inRecord2]
End If
rs1.MoveNext
Loop
rs.MoveNext
Loop