record set not returning rows

[lvmoore]

I have created a adodb recordset that will be used to populate a subform. I know that the SQL statement is correct because when I feed it through a query I end up with the appropriate results. Unfortunately I am not returning any records when it is going through the code. I have set up a nr variable that is assigned the number of records from a getrows/array function and it always returns 0 -- even when I use the debug.print strSQL immediate window output to see there are many rows that should be returned. Why oh why??? Incidentally I also receive an error on my last argument but I think it is because there are no rows (although I will investigate why it is calling it 0). Main problem is that recordset that seems to be empty though.

Here is the code with my varied and many comments.

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

[ajetrumpet]

I know you are new here, so if you could, please check out this link and format your code for readability.

[lvmoore]

Take 2 -- thanks for pointing me in the right direction. I looked all around for something on the main board for "insert code" or something along those lines. Here is the code.

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

I have been having a hard time getting my head around concatenation, and the use of quotes and double quotes when dealing with strings in SQL so I used the strSQL line to try a different method of doing it (the Like clause and wildcards were starting to make my brain melt!) I think I may well end up using Allen Browne's FindAsUType function but this has been an excellent learning exercise for me. I have yet to add error messaging etc but am learning much about properties, adodb etc. I would like to figure out why I am experiencing this problem for future reference.

Related to this is that last row using the Recordset property. I am not entirely clear on when to use recordset and when to use rowsource for situations such as this. Anyway...the failure to return rows is my most frustrating issue!

[lvmoore]

Solved by changing wildcard * to % as ado uses % -- which is why it worked through the query builder but didn't return any records.