asked on

Custom Search Filter on Access Split View Form

I have a Split Form in Access 2016. While the column header filters are useful we want a single input to quickly search for a partial match across a number of fields.

What we have done is placed an input and button in the Header (see screenshot below)
The input is called "txtSearch" and the button marked with label "GO" has an Embedded Macro assigned On Click
The macro is intended to allow a partial match against the Forename,Surname,OtherName and the WHERE condition has the following filter.

[txtSearch] Like "*" & [Forms]![ClientHeaderEDIT]![Forename] & "*" OR [txtSearch] Like "*" & [Forms]![ClientHeaderEDIT]![Surname] & "*" OR [txtSearch] Like "*" & [Forms]![ClientHeaderEDIT]![OtherName] & "*"

Open in new window

Whether we just include 1 field (forename) or all 3 it simply fails to apply the filter to the form.

So my question is ... is there something wrong with the way we are doing it OR is the split view form not capable of handling the filters in this manner? And if the answer is ..its not possible with Split Views ... how SHOULD we do it.

als315

Can you upload sample with thios form and related tables with some dummy data?

Eoin OSullivan

ASKER

@als315 - the form and table are part of a much bigger database and having to strip it down and clean up data isn't ideal for me. If you think what I want to achieve is possible .. just a probable coding error on my part then I might make the effort but first I need to know if what I'm trying to do is possible.

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eoin OSullivan

ASKER

Using your version of the VB code worked .. I was trying to use the "Event Procedure" with the Apply Filters element and it simply wouldn't work for me. This is a perfectly workable script .. I just added a second wildcard to allow it to search for matches anywhere in the 3 fields.

Thanks

Private Sub btnSearch_Click()
Dim s As String
Me.FilterOn = False
s = Me.txtSearch & ""
Me.Filter = "[Forename] Like '*" & s & "*' Or [Surname] Like '*" & s & "*' Or [OtherName] Like '*" & s & "*'"
Me.FilterOn = True
End Sub