Solved

MS Access Filter Form using multiple combo boxes

Posted on 2009-05-05
7
2,501 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
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now