Solved

MS Access Filter Form using multiple combo boxes

Posted on 2009-05-05
7
2,506 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

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!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

734 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