CODEWITHSUNDEEP
vb.netms-access
jsia
jsia

Reputation: 1

Access 2016 - Multiple text fields - Search as you type not working

I have an Access 2016 Form with 5 text boxes labeled txtprod, txtpri, txtcnt, txtph, txtmfg. I also have a query for my table for the product name, price, count, phone and manufacturer fields.

In my forms 5 text boxes, I would like to be able to auto-search as you type and have my list auto-populate.

I followed these tutorials https://www.youtube.com/watch?v=SJLQqwMOF08 https://www.youtube.com/watch?v=MwaRFjgwBW8

My form has a form load:

*Private Sub Form_Load() Dim task As String task = "SELECT * FROM pricingdata" Me!details.RowSource = task End Sub*

And my text box name has this event "on change"

*Private Sub txtprod_Change() Dim task As String task = "SELECT * FROM [pricingdata] WHERE ([Product Name] LIKE '" & Me.txtprod.Text & "');" Me!details.RowSource = task End Sub*

Search as I type works perfectly fine with just 1 text box. But when I add the following code to my Manufacturer text box event "on change" it doesn't work as intended.

*Private Sub txtmfg_Change() Dim task As String task = "SELECT * FROM [pricingdata] WHERE ([Manufacturer] LIKE '" & Me.txtmfg.Text & "');" Me!details.RowSource = task End Sub*

Now when I type in my Product name text box, it searched products just fine. When I start typing in my Manufacturers text box, it completely disregards anything I've put into the Product name text box and starts searching as I type only for text in the Manufacturers text box field.

How can I get this setup so all text in all 5 text box fields contribute to the search filter being applied to my list?

Upvotes: 0

Views: 888

Answers (1)

ASH
ASH

Reputation: 20302

Something like this . . . 

Private Sub ComboSelect_Change()

    '  You need to use String delimiters if you want to use a Text Field like:
    '  Me.Filter "[ATextFieldInRecordSource] = """ & Me.FilterComboBox & """"

    '  For a Numeric Field, use something like this:
    '  Me.Filter "[ANumericFieldInRecordSource] = " & Me.FilterComboBox
    '  Me.FilterOn = True

    Me.[Customer_Query subform1].Form.Filter = "[Company_Name] Like '*" &
                     Replace(Me.ComboSelect.Text, "'", "''") & "*'"
    Me.[Customer_Query subform1].Form.FilterOn = True

End Sub

Notice a few things:

    The subform is named Customer_Query subform1’
    The combobox is named ComboSelect’
    Finally, the ‘like clause’ is used in combination with the wildcard character.
    Like '*" & Replace(Me.ComboSelect.Text, "'", "''") & "*'"

When you type text into the combobox, the results in the subform are dynamically re-queried.

enter image description here

Upvotes: 0

Related Questions