Access 97 - Code for multiple filters in a form from its combos.

Hallo, here is my problem. I have a form taking data from a query and 3 combos in it, taking lists elsewhere.
I would like to write a code, applicable to each Combo after their update, the allows the form to view the data of the query applying 3 filter together.
I wrote this:

Private Sub ComboX_AfterUpdate()
Dim Filter1 As String
Dim Filter2 As String
Dim Filter3 As String
Dim Filter As String
      Filter1 = IIf(Combo1 = "All", "*", " & Field1 & ")
      Filter2 = IIf(Combo2 = "All", "*", " & Field2 & ")
      Filter3 = IIf(Combo3 = "All", "*", " & Field3 & ")
      Filter = [Field1] = Filter1 And [Field2] = Filter2 And [Field3] = Filter3
      Me.Filter = Filter    
      Me.FilterOn = True
End Sub

Unnfortunately, it does not work: could you help me featuring this? Thanx.
LVL 9
Sar1973Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BillystyxCommented:
Private Sub ComboX_AfterUpdate()
Dim Filter1 As String
Dim Filter2 As String
Dim Filter3 As String
Dim Filter As String
      Filter1 = IIf(Combo1 = "All", "*", " & Field1 & ")
      Filter2 = IIf(Combo2 = "All", "*", " & Field2 & ")
      Filter3 = IIf(Combo3 = "All", "*", " & Field3 & ")
      Filter = "[Field1] =" & Filter1 & " And [Field2] = " &Filter2" And [Field3] ="  &Filter3
      Me.Filter = Filter    
      Me.FilterOn = True
End Sub

try this

billystyx
0
nico5038Commented:
Hmm, I guess "All" should result in a WHERE Field1 like '*', but effectively you're better off dropping the field from the filter.

Personally I would use:

Dim strFilter as string

If Len(nz(me.combo1))  0 then
   if me.combo1 <> "All" then
       strFilter = strFilter & " AND Field1 = " & me.combo1
   endif
endif
If Len(nz(me.combo2))  0 then
   if me.combo2 <> "All" then
       strFilter = strFilter & " AND Field2 = " & me.combo2
   endif
endif
If Len(nz(me.combo3))  0 then
   if me.combo3 <> "All" then
       strFilter = strFilter & " AND Field1 = " & me.combo3
   endif
endif

If len(strFilter) > 0 then
   me.filter = mid(strFilter,5)
   me.filteron = true
else
   me.filteron = false
endif

Clear ?

Nic;o)
0
Arthur_WoodCommented:
I will GUARANTEE that this line:

      Filter = [Field1] = Filter1 And [Field2] = Filter2 And [Field3] = Filter3

is NOT doing what you think it should be doing.

In words, what do YOU think this is supposed to do.  Do not simply translate that line of code into English, but rather, tell me, in your own words, what you WANT that line to do.

AW
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Sar1973Author Commented:
Sorry, does not work. I'd better use the first code; I think that its problem due to punctuation...
0
Sar1973Author Commented:
I want that line:
1. to apply the 3 filters simultaneously;
2. use the strings determined before, so that in case the combo values are set to "All" the filters should be neutral ("*").
0
nico5038Commented:
Hmm, not wise, but try when the fields are numeric:

Private Sub ComboX_AfterUpdate()
Dim Filter1 As String
Dim Filter2 As String
Dim Filter3 As String
Dim Filter As String
      Filter1 = IIf(Combo1 = "All", "*", " & Field1 & ")
      Filter2 = IIf(Combo2 = "All", "*", " & Field2 & ")
      Filter3 = IIf(Combo3 = "All", "*", " & Field3 & ")
      Filter = "[Field1] like " & Filter1 & " And [Field2] like " &Filter2" And [Field3] like "  &Filter3
      Me.Filter = Filter    
      Me.FilterOn = True
End Sub

For text fields use:

