Form lookup with query

[Matthew]

I am trying to set up a form field to go to the record that contains the
requested data. However, the input from the user relates to a query taking
data from two tables. Here is the query (if you want to see it):

SELECT (pmain.product & Left(woodtype.wood,1)) AS Expr1 FROM woodtype INNER
JOIN (pmain INNER JOIN psub ON pmain.ID=psub.pmain_ID) ON
woodtype.ID=psub.woodtype_ID;

I created a unbound combo box, and it worked correctly displaying the ID
field of one of the tables. However, I need a "user friendly" name displayed
that is the result of the above query.

Is there a way to have the VB code look at the ID field in the background,
or look up the ID field from the "user friendly" name?

I have the latter a shot, but don't know enough to make it work.

Private Sub Combo24_AfterUpdate()
' Find the record that matches the control.

'My addition
Set dbs1 = CurrentDb
Set qdf1 = dbs1.CreateQueryDef(Q1Name)
qdf1.SQL = "SELECT (pmain.product & Left(woodtype.wood,1)) AS Expr1 FROM
woodtype INNER JOIN (pmain INNER JOIN psub ON pmain.ID=psub.pmain_ID) ON
woodtype.ID=psub.woodtype_ID"

• 'Pre-written code
  Dim rs As Object
  
  Set rs = Me.Recordset.Clone
  rs.FindFirst qdf1.Expr1 " = " & Str(Nz(Me![Combo24], 0))
  If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  End Sub
  
  Thank you.
  
  Matthew