Form filter using multiple values in a control

[SirTKC]

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]

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

'----------------
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]

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]

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]

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]

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]

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

 

[niranjanprajapati23]

Any sample db to understood