adodb.recordset not returning rows

[lvmoore]

Using code to use content of unbound textbox control in an SQL statement to generate an adodb.recordset that will be used as the rowsource/recordset (?undecided which is preferred?) for the subform. I know that the SQL is correct because if I use the immediate window results from the debug.print in a query the correct records (and there are many) are returned but I receive the EOF, BOF true or record deleted message and the number of records (nr) comes back as 0 (I have it show up in the txttest control for ease on the form). Why oh why is this happening? I just cannot figure it out!!!! (PS I am aware of the FindAsUType function but I am sticking with this code until I find out [bold]WHY[/bold] I am experiencing this problem...otherwise I won't know what to do if it happens in a different context.)

Private Sub txtFindProduct_KeyUp(KeyCode As Integer, Shift As Integer)

Dim strProduct As String
Dim strsearch As String
Dim strtest As String
Dim rsIL As ADODB.Recordset     'item list recordset
Dim nr As Integer
Dim rsarray
Dim strSQL As String


    Me.Refresh      'refresh the form
    
    Me!txtFindProduct.SelStart = Me!txtFindProduct.SelLength 'move cursor to end of the selection

    strsearch = Chr(34) & "*" & Me!txtFindProduct & "*"  & Chr(34) 'incorporating the wildcards and quotes into the string

'SQL that will feed into the rsIL
    strSQL = "SELECT tblProducts.ItemDescription,tblProducts.Category, tblCategories.Category" _
        & " FROM tblCategories INNER JOIN tblProducts ON tblCategories.CatID = tblProducts.Category" _
        & " WHERE tblProducts.Itemdescription like " & strsearch & ""

        'Debug.Print strSQL

    Set rsIL = New ADODB.Recordset      'set an instance of the recordset
    
    rsIL.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic 'open the recordset
    
    'Debug.Print strSQL
    
     
    'if there are rows, count them.
        If Not rsIL.EOF Then
            rsarray = rsIL.GetRows()                        'takes you to end of rows
            nr = UBound(rsarray, 2) + 1                     'code to count the records in the array
        End If
    
    
    Me!txttest = nr
    

    Set Me![subfrmProductList].Form.Recordset = rsIL
    

End Sub                                                                                                                                  [IMG]http://www.accessforums.net/images/misc/progress.gif[/IMG]

 

[boblarson]

This part needs quotes:

& " WHERE tblProducts.Itemdescription like " & Chr(34) & strsearch & Chr(34)

 

[lvmoore]

I thought my "quotes" were OK since I included the Chr(34)s in the strSQL portion so they are already incorporated. Plus it logically cannot get my head around how it is possible that if the SQL I get from the debug.print/immediate window returns the correct rows why this just isn't working.

Do I understand you correctly that I need to add additional quotes (identified as the Chr(34) you have added) around the strSearch variable? I am going to fire up my other computer and try it right now. But logically, why doesn't the debug.print thing not work out?

 

[boblarson]

I thought my "quotes" were OK since I included the Chr(34)s in the strSQL portion so they are already incorporated. Plus it logically cannot get my head around how it is possible that if the SQL I get from the debug.print/immediate window returns the correct rows why this just isn't working.

Do I understand you correctly that I need to add additional quotes (identified as the Chr(34) you have added) around the strSearch variable? I am going to fire up my other computer and try it right now. But logically, why doesn't the debug.print thing not work out?

Post exactly what is coming out of the debug.print.

 

[lvmoore]

SELECT tblProducts.ItemDescription, tblProducts.Category, tblCategories.Category FROM tblCategories INNER JOIN tblProducts ON tblCategories.CatID=tblProducts.Category WHERE tblProducts.ItemDescription like "*ho*"

"ho" is what is currently in the txtFindProduct control

Pasted straight into the SQL of a query gives me 11 records but nothing in my rsIL.

(Hopefully I've typed that in with no spelling errors. I don't have an internet connection on the computer that I've been working on this with at the moment. The joys of no highspeed/wireless!)

 

[pbaldy]

An ADO recordset requires % as the wildcard instead of *. DAO and JET use *, which is why it works when you paste it into a query.

 

[lvmoore]

Brilliant!!!! Now...

Why does

Set Me![subfrmProductList].Form.Recordset = rsIL

generate the error
'7965':
The object you entered is not a valid Recordset property.

I did pretty much this exact same thing before (assigning an adodb.recordset as the recordset) but it was on the main form not a subform. I did receive some problems with the syntax for referring to the subform (what I used in Access 2003 doesn't seem happy in 2010) so I generated this (which seems to work) was created by using the expression builder and then brought it over into the code.

Ideas on the recordset property issue?

 

[boblarson]

You don't use the word set. You just do it.

Me![subfrmProductList].Form.Recordset = rsIL