How to create custom filter based on user input in MS Access

Question

I have a form which has many controls on it. I would like to create custom filter that allows the user to search for any record on the form using any filed value as search criteria. For example: The user can search record using id or the can search using Grade, etc.

I have wriiten the follwing code for now :

    Private Sub CmdFind_Click()
    Dim filterStr As String
    Dim strWhere As String

      filterStr = InputBox("Enter your filter criteria")
      strWhere = "[SalesOrderNumber] = '" & filterStr & "' "

      Me.Filter = strWhere
      Me.FilterOn = True

      End Sub

However, this searches only for 'SalesOrderNumber'. I want the functionality to search using other values as well. Any help would be appreciated. Thankyou

Answer 1

As can I understand. You want to search for multiple fields.

In that case you may use OR clause:

Private Sub CmdFind_Click()

    Dim filterStr As String
        filterStr = InputBox("Enter your filter criteria")

    Dim filters(0 To 2) As String
        filters(0) = BuildCriteria("SalesOrderNumber", dbText, filterStr)
        filters(1) = BuildCriteria("UserPhoneNumber", dbText, filterStr)
        filters(2) = BuildCriteria("Comments", dbText, filterStr)

    Me.Filter = Join(filters, " OR ")
    Me.FilterOn = True

End Sub