VBA to change listbox.rowsource not working (1 Viewer)

Private Sub Form_Open(Cancel As Integer)
     Dim searchSQL As String
    
' the following commented out versions of setting searchSQL show what I
' have tried and what works vs what doesn't work.  I can't find a version
' of setting searchSQL = that works.  forms!frmSearch.txt1 evaluates to
' the string chem in my testing
    
'    this displays the whole table of rows in the listbox of this form
'    searchSQL = "select sopid, sopname, soplink from tblSOP"

'    this works also just to show it is not only adding a where that kills it
'    searchSQL = "select sopid, sopname, soplink from tblSOP where 1=1"
    
'    the next two display empty listbox with no columns
'    searchSQL = "select sopid, sopname, soplink from tblSOP where sopName like ""*" & Forms!frmsearch.txt1.Value & "*"""
'    debug.print searchSQL = select sopid, sopname, soplink from tblSOP where sopName like "*chem*"
'    searchSQL = """select sopid, sopname, soplink from tblSOP where sopName like ""*" & Forms!frmsearch.txt1.Value & "*"""""
'    debug.print searchSQL = "select sopid, sopname, soplink from tblSOP where sopName like "*chem*""


    resultsList.RowSource = searchSQL

' I have tried resultsList.requery here and also several variations of
' resultslist.recordsourcetype to no avail.  A test of the last two searchSQL
' variations using a testSQL(searchSQL) routine works fine showing two records
' in the immediate window.  somehow programatically setting rowsource evaluates quotes
' differently than sending it with openrecordset() (used in testsql)


End Sub

searchSQL = "select sopid, sopname, soplink from tblSOP where sopName like ""*" & Forms!frmsearch.txt1.Value & "*"""
'    debug.print searchSQL = select sopid, sopname, soplink from tblSOP where sopName like "*chem*"

SearchSql = "select sopid, sopname, soplink from tblSOP where sopName like '*" & Forms!frmsearch.txt1.Value & "*'"
debug.print SearchSql

select sopid, sopname, soplink from tblSOP where sopName like "*chem*"

Private Sub Form_Open(Cancel As Integer)
    Dim oArgs As String
    Dim searchSQL As String
    Dim qDef As QueryDef
    

' attempt with single quotes. listbox comes back empty but double click on searchSOP query and it works perfectly

    searchSQL = "select sopid, sopname, soplink " & _
            "from tblSOP where sopName like '*" _
            & Forms!frmsearch.txt1.Value & "*'"

' attempt with double quotes same result

'    searchSQL = "select sopid, sopname, soplink " & _
            "from tblSOP where sopName like ""*" _
            & Forms!frmsearch.txt1.Value & "*"""

'  everything exactly the same but no where clause and the listbox shows all table rows 

'    searchSQL = "select sopid, sopname, soplink from tblsop"
    If Not IsNull(Forms!frmsearchresults.OpenArgs) Then
        oArgs = Forms!frmsearchresults.OpenArgs
    End If
    Form.Caption = oArgs
    lblTitle.Caption = "SOP Search Results by " & oArgs

' delete the querydef if it exists

    DeleteQDef ("sopSearch") ' deletes it if it exists
' create the queryDef with the new search - this is working because I can change the field txt1 and the query in the query designer shows the change and it works with the new criteria

    Set qDef = CreateQDef("sopSearch", searchSQL)
    ' don't have anything really to do with the reference to qdef so discard
    ' since the query is  not permanent 

    Set qDef = Nothing
    resultsList.RowSource = "sopSearch"
    resultsList.Requery

    

End Sub

Dim searchSql As String
   searchSql = "select sopid, sopname from tblSOP where sopName like '*" & Me.txt1.Value & "*'"
   MsgBox searchSql
   resultsList.RowSource = searchSql

select sopid, sopname from tblSOP where sopName like '*Chem*'

Private Sub btnSearchName_Click()

    Dim sqlSearch As String
    Dim strWhere As String
    Dim strSep As String

    Select Case Me.Frame41

    Case 1
        strSep = " and "

    Case 2
        strSep = " or "

    End Select

    If Nz(Me.txtCriteria1, "") <> "" Then
        strWhere = strWhere & " SOPName Like ""*" & Me.txtCriteria1 & "*""" & strSep
    End If

    If Nz(Me.txtCriteria2, "") <> "" Then
        strWhere = strWhere & " SOPName Like ""*" & Me.txtCriteria2 & "*""" & strSep
    End If

    If Nz(Me.txtCriteria3, "") <> "" Then
        strWhere = strWhere & " SOPName Like ""*" & Me.txtCriteria3 & "*""" & strSep
    End If



    If Nz(strWhere, "") <> "" Then
        strWhere = " Where " & Left(strWhere, Len(strWhere) - Len(strSep))
    End If

    sqlSearch = "select SOPID, SOPName, SOPLink from tblSOP "


    Me.lstResults.RowSource = sqlSearch & strWhere

End Sub