Private Sub ComboX_AfterUpdate()
Dim Filter1 As String
Dim Filter2 As String
Dim Filter3 As String
Dim Filter As String
      Filter1 = IIf(Combo1 = "All", "*", " & Field1 & ")
      Filter2 = IIf(Combo2 = "All", "*", " & Field2 & ")
      Filter3 = IIf(Combo3 = "All", "*", " & Field3 & ")
      Filter = "[Field1] like '" & Filter1 & "' And [Field2] like '" &Filter2"' And [Field3] like '"  &Filter3 & "'"
      Me.Filter = Filter    
      Me.FilterOn = True
End Sub


Nic;o)
0
Sar1973Author Commented:
Sorry, does not work.
0
nico5038Commented:
What's the message ?

Nic;o)
0
Sar1973Author Commented:
It works like this:
Private Sub ComboX_AfterUpdate()
Dim Filter1 As String
Dim Filter2 As String
Dim Filter3 As String
Dim Filter As String
      Filter1 = IIf([Combo1] = "All", "*", [Combo1] )
      Filter2 = IIf([Combo2 = "All", "*", [Combo2])
      Filter3 = IIf([Combo3] = "All", "*", [Combo3])
      Filter = "[Field1] like '" & Filter1 & "' And [Field2] like '" & Filter2 & "' And [Field3] like '"  & Filter3  & "'"
      Me.Filter = Filter    
      Me.FilterOn = True
End Sub

The only problem is that, if I put this code for all the 3 combos, the code filters only after the choice of the first combo: as soon as I try to choose the value from the second combo, an error message saying "Run-time error 2001 You canceled the previous operation" and the debug highlights the "Me.FilterOn = True" row.
0
nico5038Commented:
You can only execute this code when all three combo's are filled.
Best to put in the form's OnOpen event:
me.combo1 = me.combo1.column(0,0)
me.combo2 = me.combo2.column(0,0)
me.combo3 = me.combo3.column(0,0)

This will force each combo to hold the value from the first entry. When you use "<All>" instead of "All" (also change the sub !) then this will be the first in general.

Nic;o)
0
Sar1973Author Commented:
The combos already have "All" as Default value: shouldn't this fix the problem?
0
Sar1973Author Commented:
I encounter the same problem even if I manipulate only the value of 1 of the 3 combos (change ot from its previous value).
0
nico5038Commented:
Nope, they should be "positioned" on that value and it needs to be "visible" when you open the form.

Nic;o)
0
nico5038Commented:
Are your combo's "cascading" because they have a reference to another form combo in their rowsource ?

Nic;o)
0
Sar1973Author Commented:
They pick values from a query which is based on the same table, but this should not interfere at my advice..
0
nico5038Commented:
Can you print the SQL of the combo's and the value of me.filter when you get the error.

Nic;o)
0
Sar1973Author Commented:
Me.Filter = "[Field1] LIKE 'Combo1' AND [Field2] LIKE 'Combo2' AND [Field3] LIKE 'Combo3'"

Combo1 SQL is: SELECT DISTINCTROW Table1.[Field1] FROM Table1 UNION SELECT 'All' AS [Field1] FROM [Table1];
0
nico5038Commented:
Looks like you use:
      Filter1 = IIf([Combo1] = "All", "*","[Combo1]" )
      Filter2 = IIf([Combo2] = "All", "*", "[Combo2]")
      Filter3 = IIf([Combo3] = "All", "*", "[Combo3]")
instead of:
      Filter1 = IIf([Combo1] = "All", "*", [Combo1])
      Filter2 = IIf([Combo2] = "All", "*", [Combo2])
      Filter3 = IIf([Combo3] = "All", "*", [Combo3])
or better:
      Filter1 = IIf(me.[Combo1] = "All", "*", me.[Combo1])
      Filter2 = IIf(me.[Combo2] = "All", "*", me.[Combo2])
      Filter3 = IIf(me.[Combo3] = "All", "*", me.[Combo3])
Besides that the Sub is named "ComboX" and you use Combo1 / Combo2 / combo3 in the code.

