?
Solved

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

Posted on 2005-03-10
46
Medium Priority
?
298 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:Sar1973
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 23
  • 21
  • +1
46 Comments
 
LVL 18

Expert Comment

by:Billystyx
ID: 13506265
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
 
LVL 54

Expert Comment

by:nico5038
ID: 13506450
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 13506546
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 9

Author Comment

by:Sar1973
ID: 13506575
Sorry, does not work. I'd better use the first code; I think that its problem due to punctuation...
0
 
LVL 9

Author Comment

by:Sar1973
ID: 13506606
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
 
LVL 54

Expert Comment

by:nico5038
ID: 13506759
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
 
LVL 9

Author Comment

by:Sar1973
ID: 13507232
Sorry, does not work.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13507346
What's the message ?

Nic;o)
0
 
LVL 9

Author Comment

by:Sar1973
ID: 13507406
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
 
LVL 54

Expert Comment

by:nico5038
ID: 13507541
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
 
LVL 9

Author Comment

by:Sar1973
ID: 13507600
The combos already have "All" as Default value: shouldn't this fix the problem?
0
 
LVL 9

Author Comment

by:Sar1973
ID: 13507622
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
 
LVL 54

Expert Comment

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

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13507690
Are your combo's "cascading" because they have a reference to another form combo in their rowsource ?

Nic;o)
0
 
LVL 9

Author Comment

by:Sar1973
ID: 13507821
They pick values from a query which is based on the same table, but this should not interfere at my advice..
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13508261
Can you print the SQL of the combo's and the value of me.filter when you get the error.

Nic;o)
0
 
LVL 9

Author Comment

by:Sar1973
ID: 13508430
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
 
LVL 54

Expert Comment

by:nico5038
ID: 13508603
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
 
LVL 9

Author Comment

by:Sar1973
ID: 13508664
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
 
LVL 9

Author Comment

by:Sar1973
ID: 13508700
I got it: Field 3 is numeric, probably I need a different syntax in Filter3 to catch the value..
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13508760
Yep, drop the surrounding single quotes.

Nic;o)
0
 
LVL 9

Author Comment

by:Sar1973
ID: 13508803
Could you please spell it again..?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13509115
Post the code you use.

Nic;o)
0
 
LVL 9

Author Comment

by:Sar1973
ID: 13514670
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
 
LVL 54

Expert Comment

by:nico5038
ID: 13514689
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
 
LVL 9

Author Comment

by:Sar1973
ID: 13514742
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
 
LVL 54

Expert Comment

by:nico5038
ID: 13514757
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
 
LVL 9

Author Comment

by:Sar1973
ID: 13514803
Could you please provide me a single line usefule to remove the filter from the form?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13514914
me.filteron = false
0
 
LVL 9

Author Comment

by:Sar1973
ID: 13535189
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
 
LVL 54

Expert Comment

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

Nic;o)
0
 
LVL 9

Author Comment

by:Sar1973
ID: 13536240
Sorry, does not work. Anyway, the original message said "you canceled the previous operation". Do you have any idea about this operation..?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13536629
Guess some other code will block this.
Retry with a separate form and just the combo code.

Nic;o)
0
 
LVL 9

Author Comment

by:Sar1973
ID: 13611453
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
 
LVL 54

Expert Comment

by:nico5038
ID: 13614751
The apply filter should hold the complete filter needed and the Me.filterOn will need to be true.

Nic;o)
0
 
LVL 9

Author Comment

by:Sar1973
ID: 13619690
Only the 1st filter works, if put alone. The other two give the same error. Any idea..?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13619738
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
 
LVL 9

Author Comment

by:Sar1973
ID: 13619970
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
 
LVL 54

Expert Comment

by:nico5038
ID: 13620216
Use:
Forms!<formname>

Nic;o)
0
 
LVL 9

Author Comment

by:Sar1973
ID: 13620301
Sorry, does not work, even if the combos are in the same form..
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13620371
Hmm, try using the statement:

DoCmd.OpenForm "Form_data",,,strFilter

After building the filter string

Nic;o)
0
 
LVL 9

Author Comment

by:Sar1973
ID: 13620594
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
 
LVL 54

Expert Comment

by:nico5038
ID: 13620647
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
 
LVL 9

Author Comment

by:Sar1973
ID: 13621181
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
 
LVL 9

Author Comment

by:Sar1973
ID: 13622539
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
 
LVL 54

Accepted Solution

by:
nico5038 earned 150 total points
ID: 13624682
Fields need to be in the form's recordsource.
Are you sure Field3 is defines as a YesNo field ?

Nic;o)
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question