Help with Search code Please(RESOLVED)

[mhipate]

I want to display the results in a text box.
If I do a search by Title it will work, but searching
by Author it says it's not there ( same book)

Option Explicit
Private cn As ADODB.Connection
Private rs As ADODB.Recordset
 
Private Sub cmdSearch_Click()
 If Trim(txtAuthor.Text) <> "" Then
  'we have something in the text box
     rs.Open "SELECT * FROM ACT WHERE AUTHOR Like '" & Trim(txtAuthor.Text) & "'", cn, adOpenKeyset, adLockBatchOptimistic, adCmdText
   If rs.EOF Then
    'nothing was returned 
    lblNo.Caption = "No"
   Else
    'We have one or more results 
     With rs
     txtTitle.Text = .Fields("TITLE")
    End With
  End If
End If
rs.Close
End Sub

Can anyone see what I am doing wrong?
Thanks

[MartinLiss]

I added [vbcode][/vbcode] tags around your code to make it easier to read. You should do that each time you post code.

I don't know what the problem is but at most you will get only one title since you are not looping through the returned rs.

[D12Bit]

Try this:

Option Explicit
Private cn As ADODB.Connection
Private rs As ADODB.Recordset
 
Private Sub cmdSearch_Click()
    If Trim(txtAuthor.Text) <> "" Then
        'we have something in the text box
        rs.Open "SELECT * FROM ACT WHERE AUTHOR Like '%" & Trim(txtAuthor.Text) & "%'", cn, adOpenKeyset, adLockBatchOptimistic, adCmdText
    'Note the use of % ...Could be that the author name is in between of another chars...
        If rs.EOF Then
            'nothing was returned 
            lblNo.Caption = "No"
        Else
            'We have one or more results 
            txtTitle.Text = rs.Fields("TITLE") 'The use of With rs is effective with more replacements.
        End If
    End If
    rs.Close
End Sub