Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2512
  • Last Modified:

MS Access Filter Form using multiple combo boxes

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
Bama_Smitty
Asked:
Bama_Smitty
1 Solution
 
Simon BallCommented:
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
 
MikeTooleCommented:
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
 
MikeTooleCommented:
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!

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

etc?
0
 
Bama_SmittyAuthor Commented:
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
 
Simon BallCommented:
lol.  not even an assist?
0
 
Helen FeddemaCommented:
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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now