simple use recordset to find text box value on report

Question

I'm trying to learn how to use recordsets in VBA and starting here. I want to lookup the value from the ProductVars table and populate to a text box on a report for each record [ProductID].

The value I want is where Field [Name]="Hinging" and I need it to send the value from the Field [Value] to the txtHinge text box on the report.

Here is my current code.

Private Sub Report_Load()

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb

'Open a table-type Recordset
Set rs = db.OpenRecordset("ProductVars")

'Find the value of Hinging from the Name field Name and populate to txtHinge 
for the ProductID

Do Until rs.EOF
Me.txtHinge = rs!Name.Hinging.Value
rs.MoveNext
Loop

End Sub

Any help would be greatly appreciated.

Answer 1

In textbox ControlSource:

=DLookup("[Value]", "PRODUCTVARS", "[Name]='Hinging' AND ProductID=" & [ProductID])

Or if multiple dynamic parameters must be considered:

=DLookup("[Value]", "PRODUCTVARS", "[Name]='" & [Name] & "' AND ProductID=" & [ProductID])

If the latter is the case, possibly could just include the PRODUCTVARS table in the report RecordSource with a compound join.