Results 1 to 7 of 7

Filter form from multiple combo boxes

  1. #1
    Bird_FAT is offline Novice
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    5

    Filter form from multiple combo boxes

    Been a while since I've used VBA in Access!!

    I currently have a form (frmKeys) that has the following sections:

    ======================
    Find Keys
    [Live/Test] [Software]
    - Both combo boxes
    [Bit Rate] [Version] - Both combo boxes
    [FIND] [CLEAR]
    ======================
    Keys
    [Keys
    ] - Subform
    [Notes]

    ======================
    Add Keys
    [Live/Test] [Software]
    - Both combo boxes
    [Bit Rate] [Version] - Both combo boxes

    ======================


    I've got the add sections working, but am having problems with the code for the 'FIND' section!

    I'm trying to get the code to filter the form based on the user's choice in the top four combo boxes.
    Here's the code I'm using (failing to, I should say. LOL!):

    Code:
    Private Sub btnFind_Click()
    
    Dim strFilter As String
    
    If Not IsNull(Me.cboLive) Then
       strFilter = strFilter & " And Live/Test = " & Me.cboLive
    End If
    
    If Not IsNull(Me.cboBit) Then
       strFilter = strFilter & " And 32/64bit = " & Me.cboBit
    End If
    
    If Not IsNull(Me.cboSoftware) Then
       strFilter = strFilter & " And Software = " & Me.cboSoftware
    End If
    
    If Not IsNull(Me.cboVersion) Then
       strFilter = strFilter & " And Version = " & Me.cboVersion
    End If
    
    ' remove redundant leading " And " from filter expression
        strFilter = Mid(strFilter, 6)
        
    ' filter form
        Me.Filter = strFilter
        Me.FilterOn = True
    
    End Sub
    When I click the Find button, all I get is an error and debugging points me to the line:
    Code:
        Me.Filter = strFilter
    Anybody able to point out where I've gone wrong?
    Please let me know what info I can give to clarify further!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,152
    Add this line right before the line that errors

    Debug.Print strFilter

    which will print out the finished string to the VBA Immediate window so you can examine it. If that doesn't point out the problem, post it here. Also, you're treating all the values as numeric. Are they? If any are text, the value needs to be surrounded by single quotes:

    strFilter = strFilter & " And Live/Test = '" & Me.cboLive & "'"
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,152
    Oh, and due to the inadvisable symbol in the name, you should bracket the field names with "/" in them.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  4. #4
    Bird_FAT is offline Novice
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    5

    Debug

    Thanks for the help Pbaldy,

    Unfortunately, it doesn't seem to work.

    I have changed the names of the badly named columns to more user friendly names for the script. I've also added the single and double quotes as you suggested.

    the code looks like this now:
    Code:
    Private Sub Command11_Click()
    
    Dim strFilter As String
    
    If Not IsNull(Me.cboLive) Then
    strFilter = strFilter & " And Format = '" & Me.cboLive & "'"
    End If
    
    If Not IsNull(Me.cboBit) Then
    strFilter = strFilter & " And Bitrate = '" & Me.cboBit & "'"
    End If
    
    If Not IsNull(Me.cboSoftware) Then
    strFilter = strFilter & " And Software = '" & Me.cboSoftware & "'"
    End If
    
    If Not IsNull(Me.cboVersion) Then
    strFilter = strFilter & " And Version = '" & Me.cboVersion & "'"
    End If
    
    
    ' filter form
    Debug.Print strFilter
        Me.Filter = strFilter
        Me.FilterOn = True
    
    End Sub
    Now it seems to filter the form, but the information in the bottom section doesn't change, and the subform has nothing in it.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,152
    For starters, you should put back in the bit that gets rid of the leading " And ". It sounds like this is properly filtering the form? What is the bottom section, and the subform? Typically a subform would stay in sync with the main form with master/child links. If that's not appropriate, you'll probably have to filter it too.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  6. #6
    Bird_FAT is offline Novice
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    5
    Quote Originally Posted by pbaldy View Post
    It sounds like this is properly filtering the form?
    It is now that I put the
    Code:
        strFilter = Mid(strFilter, 6)
    back in!

    Quote Originally Posted by pbaldy View Post
    Typically a subform would stay in sync with the main form with master/child links.
    This bits working fine!

    Thanks a lot for your help pbaldy.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,152
    Happy to help, and welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-30-2012, 07:43 AM
  2. Filter my form from combo box
    By Angate in forum Forms
    Replies: 3
    Last Post: 04-24-2010, 12:57 PM
  3. Help with form & combo boxes
    By rnjalston in forum Forms
    Replies: 1
    Last Post: 03-17-2010, 09:38 PM
  4. Using Cascading Combo Boxes to Filter a Query
    By skiptotheend in forum Queries
    Replies: 0
    Last Post: 10-13-2009, 05:57 AM
  5. Replies: 1
    Last Post: 03-01-2009, 07:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums