Solved How to create a dynamic search filter using text box and multiple filters on a continuous form

[SparkieDEV]

Hi All,

I have been working on making improvements to an Asset Management Database that I have been building for some months now. I am finding it quite difficult to create a dynamic search filter using a text box to search both text and date fields on a KeyUp function. I would like your help please:

I am searching multiple fields as you can see below, most of them are text fields. I am however unable to find a solution that works when ALSO searching for a date within the textbox [AuditDate].

.Format(AuditDate,"dd/mm/yy") Like

doesn't work.

Thank you in advance.

Private Sub TB_AuditSearch_KeyUp(KeyCode As Integer, Shift As Integer)
On Error GoTo errHandler

Dim filterText As String

'Apply or update filter based on user input.
If Len(TB_AuditSearch.Text) > 0 Then
   filterText = TB_AuditSearch.Text
   Me.Form.Filter = "[TBLAuditDB]![AUDITID] LIKE '*" & filterText & "*' OR [TBLAuditDB]![AuditDate] LIKE '*" & filterText & "*' OR [TBLAuditDB]![AssetName] LIKE '*" & filterText & "*' OR [TBLAuditDB]![REF] LIKE '*" & filterText & "*' OR [TBLAuditDB]![Type] LIKE '*" & filterText & "*' OR [TBLAuditDB]![Status] LIKE '*" & filterText & "*' OR [TBLAuditDB]![RAGID] LIKE '*" & filterText & "*' OR [TBLAuditDB]![Period] LIKE '*" & filterText & "*'"
   Me.FilterOn = True
  'Retain filter text in search box after refresh.
   TB_AuditSearch.Text = filterText
   TB_AuditSearch.SelStart = Len(TB_AuditSearch.Text)
Else
   ' Remove filter.
   Me.Filter = ""
   Me.FilterOn = False
   TB_AuditSearch.SetFocus
End If

Exit Sub

errHandler:

MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."

End Sub

 

[Uncle Gizmo]

I use a pop-up form:-

Build an Advanced Search Form - Nifty Access

Why Build a Search Form? I answered This question:- Multi field search-box on Access World Forums (AWF) where the OP asked about using a list box to search a table. I didn't think a LISTBOX was the right solution, so I demonstrated this "Nifty Search Form" by adapting it to the OP's Database...

www.niftyaccess.com

 

[Gasman]

Start with Allen Browne's page.
http://allenbrowne.com/ser-62.html

 

[arnelgp]

Private Sub TB_AuditSearch_KeyUp(KeyCode As Integer, Shift As Integer)
On Error GoTo errHandler

Dim filterText As String
Dim strDateFilter
'Apply or update filter based on user input.
If Len(TB_AuditSearch.Text) > 0 Then
   filterText = TB_AuditSearch.Text
   If InStr(Format(filterText, "\#mm\/dd\/yyyy\#"), "#") > 0 Then
       Me.Form.Filter = "[TBLAuditDB]![AUDITID] LIKE '*" & filterText & "*' OR [TBLAuditDB]![AuditDate] LIKE '*" & filterText & "*' OR [TBLAuditDB]![AssetName] LIKE '*" & filterText & "*' OR [TBLAuditDB]![REF] LIKE '*" & filterText & "*' OR [TBLAuditDB]![Type] LIKE '*" & filterText & "*' OR [TBLAuditDB]![Status] LIKE '*" & filterText & "*' OR [TBLAuditDB]![RAGID] LIKE '*" & filterText & "*' OR [TBLAuditDB]![Period] LIKE '*" & filterText & "*'"
   Else
        Me.Form.Filter = "[TBLAuditDB]![AUDITID] LIKE '*" & filterText & "*' OR [TBLAuditDB]![AssetName] LIKE '*" & filterText & "*' OR [TBLAuditDB]![REF] LIKE '*" & filterText & "*' OR [TBLAuditDB]![Type] LIKE '*" & filterText & "*' OR [TBLAuditDB]![Status] LIKE '*" & filterText & "*' OR [TBLAuditDB]![RAGID] LIKE '*" & filterText & "*' OR [TBLAuditDB]![Period] LIKE '*" & filterText & "*'"
    End If
   Me.FilterOn = True
  'Retain filter text in search box after refresh.
   TB_AuditSearch.Text = filterText
   TB_AuditSearch.SelStart = Len(TB_AuditSearch.Text)
