Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    dmadiola is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Nov 2013
    Posts
    10

    Exclamation Allen Browne's Use a multi-select list box to filter a report with Two List Boxes

    Has anyone had experience with Allen Browne's "Use a multi-select list box to filter a report" solution, in particularly with two multi-select list boxes? The code works fine for me for either box so long as I code it for one box alone. Combining the two into one code results in a type mismatch error. I'm trying to use the code to pass the contents of both multi-select boxes as Where conditions to a report. Both boxes are based on number fields. To try to isolate the problem, I've removed Allen's setDescription and OpenArgs conditions. We're unfortunately still on Access 2003 as the company desires to squeeze every dime by using until end-of-life next year.



    Code:
    Private Sub cmdPreview_Click()
    On Error GoTo Err_Handler
        'Purpose:  Open the report filtered to the items selected in the list box.
        'Author:   Allen J Browne, 2004.   http://allenbrowne.com
        Dim varItem As Variant      'Selected items
        Dim varItm As Variant      'Selected items
        Dim strWhere As String      'String to use as WhereCondition
        Dim strThere As String      'String to use as WhereCondition
        Dim lngLen As Long          'Length of string
        Dim strDelim As String      'Delimiter for this field type.
        Dim strDoc As String        'Name of report to open.
        
        'strDelim = """"            'Delimiter appropriate to field type. See note 1.
        strDoc = "PriceLetterNew"
        'Loop through the ItemsSelected in the list box.
        With Me.ProductTypeList
            For Each varItem In .ItemsSelected
                If Not IsNull(varItem) Then
                    'Build up the filter from the bound column (hidden).
                    strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                End If
            Next
        End With
        'Remove trailing comma. Add field name, IN operator, and brackets.
        lngLen = Len(strWhere) - 1
        If lngLen > 0 Then
            strWhere = "[TypeCode] IN (Left$(strWhere, lngLen))"
        End If
        With Me.MfgList
            For Each varItm In .ItemsSelected
                If Not IsNull(varItm) Then
                    'Build up the filter from the bound column (hidden).
                    strThere = strThere & strDelim & .ItemData(varItm) & strDelim & ","
                End If
            Next
        End With
        'Remove trailing comma. Add field name, IN operator, and brackets.
        lngLen = Len(strThere) - 1
        If lngLen > 0 Then
            strThere = "[MfgCode] IN (Left$(strThere, lngLen))"
        End If
        'Report will not filter if open, so close it. For Access 97, see note 3.
        If CurrentProject.AllReports(strDoc).IsLoaded Then
            DoCmd.Close acReport, strDoc
        End If
        'Omit the last argument for Access 2000 and earlier. See note 4.
        DoCmd.OpenReport strDoc, acViewPreview, strWhere And strThere
    Exit_Handler:
        Exit Sub
    Err_Handler:
        If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
            MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
        End If
        Resume Exit_Handler
    End Sub

  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
    More like:

    DoCmd.OpenReport strDoc, acViewPreview, , strWhere & " And " & strThere

    This may help debug the string:

    BaldyWeb-Immediate window
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    dmadiola is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Nov 2013
    Posts
    10
    More like:

    DoCmd.OpenReport strDoc, acViewPreview, , strWhere & " And " & strThere
    Thank you so much for your quick reply.

    With your suggested change, I now get Error 3075- Syntax Error (missing operator) in query expression '( And )'.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    And what is the result of using the Debug method I gave you on the string?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    dmadiola is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Nov 2013
    Posts
    10
    Since I haven't had experience with inserting Debug.Print, where should it be placed and what is/are the functions/arguments I should include following the Debug.Print?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The link didn't demonstrate it well enough? After the strings are built, this type of thing:

    Debug.Print strWhere & " And " & strThere

    or to examine one of them alone

    Debug.Print strWhere

    or to get fancier

    Debug.Print "strWhere contains: " & strWhere
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    dmadiola is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Nov 2013
    Posts
    10
    When I include Debug.Print strWhere & " And " & strThere just after strings are built and run code, Immediate Window displays And (my emphasis added).

  8. #8
    dmadiola is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Nov 2013
    Posts
    10
    And, I get no debugging errors when strings are run alone (e.g., Debug.Print strWhere).

  9. #9
    dmadiola is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Nov 2013
    Posts
    10
    When I insert Debug.Print "strThere contains: " & strThere and select a value in that respective list box, I get Error 3075- Syntax Error (missing operator) in query expression '( And [MfgCode] IN (Left$strThere, lngLen)))'. And Immediate Window displays strThere contains: [MfgCode] IN (Left$(strThere, lngLen)). Same error messages when switched to strWhere and selecting value from its respective list box.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That should have pointed you to the problem. Try this on both

    strWhere = "[TypeCode] IN (" & Left$(strWhere, lngLen) & ")"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    dmadiola is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Nov 2013
    Posts
    10
    I get now when running just Debug.Print strThere and selecting values in listbox, Error 3075- Extra ) in query expression '([TypeCode] IN (2000,900) And)'.

  12. #12
    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've added parentheses I didn't have, plus if it ends like that you have to account for selections not being made in both listboxes.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    dmadiola is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Nov 2013
    Posts
    10
    Here's the most recent code that has generated latest errors. I can't see where I have added more parantheses than you suggested. However, I do agree that since And seems to pop up repeatedly in errors including when I select nothing in both boxes that I'm not accounting for no selections being made in both or either boxes. Would you please be willing to suggest a solution?

    Code:
    Private Sub cmdPreview_Click()
    On Error GoTo Err_Handler
        'Purpose:  Open the report filtered to the items selected in the list box.
        'Author:   Allen J Browne, 2004.   http://allenbrowne.com
        Dim varItem As Variant      'Selected items
        Dim varItm As Variant      'Selected items
        Dim strWhere As String      'String to use as WhereCondition
        Dim strThere As String      'String to use as WhereCondition
        Dim lngLen As Long          'Length of string
        Dim strDelim As String      'Delimiter for this field type.
        Dim strDoc As String        'Name of report to open.
        
        'strDelim = """"            'Delimiter appropriate to field type. See note 1.
        strDoc = "PriceLetterNew"
        'Loop through the ItemsSelected in the list box.
        With Me.ProductTypeList
            For Each varItem In .ItemsSelected
                If Not IsNull(varItem) Then
                    'Build up the filter from the bound column (hidden).
                    strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                End If
            Next
        End With
        
        'Remove trailing comma. Add field name, IN operator, and brackets.
        lngLen = Len(strWhere) - 1
        If lngLen > 0 Then
            strWhere = "[TypeCode] IN (" & Left$(strWhere, lngLen) & ")"
        End If
        
        With Me.MfgList
            For Each varItm In .ItemsSelected
                If Not IsNull(varItm) Then
                    'Build up the filter from the bound column (hidden).
                    strThere = strThere & strDelim & .ItemData(varItm) & strDelim & ","
                End If
            Next
        End With
        
        'Remove trailing comma. Add field name, IN operator, and brackets.
        lngLen = Len(strThere) - 1
        If lngLen > 0 Then
            strThere = "[MfgCode] IN (" & Left$(strThere, lngLen) & ")"
        End If
        
        'Report will not filter if open, so close it. For Access 97, see note 3.
        If CurrentProject.AllReports(strDoc).IsLoaded Then
            DoCmd.Close acReport, strDoc
        End If
        
        'Omit the last argument for Access 2000 and earlier. See note 4.
        DoCmd.OpenReport strDoc, acViewPreview, , strWhere & " And " & strThere
        
    Exit_Handler:
        Exit Sub
    Err_Handler:
        If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
            MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
        End If
        Resume Exit_Handler
    End Sub
    Thanks repeatedly for your help!

  14. #14
    dmadiola is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Nov 2013
    Posts
    10
    It's apparently picking up the & ")" at end of each string when nothing selected?

  15. #15
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It looks like the error occurs if only one list box has selections.
    You need to check if both list boxes have selections of just one list box has selections.
    Currently you have a string to filter the report " ,strWhere & " And " & strThere"

    If both variables have data, then the filter string looks like "[TypeCode] IN (1,2) And [MfgCode] IN (1,2)"
    If only "strWhere" has data, then the filter string looks like " [TypeCode] IN (1,2) And " , which will throw an error
    If only "strThere" has data, then the filter string looks like " And [MfgCode] IN (1,2)" , which will throw an error

    So I modified your code......
    Code:
    Private Sub cmdPreview_Click()
       On Error GoTo Err_Handler
       'Purpose:  Open the report filtered to the items selected in the list box.
       'Author:   Allen J Browne, 2004.   http://allenbrowne.com
       Dim varItem As Variant      'Selected items
       Dim varItm As Variant      'Selected items
       Dim strWhere As String      'String to use as WhereCondition
       Dim strThere As String      'String to use as WhereCondition
       Dim lngLen As Long          'Length of string
       Dim strDelim As String      'Delimiter for this field type.
       Dim strDoc As String        'Name of report to open.
       Dim strCriteria As String   'String to use as the criteria to open report
    
       'strDelim = """"            'Delimiter appropriate to field type. See note 1.
       strDoc = "PriceLetterNew"
       'Loop through the ItemsSelected in the list box.
       With Me.ProductTypeList
          For Each varItem In .ItemsSelected
             If Not IsNull(varItem) Then
                'Build up the filter from the bound column (hidden).
                strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
             End If
          Next
       End With
    
       'Remove trailing comma. Add field name, IN operator, and brackets.
       lngLen = Len(strWhere) - 1
       If lngLen > 0 Then
          strWhere = "[TypeCode] IN (" & Left$(strWhere, lngLen) & ")"
       End If
       '   Debug.Print strWhere
    
    
       With Me.MfgList
          For Each varItm In .ItemsSelected
             If Not IsNull(varItm) Then
                'Build up the filter from the bound column (hidden).
                strThere = strThere & strDelim & .ItemData(varItm) & strDelim & ","
             End If
          Next
       End With
    
    
       'Remove trailing comma. Add field name, IN operator, and brackets.
       lngLen = Len(strThere) - 1
       If lngLen > 0 Then
          strThere = "[MfgCode] IN (" & Left$(strThere, lngLen) & ")"
       End If
       '   Debug.Print strThere
    
       'Report will not filter if open, so close it. For Access 97, see note 3.
       If CurrentProject.AllReports(strDoc).IsLoaded Then
          DoCmd.Close acReport, strDoc
       End If
    
       '      Debug.Print strWhere & " And " & strThere
    
       'check to see if both strWhere and strThere have values
       If Len(strWhere) > 0 And Len(strThere) > 0 Then
          strCriteria = strWhere & " And " & strThere
       ElseIf Len(strWhere) > 0 Then
          'only strWhere has values
          strCriteria = strWhere
       ElseIf Len(strThere) > 0 Then
          'only strThere has values
          strCriteria = strThere
       End If
    
       'Omit the last argument for Access 2000 and earlier. See note 4.
       DoCmd.OpenReport strDoc, acViewPreview, , strCriteria
    
    Exit_Handler:
       Exit Sub
    Err_Handler:
       If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
          MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
       End If
       Resume Exit_Handler
    End Sub

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 08-15-2012, 04:05 PM
  2. Replies: 5
    Last Post: 07-26-2012, 12:59 PM
  3. 2 multi select list boxes on one search form
    By woodey2002 in forum Forms
    Replies: 2
    Last Post: 11-05-2010, 12:44 PM
  4. Multi-select List Boxes
    By Rawb in forum Programming
    Replies: 6
    Last Post: 09-21-2010, 09:02 AM
  5. Replies: 1
    Last Post: 03-01-2009, 09: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
  •  
Other Forums: Microsoft Office Forums