Form filter using multiple values in a control (1 Viewer)

SirTKC · Mar 20, 2019

Hi,

I am using this [On Change] very simple but useful script to filter a continuous form.

================
Me.Filter = "ID LIKE '" & "*" & S_ID.Text & "*" & "'"
Me.FilterOn = True
Me.Requery
Me.S_ID.SetFocus
Me.S_ID.SelStart = Len(S_ID.Text)
================
Works very well.

But I've been ask if its possible to use multiple values inside a control and perform a search based on the same principle.

Ex. [1,5,7] - In the same control and dynamicaly filter my form.

I thought about using commas to seperate values and then try to REPLACE. But not a chance. Once again I've hit the limit of my unknown...

Is there a guru here who ever tried that ?

Ranman256 · Mar 20, 2019

build the 'where' clause by cycling thru all the controls....
it executes after a find button CLICK event
if null, ignore.
if not, apply.

Code:

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string 

sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

SirTKC · Mar 20, 2019

Thanks Ranman for this piece of code.

What I am trying to achieve is to filter ONE numeric column based on numbers (seperated by commas) in a control on my form.

Am not trying to search in multiple column.

But your example is very interesting though. I will use this for something else !

pbaldy · Mar 20, 2019

If the user enters

1,3,5

you can filter with

"FieldName In(" & Me.Textbox & ")"

I would use the after update event rather than the change event, which fires with every keystroke.

SirTKC · Mar 20, 2019

Hi PBaldy

Thank you for your reply.

I have tried this without success.

==============
Me.Filter = "Tri_ID In(" & Me.Cat_Tri & ")"
Me.FilterOn = True
Me.Requery
Me.Cat_Tri.SetFocus
Me.Cat_Tri.SelStart = Len(Cat_Tri.Text)
==============

I was wondering how the IN function would split the commas ?
Also, the search control contains variables that may changes accordingly.

SirTKC · Mar 20, 2019

UPDATE

I made a slight change to your code PBaldy and it work beautifuly. I just had to add .Text

==============
Me.Filter = "Tri_ID In(" & Me.Cat_Tri.Text & ")"
Me.FilterOn = True
Me.Requery
Me.Cat_Tri.SetFocus
Me.Cat_Tri.SelStart = Len(Cat_Tri.Text)
==============

So many thanks guys !

pbaldy · Mar 21, 2019

Happy to help! Like I said, I wouldn't use the change event thus wouldn't need the Text property.

niranjanprajapati23 · Apr 8, 2019

Any sample db to understood

Form filter using multiple values in a control (1 Viewer)

SirTKC

Art Systems Canada Inc.

Ranman256

Well-known member

SirTKC

Art Systems Canada Inc.

pbaldy

Wino Moderator

SirTKC

Art Systems Canada Inc.

SirTKC

Art Systems Canada Inc.

pbaldy

Wino Moderator

niranjanprajapati23

New member

Similar threads

Users who are viewing this thread