Solved

MS Access Filter Form using multiple combo boxes

Posted on 2009-05-05
7
2,502 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

919 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

17 Experts available now in Live!

Get 1:1 Help Now