Working SQL query generates "A RunSQL statement requires an argument consisting of an SQL statement" in VBA

Question

I'm trying to make a form in Access with a "search as you type" function to find a customer.
I'll double click the correct match in a listbox to update the customer info textboxes.

I succeeded the "search as you type" part, but not the listbox double click update form part.

I get the following RunSQL error:

"A RunSQL statement requires an argument consisting of an SQL statement"

The SQL statement works in a query.

Private Sub lstClient_DblClick(Cancel As Integer)
    Dim selectedItem, strSQL, strSQL1 As String
    Dim i As Integer
        
    i = lstClient.ListIndex
    selectedItem = lstClient.ItemData(i)
    txtCustName.Value = selectedItem
        
    strSQL = "SELECT tblClient.[Addresse] FROM tblClient WHERE tblClient.[Nom] ='" & selectedItem & "';"

    strSQL1 = "SELECT tblClient.[Addresse] FROM tblClient WHERE tblClient.[Nom] ='Bernache Funeral home';"
            
    DoCmd.RunSQL (strSQL1)
    Text191.Value = strSQL1
End Sub

The second statement (strSQL1) is the one I tried in the query to make sure it worked, and it did.

Answer 1

Try something like

Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL1)
Text191.Value = rs.Fields("Addresse").Value

Set rs = Nothing
Set db = Nothing