Vba code for a search button in access form

[dannyboy7]

I have created a form with combo box and a search button to search for a book title entered in the combo box from the books table and display the other details of the book in a book subform.Pls I need the vba code for the search button. This is the code i tried

Private Sub Command19_Click()
 Dim LSQL  As String
    Dim LSearchString As String
 
    If Len(Combo15) = 0 Or IsNull(Combo15) = True Then
        MsgBox "You must enter a search string."
 
    Else
 
        LSearchString = Combo15
 
        'Filter results based on search string
        LSQL = "select * from book"
        LSQL = LSQL & " where bookID = " & LSearchString & ""
 
        Form_book.RecordSource = LSQL
 
        lblTitle.Caption = "Customer Details:  Filtered by '" & LSearchString & "'"
        'Clear search string
        Combo15 = ""
 
        MsgBox "Results have been filtered.  All Book Title containing " & LSearchString & "."
 
    End If
End Sub.
 

It gave an error on this line
'Form_book.RecordSource = LSQL'

[jimatqsi]

Danny,
Welcome to Bytes.com. You'll find lots of good help here. But you have to do your part by posting all the relevant information. To tell us there was an error without telling what error occurred is not very helpful. We can guess but it would go a lot smoother if we didn't have to guess.

You say a book title is being entered in the search string. But the search string is being tested against BookID. Is BookID actually a title? Things ending in ID are usually numeric values. So it may be that your error came about because you compared a numeric to a string value.

Your solution might be as simple as changing BookID to BookTitle (if there is a field by the name of BookTitle).

Further, you really don't need a Search button. You could put this same code in the After_Update event of the combo box and then the search would be done as soon as a value was entered, no need to click a button afterward.

I hope that helps.

Jim

[twinnyfo]

Danny,

First, my guess at to the nature of your error is that you are evaluating a String (LSearchString) for a probably numerical value (BookID), unless, of course, LSearchString has a Bound Column which is numerical--you have not indicated so.

It is also not typical to use a Combo Box as a Search text box, unless, of course, you also have set the Row Source of your combo box to the list of titles (and your bound column could be the BookID). This would allow the user to "Select" the Book Title, rather than "Enter" it. Then, in the AFterUpdate Event of your Combo Box (great advice, Jim!), all you need is this:

Me.SubFormName.Filter = "BookID = " & LSearchString
Me.SubFormName.FilterOn = True

Also, as a word of advice, you have named your Command Button "Command19", which, although it will work, may be confusing later on as your troubleshoot--or if someone else has to troubleshoot. It is always wise to name your controls something that will make sense, so you can find them on your forms and give others some insight as to what they might do.