Else
   ' Remove filter.
   Me.Filter = ""
   Me.FilterOn = False
   TB_AuditSearch.SetFocus
End If

Exit Sub

 

[SparkieDEV]

Private Sub TB_AuditSearch_KeyUp(KeyCode As Integer, Shift As Integer)
On Error GoTo errHandler

Dim filterText As String
Dim strDateFilter
'Apply or update filter based on user input.
If Len(TB_AuditSearch.Text) > 0 Then
   filterText = TB_AuditSearch.Text
   If InStr(Format(filterText, "\#mm\/dd\/yyyy\#"), "#") > 0 Then
       Me.Form.Filter = "[TBLAuditDB]![AUDITID] LIKE '*" & filterText & "*' OR [TBLAuditDB]![AuditDate] LIKE '*" & filterText & "*' OR [TBLAuditDB]![AssetName] LIKE '*" & filterText & "*' OR [TBLAuditDB]![REF] LIKE '*" & filterText & "*' OR [TBLAuditDB]![Type] LIKE '*" & filterText & "*' OR [TBLAuditDB]![Status] LIKE '*" & filterText & "*' OR [TBLAuditDB]![RAGID] LIKE '*" & filterText & "*' OR [TBLAuditDB]![Period] LIKE '*" & filterText & "*'"
   Else
        Me.Form.Filter = "[TBLAuditDB]![AUDITID] LIKE '*" & filterText & "*' OR [TBLAuditDB]![AssetName] LIKE '*" & filterText & "*' OR [TBLAuditDB]![REF] LIKE '*" & filterText & "*' OR [TBLAuditDB]![Type] LIKE '*" & filterText & "*' OR [TBLAuditDB]![Status] LIKE '*" & filterText & "*' OR [TBLAuditDB]![RAGID] LIKE '*" & filterText & "*' OR [TBLAuditDB]![Period] LIKE '*" & filterText & "*'"
    End If
   Me.FilterOn = True
  'Retain filter text in search box after refresh.
   TB_AuditSearch.Text = filterText
   TB_AuditSearch.SelStart = Len(TB_AuditSearch.Text)
Else
   ' Remove filter.
   Me.Filter = ""
   Me.FilterOn = False
   TB_AuditSearch.SetFocus
End If

Exit Sub

Thank you for this solution, works just fine.

Note, after posting the code I tidied it up somewhat. Again, Thanks.

 

[arnelgp]

you're welcome!

 

[Estuardo416]

Hi,
Not wanting to step on anyone toes, i would like to suggest that when building a search component that needs to update as soon as you type Avoid to:
1. Use key/up/down/press whenever possible.
2. Sql strings
3. IFs and Cases for criteria/parameters*

The reason:
1
Key press/down won't report back after the key is up. If you type "Me" those events just will register M. So your search string will be incomplete.
KeyUp is the only one that reports back as soon as you type, But... if you are typing with your numeric keyboard (ex. phone number) it won't give you back what you're expecting.
Let's say you hit 9 with your numeric keyboard, the keycode in my tests it always returns "i" same goes for the other numbers.
Also to have a descent UI you'll have to position the cursor at the end of the text with "SelStart" & "SelLength"

A possible solution:
Use the textbox OnChange event and get the typed string via the text property, like txtSearch.Text. If your query is linked to your form you should explicit add the text property to the expression: [Forms]![frmSearch]![txtSearch].[Text]

2
Sql strings, as we all know is a bad practice, are prone to errors, weak security, and famous for slow performance.

A possible solution:
Have a pre-built query and use hidden controls linked to the query if more than one parameter is needed

3
I left the "*" because if the search component will be reusable across the application it'll end up searching in different sources. In that case there is no choice but to change sources. If that's not the case:

A possible solution:
If the searching is within the same source (let's say a query) use as many "OR" as parameters needed. There it'll have the look & feel of a search engine. No need of IF's or Case's

Finally. The use of a form or a listbox for a search component, IMHO, It's more than anything, a matter of taste. Whenever possible i choose a listbox. Most of the times the results are read-only and all i need is just a key value to jump to somewhere else.

I hope it helps.
Cheers!