Nic;o)
0
Sar1973Author Commented:
No way. I am trying that now in Combo1_AfterUpdate and it gives the same error. It seems that this procedure accepts only the values of Combo1 and its further changes. When the values of Combo 2 and 3 are set to "All" it works, otherwise it doesn't.
0
Sar1973Author Commented:
I got it: Field 3 is numeric, probably I need a different syntax in Filter3 to catch the value..
0
nico5038Commented:
Yep, drop the surrounding single quotes.

Nic;o)
0
Sar1973Author Commented:
Could you please spell it again..?
0
nico5038Commented:
Post the code you use.

Nic;o)
0
Sar1973Author Commented:
Private Sub Combo1_AfterUpdate()
Dim Filter1 As String
Dim Filter2 As String
Dim Filter3 As String
Dim Filter As String
Filter1 = IIf(Me.Combo1= "(Tutti)", "*", Me.Combo1)
Filter2 = IIf(Me.Combo2= "(Tutti)", "*", Me.Combo2)
Filter3 = IIf(Me.Combo3= "(Tutti)", "*", Me.Combo3)
Filter = "[Field1] LIKE '" & Filter1 & "' AND [Field2] LIKE '" & Filter2 & "' AND [Field3] LIKE '" & Filter3 & "'"
Me.Filter = Filter
Me.FilterOn = True
End Sub
0
nico5038Commented:
Try:

 Private Sub Combo1_AfterUpdate()
Dim Filter1 As String
Dim Filter2 As String
Dim Filter3 As String
Dim Filter As String
Filter1 = IIf(Me.Combo1= "(Tutti)", "*", Me.Combo1)
Filter2 = IIf(Me.Combo2= "(Tutti)", "*", Me.Combo2)
Filter3 = IIf(Me.Combo3= "(Tutti)", "*", Me.Combo3)
Filter = "[Field1] LIKE '" & Filter1 & "' AND [Field2] LIKE '" & Filter2 & "' AND [Field3] LIKE " & Filter3
Me.Filter = Filter
Me.FilterOn = True
End Sub

Nic;o)
0
Sar1973Author Commented:
Sorry, does not work. Another error appears saying the value is not correct. I suspect that this code needs a line at its beginning which removes any filter from the query/form before running the other lines.
0
nico5038Commented:
Hmm, guess my first code is the better way to go:

Dim strFilter as string

If Len(nz(me.combo1))  0 then
   if me.combo1 <> "All" then
       strFilter = strFilter & " AND Field1 = '" & me.combo1 & "'"
   endif
endif
If Len(nz(me.combo2))  0 then
   if me.combo2 <> "All" then
       strFilter = strFilter & " AND Field2 = '" & me.combo2 & "'"
   endif
endif
If Len(nz(me.combo3))  0 then
   if me.combo3 <> "All" then
       strFilter = strFilter & " AND Field3 = " & me.combo3
   endif
endif

If len(strFilter) > 0 then
   me.filter = mid(strFilter,5)
   me.filteron = true
else
   me.filteron = false
endif

Nic;o)
0
Sar1973Author Commented:
Could you please provide me a single line usefule to remove the filter from the form?
0
nico5038Commented:
me.filteron = false
0
Sar1973Author Commented:
Sorry, does not work. I tried to do it as:

Me.RecordSource = "select * from [Query] WHERE [Field1] LIKE '" & Filter1 & "' AND [Field2] LIKE '" & Filter2 & "' AND [Field3] LIKE '" & Filter3 & "'"

but it produces the same error if I choose a value in Combo3 different from "All". (Run-time Error 2001: ypu canceled the previous operation).

What shall I do?
0
nico5038Commented:
Create a subform based on the [Query] and use:
Me.subformname.form.RecordSource = the SQL string

Nic;o)
0
Sar1973Author Commented:
Sorry, does not work. Anyway, the original message said "you canceled the previous operation". Do you have any idea about this operation..?
0
nico5038Commented:
Guess some other code will block this.
Retry with a separate form and just the combo code.

