Search record from table with different table than the form's record source

[isumasama]

hi all,
i need to do a record search in a form where its Record Source is Operation,
but instead of searching the Operation, i need to search different table, say Remark..
i supposed the red highlighted code should be the place to set the Remark table, but

how to do this? if there's other way to do this i would like to try too.

Thanks

'SEARCH FOR THE RECORD IN Operation TABLE
   Dim rs As DAO.Recordset
   Set rs = Me.Recordset.Clone   
   rs.FindFirst BuildCriteria("[IDOp]", dbInteger, OpNameClick)
   If Not rs.NoMatch Then 'MATCH FOUND SET BOOKMARK
       Me.Bookmark = rs.Bookmark
   Else 'CLOSE FORM
       DoCmd.Close acForm, stDocName
   End If
   
   rs.Close
   Set rs = Nothing

[boblarson]

You don't need to use the search to find the record in the other table. You would use a SQL Statement and recordset to bring back the appropriate record if there was one:

'SEARCH FOR THE RECORD IN REMARK TABLE
   Dim strSQL As String
   Dim strWHERE As String
   Dim rs As DAO.Recordset
   
   ' I assume that BuildCriteria does NOT return the word WHERE at the beginning
   strWHERE = BuildCriteria("[IDOp]", dbInteger, OpNameClick)

   strSQL = "SELECT * FROM Remark WHERE " & strWHERE

   Set rs = CurrentDb.OpenRecordset(strSQL)
   If rs.RecordCount = 0 Then
     Msgbox "Record does not exist"
   Else
     ' do whatever here with the record you have found from the other table
     ' not sure what it is you really want or what to do with it.
   End If

   rs.Close
   Set rs = Nothing

[isumasama]

thanks bob