Results 1 to 7 of 7
  1. #1
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86

    Filter a Form with Multiple Combo Boxes

    Thanks to anyone who can help!
    Here is my problem. I have a continous form with a list of item. Now I want to filter those items based on the selections of the 3 combo boxes in the header of the form. I have the combo boxes there and tried to borrowed some code and modify it. But it is not working.

    When I try to execute the code with the command button. I get this error.

    Run-tim error '3075'
    Missing),], or Item in query
    expression '([mfgName]= "136"

    The 136 in this error is the ID field of the table with my values in it. This field is not part fo the combo box, it should only be reading the name field. I tried changing mfgName to mfgID, but I still get the same error.

    I am also not sure about the string length defined below as some of my string lengths are much longer than 5 characters, so not sure I want to cut them off. I want to match them exactly, so I would think this part of the code could be eliminated.

    Here is the code attached to the command button:

    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.cmbFilterEquip) Then
    strWhere = strWhere & "([EquipName] = """ & Me.cmbFilterEquip & """) AND"
    End If

    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cmbFilterMfg) Then
    strWhere = strWhere & "([MfgName] = """ & Me.cmbFilterMfg & """) AND"
    End If



    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.cmbFilterCat) Then
    strWhere = strWhere & "([CatName] = """ & Me.cmbFilterCat & """) AND"
    End If



    '************************************************* **********************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '************************************************* **********************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then 'Nah: there was nothing in the string.
    MsgBox "No criteria", vbInformation, "Nothing to do."
    Else 'Yep: there is something there, so remove the " AND " at the end.
    strWhere = Left$(strWhere, lngLen)
    'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
    'Debug.Print strWhere

    'Finally, apply the string as the form's Filter.
    Me.Filter = strWhere
    Me.FilterOn = True
    End If

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You're not adding " And " to the end, you're only adding " And".
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    When changed them to " And "
    I do not get an error, but I do not get any results either. It clears my list and does not give me what I searched for, when the item is clearly in the list. Now What?

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    What are the field types of the fields "EquipName", "MfgName" and "CatName"?
    What is the data returned by the combo boxes? Examples??
    If the bound column of the combo boxes is a number, and the field types are text, the query will return an empty set.
    If the combo boxes are returning numbers, they should not be delimited with quotes....


    Have you un-commented the Debug line, set a breakpoint and looked at the string (in the immediate window) that is in strWhere?? If the field "EquipName" contains names, like "Kubota", and the combo box "Me.cmbFilterEquip" is returning a number 16, obviously nothing will be returned...

  5. #5
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    Field type data for the EquipName etc are all text.
    Equipment sample data - actuators, batteries, cable, pcv, etc, etc.
    Manfacturer sample data - 3M, ABB, Aerotek, etc, etc.
    Category sample data - EE, AQ, PD, IC etc, etc.
    There are two columns in the table behind the combo boxes, but I set the combo box to only read the one text "Name" column. Both columns however, are text fields, but do contain numbers the field I am not seeing in my combo box is a primary key field. I am suspecting that is is somehow trying to still read this field.
    I un-commented the Debug line, but it did not work either and I am no longer getting an error, I am just not getting any results.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I un-commented the Debug line, but it did not work either
    "strWhere" is empty????????
    What do you mean by "It did not work?
    Did you set a break point on the line after the "Debug" line and look at the immediate window??

    OK, would you post the SQL of the combo box "Me.cmbFilterEquip"??
    What is the bound field of the combo box?
    Is the bound field of the combo box a number or text field???

  7. #7
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    Ok, I changed all my filter boxes to queries (instead of tables) and they only bring back the text Name field, so that the ID field is completely out of the picture. Now the filters work fine.

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

Similar Threads

  1. Filter Report by Form using combo boxes
    By TubbzUK in forum Reports
    Replies: 3
    Last Post: 12-11-2012, 01:18 PM
  2. Replies: 5
    Last Post: 07-26-2012, 02:30 PM
  3. code needed to filter form using combo boxes
    By drjim in forum Programming
    Replies: 1
    Last Post: 06-29-2012, 01:50 PM
  4. using multiple combo boxes in one form
    By quandore in forum Access
    Replies: 5
    Last Post: 01-30-2012, 03:03 AM
  5. Filter form from multiple combo boxes
    By Bird_FAT in forum Programming
    Replies: 6
    Last Post: 05-19-2010, 09:32 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
  •  
Other Forums: Microsoft Office Forums