Start Free Trial

asked on

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.

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

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)

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

ASKER

Sorry, does not work. I'd better use the first code; I think that its problem due to punctuation...

ASKER

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 ("*").

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)

ASKER

Sorry, does not work.

What's the message ?

Nic;o)

ASKER

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.

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)

ASKER

The combos already have "All" as Default value: shouldn't this fix the problem?

ASKER

I encounter the same problem even if I manipulate only the value of 1 of the 3 combos (change ot from its previous value).

Nope, they should be "positioned" on that value and it needs to be "visible" when you open the form.

Nic;o)

Are your combo's "cascading" because they have a reference to another form combo in their rowsource ?

Nic;o)

ASKER

They pick values from a query which is based on the same table, but this should not interfere at my advice..

Can you print the SQL of the combo's and the value of me.filter when you get the error.

Nic;o)

ASKER

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

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)

ASKER

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.

ASKER

I got it: Field 3 is numeric, probably I need a different syntax in Filter3 to catch the value..

Yep, drop the surrounding single quotes.

Nic;o)

ASKER

Could you please spell it again..?

Post the code you use.

Nic;o)

ASKER

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

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)

ASKER

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.

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)

ASKER

Could you please provide me a single line usefule to remove the filter from the form?

me.filteron = false

ASKER

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?

Create a subform based on the [Query] and use:
Me.subformname.form.RecordSource = the SQL string

Nic;o)

ASKER

Sorry, does not work. Anyway, the original message said "you canceled the previous operation". Do you have any idea about this operation..?

Guess some other code will block this.
Retry with a separate form and just the combo code.

Nic;o)

ASKER

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.

The apply filter should hold the complete filter needed and the Me.filterOn will need to be true.

Nic;o)

ASKER

Only the 1st filter works, if put alone. The other two give the same error. Any idea..?

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)

ASKER

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.

Use:
Forms!<formname>

Nic;o)

ASKER

Sorry, does not work, even if the combos are in the same form..

Hmm, try using the statement:

DoCmd.OpenForm "Form_data",,,strFilter

After building the filter string

Nic;o)

ASKER

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. "

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)

ASKER

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".

ASKER

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial