Access VBA Keyword Search Function on multiple fields

Question

I currently have a simple Access database with forms for the users to fill out based off a queried table.

My goal is to use a search box that can filter results based off a keyword multiple times. My existing code works great for a single search on 1 field. I want to be able to drill down off the first search by searching off another field. After I select my field from combo box and search keyword, my results are displayed. Once I pick another field from the same box and search, the results do not include my 1st filter.

On the form, I already have a combo box with a list of all the fields to choose from. Then next to that is a text box for the user to search off the chosen field list. I have correct VBA code to search off a single field, but I'd like to drill down from there. Basically, I want the ability to search a keyword on a selected field, and then be able to filter those results further by using the same search box again.

Example: On form, select "borrower" from drop down list and type "Smith" in search box, click search button. THEN I'd like to choose another field such as "Issue Category" from the same drop down list and type "late payment", then click search button. Thus, giving me all records containing the borrower Smith where issues exist of late payments.

I've been spending days on this and finally broke down to come here. I need to know what code I'm needing to add that would accomplish my goal of multiple searches without filter resetting. I am hoping you can help. Here is my code (Text35 is the text box and searchlist is the combobox list of field names):

Private Sub Search_Click()
Dim strSearchValue As String
strSearchValue = Me.Text35.Value

Select Case Me.searchlist.Value

        Case "Date"
            Me.Filter = "[Date] = #" & strSearchValue & "# "

        Case "Account number"
            Me.Filter = "[Account number] = #' & strSearchValue & '# "

        Case "Borrower"
            Me.Filter = "[Borrower] LIKE '*" & (Replace(strSearchValue, "'", "''")) & "*'"

        Case "Issue Category"
            Me.Filter = "[Issue Category] LIKE '*" & (Replace(strSearchValue, "'", "''")) & "*'"


End Select

Me.FilterOn = True

End Sub

What is the issue - error message, wrong result, nothing happens? — June7
If numeric, correct this to: Me.Filter = "[Account number] = " & strSearchValue & " ". — Gustav
The problem is that I cannot search more than once without the filters being reset. I can search off one field fine when clicking a button that contains this code. However, when choosing a new field from the list box and typing a new keyword, it gives me the new results from the entire table again. I want my 2nd search to drill down from the first search results. How can I achieve that? What additional code would I need to add to make this work? — DJ2904

user11710430 user11710430 · Accepted Answer · 2019-06-27T19:31:23

-1

votes

I think you would use the OR keyword instead of &

Access VBA Keyword Search Function on multiple fields

1 Answers