CODEWITHSUNDEEP
ms-accesssearchfiltervba
DJ2904
DJ2904

Reputation: 11

Access VBA Keyword Search Function on multiple fields

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

Upvotes: 1

Views: 1983

Answers (1)

user11710430
user11710430

Reputation: 1

I think you would use the OR keyword instead of &

Upvotes: -1

Related Questions