Results 1 to 9 of 9
  1. #1
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496

    Multiselect listbox items and display in form

    I want to make a listbox I have multiselect (thus far accomplished) but I want the results that I select to show in a report (or pdf) when I press a button.



    So I have filtered my results in the listbox, now I select them and click a button and the report only shows the ones I have selected. This allows me to filter out what I don't want.

    How would I go about this? What is the basic concept in achieving this? Does it require VBA?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Is the listbox to select multiple values from a single field? If Yes, then need VBA to construct a filter string with multiple parameters for the same field. Review http://allenbrowne.com/ser-50.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    how does the string contain multiple parameters? I mean one ID is 1-1 but getting something to store multiple selections and then asking the form to display them - how does that work? (read that form page already not quite sure, I even tried implementing it without success).

  4. #4
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    e.g.

    Code:
    'Loop through the ItemsSelected in the list box.    With Me.lstCategory
            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 & ","
                    'Build up the description from the text in the visible column. See note 2.
                    strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
                End If
            Next     End With
    I am guessing this pulls each ID and loops through collecting those ids?

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Yes, if ID is the value of listbox items.

    Allen's code actually builds a set of values that are then used with IN operator in the WHERE CONDTION of DoCmd.OpenReport.

    Where did your attempt fail - what happens?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by June7 View Post
    Yes, if ID is the value of listbox items.

    Allen's code actually builds a set of values that are then used with IN operator in the WHERE CONDTION of DoCmd.OpenReport.

    Where did your attempt fail - what happens?
    Allen was using a pre made query where I am just using a report with the same query as the listbox

    I got a can't locate object error.

    I tried replacing CategoryID with TeacherID and Category with the second field name in the query

    I did name the preview command button the same as well as the report the same as allens and the listbox with the same name.

    If it told me what was missing I could figure it out but it just doesn't tell me where the fault lies.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Doesn't seem to matter to me if the report RecordSource has the same datasource as the listbox. Still passing filter criteria to the report.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Click image for larger version. 

Name:	Capture.JPG 
Views:	18 
Size:	19.2 KB 
ID:	14357

    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 strWhere As String      'String to use as WhereCondition
        Dim strDescrip As String    'Description of 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 = "Products by Category"
    
        'Loop through the ItemsSelected in the list box.    
        With Me.lstCategory
            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 & ","
                    'Build up the description from the text in the visible column. See note 2.
                    strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
                End If
            Next
        End With
        
        'Remove trailing comma. Add field name, IN operator, and brackets.
        lngLen = Len(strWhere) - 1
        If lngLen > 0 Then
            strWhere = "[TeacherID] IN (" & Left$(strWhere, lngLen) & ")"
            lngLen = Len(strDescrip) - 2
            If lngLen > 0 Then
                strDescrip = "SchoolName: " & Left$(strDescrip, lngLen)
            End If
        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, WhereCondition:=strWhere, OpenArgs:=strDescrip
    
    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
    I don't see what else there is to change

    I have made the form name strDoc

    I have made the listbox has been named, the preview button has been named correctly.

  9. #9
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Nevermind - got it working using this:

    On Error GoTo Err_cmdOpenReport_Click


    Dim strWhere As String
    Dim ctl As Control
    Dim varItem As Variant


    'make sure a selection has been made

    If Me.lstCategory.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 employee"
    Exit Sub
    End If


    'add selected values to string
    Set ctl = Me.lstCategory
    For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & ctl.ItemData(varItem) & ","
    Next varItem
    'trim trailing comma
    strWhere = Left(strWhere, Len(strWhere) - 1)
    'open the report, restricted to the selected items
    DoCmd.OpenReport "strdoc", acPreview, , "TeacherID IN(" & strWhere & ")"


    Exit_cmdOpenReport_Click:
    Exit Sub


    Err_cmdOpenReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenReport_Click

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

Similar Threads

  1. Multiselect Listbox
    By wwhit in forum Forms
    Replies: 19
    Last Post: 03-09-2015, 02:58 PM
  2. Replies: 4
    Last Post: 06-24-2013, 07:34 AM
  3. Listbox multiselect status
    By Sam23 in forum Programming
    Replies: 5
    Last Post: 03-06-2012, 01:13 PM
  4. Append Records using MultiSelect Listbox
    By Ted C in forum Programming
    Replies: 14
    Last Post: 03-15-2011, 01:25 PM
  5. Replies: 9
    Last Post: 01-20-2011, 02:22 PM

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