Building dynamic filters

[Levi92]

Hi everybody!

I am currently building multiple dynamic filters for a split form and actually everything is working how I want it to work. What I don't like
is how unneccesarily complicated my solution probably is.





So the idea is that you simply use the text fields in the header row to filter the data in the table. The problem I came across is that no matter how
you apply a filter using VBA (DoCmd.ApplyFilter or Me.Filter), you automatically remove all active filters. This is very unfortunate when I want multiple filters to be active.

So here is what I have done to solve this problem for the "City" text control:

FirstName = Me.Text121.Value
LastName = Me.Text123.Value
Gender = Me.Text133.Value
City = Me.Text136.Value


If FirstName <> "" And FirstName <> "First Name Here" And LastName <> "" And LastName <> "Last Name Here" And Gender <> "" And Gender <> "Gender Here" Then
DoCmd.SetFilter
DoCmd.ApplyFilter , "[Gender] = '" & Gender & "' And [City] = '" & City & "' And [First Name] = '" & FirstName & "' And [Last Name] = '" & LastName & "'"
GoTo Sel
End If

And this code works for me. The thing is that this code assumes that all 3 text controls (ignoring City) have a value in them. So I also have to write the same code to take into account that maybe only 2 of the 4 text controls carry a value, and so on...

So what it all comes down to me is the following question: Isn't there a command to apply a filter that doesn't remove all other active filters? Would make my life much easier!

[Minty]

Generally the best method is to build your filter dynamically after each criteria update.
Allen Browne's search form is the defacto standard method given as an example. http://allenbrowne.com/ser-62.html

[ranman256]

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

[Levi92]

Building the condition for the filter with help of IsNull is a very clever thing to do! Thanks for your help!

While this is not what I had initially in mind (I wanted the table to update as soon as a value is added to one of the entry fields), it is the most efficient solution coding-wise.
I guess that when you come from Excel you have to make compromises here and there.

PS: Is there a way I can refer to a control button that is part of a form from inside a module? A module won't accept something like "Me.txtCity". The debugging process would be alot easier if I could code inside a module.

Edit: Let me take that back! Got it to work now. Just have to use the entire code for each button individually.