Access RecordSet is empty

Question

I got very simple Sub which is run by a Combobox in a Access Form. Combobox shows all values from a column from Products table. Based on that selection then other values should used to populate text boxes in this Form. So basic data seeking case.

Private Sub ComboProductNameSearch_AfterUpdate()

    Dim rs As Object
    Dim strSearchCriteria As String
    Dim strSearchName As String

    Set rs = Me.Recordset.Clone
strSearchName = Me!ComboProductNameSearch.Value
strSearchCriteria = "Product name like '" & strSearchName & "'"

MsgBox (Me.Recordset.RecordCount)



rs.FindFirst strSearchCriteria
       If rs.NoMatch Then
           MsgBox "Record not found"
        Else
           Me.Bookmark = rs.Bookmark
           MsgBox "Record is found"
        End If

User will now select a product and what happens is that Recordset is empty. Code always runs to Record not found due to that. How can I access a correct Recordset ? My form will use a query to find all data from that Table. Query works. No linking to any other table or queries.

Watch : : strSearchCriteria : "Product name like 'Vehicle, person car'" : String : Form_frmAddProduct.ComboProductNameSearch_AfterUpdate

Watch : + : ComboProductNameSearch : "Vehicle, person car" : Object/ComboBox : Form_frmAddProduct.ComboProductNameSearch_AfterUpdate

Watch : : strSearchName : "Vehicle, person car" : String : Form_frmAddProduct.ComboProductNameSearch_AfterUpdate

Answer 1

Try with:

strSearchCriteria = "[Product name] = '" & strSearchName & "'"

or, if the field name is Product:

strSearchCriteria = "Product = '" & strSearchName & "'"

Also, normally the RecordsetClone is used:

Dim rs As DAO.Recordset
Dim strSearchCriteria As String
Dim strSearchName As String

Set rs = Me.RecordsetClone

If the form displays records, so will RecordsetClone.

Answer 2

Property Sheet of the Form

In my Form RecordSource is that very same table.

Private Sub ComboProductNameSearch_AfterUpdate()

    Dim rs As DAO.Recordset

    'Set rs = Me.Recordset.Clone
    Set rs = Me.RecordsetClone
    

   If rs.EOF Then
      MsgBox ("EOF no records")
   Else
      rs.MoveLast
      MsgBox ("Records found")
      rs.MoveFirst
   End If

MsgBox (Me.Recordset.RecordCount)

Still no fire no smoke EOF and 0 records.