Nic;o)
0
Sar1973Author Commented:
Nico, I am now trying to do it like this:

Private Sub Button_Click()

DoCmd.OpenForm "Form_data"

Dim Filter1 As String
Dim Filter2 As String
Dim Filter3 As String
     
      Filter1 = IIf(Me.Combo1 = "All", "*", Me.Combo1)
      Filter2 = IIf(Me.Combo2 = "All", "*", Me.Combo2)
      Filter3 = IIf(Me.Combo3 = "All", "*", Me.Combo3)

DoCmd.ApplyFilter , "[Field1] = '" & Filtro1 & "'"
DoCmd.ApplyFilter , "[Field2] = '" & Filtro2 & "'"
DoCmd.ApplyFilter , "[Field3] = '" & Filtro3 & "'"

End Sub

The problem is that an error message appears saying "The applyfilter action was canceled - ...you clicked cancel in a dialog box..." even if there is no dialog box. Moreover, if I put it just with filter 1 it works.

Can you help me, please? Thanx.
0
nico5038Commented:
The apply filter should hold the complete filter needed and the Me.filterOn will need to be true.

Nic;o)
0
Sar1973Author Commented:
Only the 1st filter works, if put alone. The other two give the same error. Any idea..?
0
nico5038Commented:
Build ONE filter like:

Dim strFilter as string

If Len(nz(me.combo1))  0 then
   if me.combo1 <> "All" then
       strFilter = strFilter & " AND Field1 = '" & me.combo1 & "'"
   endif
endif
If Len(nz(me.combo2))  0 then
   if me.combo2 <> "All" then
       strFilter = strFilter & " AND Field2 = '" & me.combo2 & "'"
   endif
endif
If Len(nz(me.combo3))  0 then
   if me.combo3 <> "All" then
       strFilter = strFilter & " AND Field3 = " & me.combo3
   endif
endif

If len(strFilter) > 0 then
   me.filter = mid(strFilter,5)
   me.filteron = true
else
   me.filteron = false
endif

Nic;o)
0
Sar1973Author Commented:
The filter doesn't activate, since Fields 1, 2 and 3 are used in another form. Could you please spell the syntax of:
If len(strFilter) > 0 then
   Form!X.filter = mid(strFilter,5)
   Form!X.filteron = true
else
   Form!X.filteron = false
endif
Thanx.
0
nico5038Commented:
Use:
Forms!<formname>

Nic;o)
0
Sar1973Author Commented:
Sorry, does not work, even if the combos are in the same form..
0
nico5038Commented:
Hmm, try using the statement:

DoCmd.OpenForm "Form_data",,,strFilter

After building the filter string

Nic;o)
0
Sar1973Author Commented:
Fine, it works. One more question: how would you manipulate a flag field? My problem is that Combo3 contains 3 choices: "All", "Open", "Closed", while the corresponding field in the table is a flag (0, -1).
I put it as:

Dim strStatus As String
strStatus = IIf(me.Combo3 = "Open", 0, -1)
...
If Len(nz(me.combo3))  0 then
   if me.combo3 <> "All" then
       strFilter = strFilter & " AND Field3 = '" & strStatus & "'"
   endif
endif

But the following message appears: "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. "
0
nico5038Commented:
Try:

If me.combo3 = "Open" then
    strFilter = strFilter & " AND Field3 = False "
endif
If me.combo3 = "Closed"  then
    strFilter = strFilter & " AND Field3 = True "
endif

All other possibilities can be ignored.

Nic;o)
0
Sar1973Author Commented:
Sorry, the same error appears. Filters 1 and 2 work fine, alone and together, but Filter3 does not, even if applied alone..If I filter by form using Field3, the filter says "Field3 = 0" or "Field3 = -1".
0
Sar1973Author Commented:
Do you know if there is a different syntax in using the same code to filter a form based on a query instead of a table? The fields are the same..
0
nico5038Commented:
Fields need to be in the form's recordsource.
Are you sure Field3 is defines as a YesNo field ?

Nic;o)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.