Solved

MS Access Filter Form using multiple combo boxes

Posted on 2009-05-05
7
2,505 Views
Last Modified: 2012-08-14
I've got a form with 3 combo boxes that i want the end user to select any combination of the 3 and click the run button and it filters the forms for those records.  the 3rd combo box is a little tricky since it involves multiple fields.  ex. the salesman has multiple salesman numbers that are associated in the customer file.  the combo box is set to list the names and bind it to salesman_nums field which might look like this "361","362","100".  the other 2 combo boxes are straight forward and contain a one to one relationship (select branch name and it's bound to branch_num which is in the customer table)  i can't get this to work at all.  
Private Sub btRunFilter_Click()
    'None
    If ([CmbBrn] = 0 And [CmbAM] = 0 And [CmbSls] = "") Then
    Me.FilterOn = False
    Else
    'Branch Only
        If ([CmbBrn] <> 0 And [CmbAM] = 0 And [CmbSls] = "") Then
    Me.Filter = "[Branch_num]='" & [CmbBrn] & "'"
    Me.FilterOn = True
    Else
    'Area Manager Only
            If ([CmbBrn] = 0 And [CmbAM] <> 0 And [CmbSls] = "") Then
    Me.Filter = "[Territory_num]='" & [CmbAM] & "'"
    Me.FilterOn = True
    Else
    'Salesman Only
                If ([CmbBrn] = 0 And [CmbAM] = 0 And [CmbSls] <> "") Then
    Me.Filter = "[Salesman_num]IN(" & [CmbSls] & ")"
    Me.FilterOn = True
    Else
     'Branch and Area Manager
                    If ([CmbBrn] <> 0 And [CmbAM] <> 0 And [CmbSls] = "") Then
    Me.Filter = "[Branch_num]='" & [CmbBrn] & "'" And "[Territory_num]='" & [CmbAM] & "'"
    Me.FilterOn = True
    Else
    'Branch and Salesman
                        If ([CmbBrn] <> 0 And [CmbAM] = 0 And [CmbSls] <> "") Then
    Me.Filter = "[Branch_num]='" & [CmbBrn] & "'" And "[Salesman_num]IN(" & [CmbSls] & ")"
    Me.FilterOn = True
    Else
    'Area Manager and Salesman
                            If ([CmbBrn] = 0 And [CmbAM] <> 0 And [CmbSls] <> "") Then
    Me.Filter = "[Salesman_num]IN(" & [CmbSls] & ")" And "[Territory_num]='" & [CmbAM] & "'"
    Me.FilterOn = True
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If
        
    Me.Refresh
End Sub

Open in new window

0
Comment
Question by:Bama_Smitty
[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
7 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 24311952
does the value in the combo contain multipoe fields - or is there a list box which the user can do multiple select on?

if its a combo showing multiple values, you'll need to build up a filter string with a where clause with "or" for each value..

also your nested IF is a night mare...

why not declare a string

dim strFilter as string...
strFilter = ""
then in each secion for the 3 combo's add new filter stuff to the string with and's or Or's
strFilter = strFilter " and [Salesman_num]IN(" & [CmbSls] & ")"

etc..

then at the end set
me.filter = strFilter
0
 
LVL 27

Accepted Solution

by:
MikeToole earned 500 total points
ID: 24312204
Going with Sudonim's suggestion:
dim strFilter as String
const cAnd as String = " AND "
    If ([CmbBrn] = 0 And [CmbAM] = 0 And [CmbSls] = "") Then
       Me.FilterOn = False
    Else
            If [CmbBrn] <> 0 Then
                strFilter = "[Branch_num]='" & [CmbBrn] & "'"
            End If
            If [CmbAM] <> 0 Then
                strFilter = strFilter & cAnd &  "[Territory_num]='" & [CmbAM] & "'"
            End If
            If [CmbSls] <> "" Then
                 strFilter = strFilter  & cAnd & " [Salesman_num]IN(" & [CmbSls] & ")"
            End If
            If Left(strFilter, len(cAnd)) = cAnd then
                 ' Drop the leading AND if there is one
                 strFilter = mid(strFilter, len(cAnd) + 1)
            End If
            me.Filter = strFilter                     ' I can't offhand remember whther setting this property automatically sets FilterOn = True
    End If
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 24312218
There should be spaces surrounding IN, I think :
[Salesman_num] IN (" & [CmbSls] & ")"
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:Simon Ball
ID: 24312229
does cmdSLS have speech mark and comma delimited values in?
e.g.
"212", "214", "232"

etc?
0
 

Author Comment

by:Bama_Smitty
ID: 24314953
the multiple fields in the salesman table is all contained in one field and is listed exactly as "212","214","232".  i was trying to avoid using the or since i'm assuming that would mean i would have to add columns to my table.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 24323174
lol.  not even an assist?
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 24908919
Is the field with values like "212","214","232" an Access 2007 multi-valued field, or a regular text field?  If it is not a multi-valued field, these values should really be broken out into a linked table.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

756 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