Results 1 to 5 of 5

Run select query from VBA code where criteria comes from a multi select ListBox

  1. #1
    MartinS is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Location
    Queensland Australia
    Posts
    2

    Run select query from VBA code where criteria comes from a multi select ListBox

    Hey Guys,

    I'm pretty new to VBA and have hit a snag where I don't know how to go forward. Your assistance/coaching will be greatly appreciated. I have a modal form with member data populated on the form load procedure on it. Then on a listBox that has "Multi Select" enabled, the user will make a selection of all the "codes" he wants to report on.

    In a button "on-click" procedure I have the following code that runs through the listBox selections:


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

    'make sure a selection has been made
    If Me.lstBulkExercise.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 Exercise"
    Exit Sub
    End If

    'add selected values to string
    Set ctl = Me.lstBulkExercise
    For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
    Next varItem


    'trim trailing comma
    strWhere = Left(strWhere, Len(strWhere) - 1)

    What I need assistance with:
    I need to run a select query in the code WHERE the member number as the primary key AND "strWhere" values are run agains the table, and then have that displayed on a report. The problem is I don't know how to accomplish this and really want to find out how to get this done.

    Your assistance is greatly appreciated.

    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,714
    Leave the query without criteria and apply the criteria as you open the report:

    http://www.baldyweb.com/multiselect.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,477
    Have you created the report?
    The report record source should be a SQL string or a saved query WITHOUT a Where clause. All records would be displayed.

    In the button click event you create a filter string with the selected items.
    The syntax is
    Code:
    SELECT column_name(s)
    FROM table_name
    WHERE column_name IN (value1, value2, ...); 
    What you need to create is the code in BLUE

    This is what your code might look like
    Code:
    Public Sub ButtonClick()
        Dim strWhere As String
        Dim ctl As Control
        Dim varItem As Variant
    
        'make sure a selection has been made
        If Me.lstBulkExercise.ItemsSelected.Count = 0 Then
            MsgBox "Must select at least 1 Exercise"
            Exit Sub
        End If
    
        'add selected values to string
        Set ctl = Me.lstBulkExercise
        For Each varItem In ctl.ItemsSelected
            strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
        Next varItem
    
        'trim trailing comma
        strWhere = Left(strWhere, Len(strWhere) - 1)
        strWhere = "[BulkExercise] IN (" & strWhere & ")"
        'comment out next line after debugging complete
        Debug.Print strWhere
    
        DoCmd.OpenReport "ReportName", acViewPreview, , strWhere
    
    End Sub
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    MartinS is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Location
    Queensland Australia
    Posts
    2
    Thanks Guys for your pointers. I managed to get it running first time and all is working very well. Thanks so much for your time and effort into my request. It is really appreciated. You guys rock.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,714
    Glad you got it sorted and welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 4
    Last Post: 09-23-2014, 08:39 AM
  2. Multi Select Listbox parameter for Query
    By KBAR12 in forum Programming
    Replies: 70
    Last Post: 03-26-2014, 11:22 AM
  3. Replies: 6
    Last Post: 11-02-2012, 12:48 PM
  4. Replies: 5
    Last Post: 08-02-2012, 09:44 AM
  5. Need Multi-Select listbox code example
    By Buakaw in forum Forms
    Replies: 4
    Last Post: 08-21-2011, 08:37 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
  •  
Tech Forums: Microsoft Office Forums