Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003

    Talking Unanswered: open report based on listbox selection(s)

    hello. i have a report that i am opening via a command button in a form. also in this form is a multiple selection listbox with the 50 states. i want for the report to open displaying only the records for the selected states in the listbox. how can i do this?


  2. #2
    Join Date
    May 2002
    Use the listbox data to filter the report:


    docmd.OpenReport "rptSales",acViewPreview,,"[State]='NY' or [State]='MA'"

    by construct the criteria string from the listbox selections.

  3. #3
    Join Date
    Jul 2003
    could you elaborate some more. what is the syntax that needs to be used with a listbox? i know how to get this to work with a text box or combo box. what if i have multiple selections?

  4. #4
    Join Date
    Jul 2003
    Here's something I made recently. You can use it to make a string to put as the filter. To call it, you can go GetSelectedItems(listbox, "State IN('", "',", "')"), which would give you STATE IN('NY', 'MA').

    Public Function GetSelectedItems(lst As ListBox, Optional strPrefix As String = "", Optional strDelimiter As String = "", Optional strPostfix As String = "") As String
    'returns a string containing all the items selected in the listbox

    Dim varCount As Variant
    Dim str As String

    str = strPrefix

    'get items in listbox
    For Each varCount In lst.ItemsSelected
    str = str & lst.ItemData(varCount) & strDelimiter

    'get rid of last delimiter
    If strDelimiter <> "" Then
    str = Left(str, Len(str) - Len(strDelimiter))
    End If

    'attach postfix
    str = str & strPostfix

    'return string
    GetSelectedItems = str

    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts