Results 1 to 4 of 4

Pass *multiple* listbox values into query

  1. #1
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    317

    Pass *multiple* listbox values into query

    Experts:

    I'd like to get some assistance with tweaking a process (in VBA). Attached sample database contains a table, query, and form (I kept the report but that's not the focus in this thread).

    Current process:
    - Open the form "Form"
    - Select one value (e.g., "Florida") from the listbox and click "Submit Query".
    - Open the query... value "Florida" has been added to the criteria... so far so good!


    - However, if I were to select multiple values (e.g., "Florida" and "Texas") in the listbox and then click "Submit Query", only the last value (i.e., "Texas") of the value range (listbox) is currently being passed into the query.

    Modified process:
    - The modified process should allow me to pass all select values (whether consecutive or non-consecutive value selection) into the query criteria.
    - So, if I were to select "Colorado", "Florida" and "Virginia", I'd expect to see all of these three (3) values in my query criteria.

    How do I modify the existing VBA to accommodate this process?

    Thanks,
    EEH
    Attached Files Attached Files

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,739
    You have to build a string inside the loop and then use it after. Along the lines of:

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

  3. #3
    accesstos's Avatar
    accesstos is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    275
    Tom,

    Bellow you see the practical application of Paul's demonstration:
    Code:
        If Me!lstStates.ItemsSelected.Count > 0 Then
            For Each varItem In Me!lstStates.ItemsSelected
                strCriteria = strCriteria & "'" & Me!lstStates.ItemData(varItem) & "',"
                ReportFileName = ReportFileName & "_" & Replace(Me!lstStates.ItemData(varItem), "/", "_")
            Next varItem
                'trim trailing comma from strCriteria.
                strCriteria = Left(strCriteria, Len(strCriteria) - 1)
                'Build the final ReportFileName.
                ReportFileName = Mid(ReportFileName, 2) & ".pdf"
                
                'Build the new SQL statement incorporating the string
                strSQL = "SELECT * FROM T01_SourceData " & "WHERE T01_SourceData.State IN(" & strCriteria & ");"
                Debug.Print strSQL
                'Apply the new SQL statement to the query
                qdf.SQL = strSQL
    
                'ReportFileName = Replace(Me!lstStates.ItemData(varItem), "/", "_") & ".pdf"
                OutputPathFileName = ReportPath & ReportFileName
                Debug.Print OutputPathFileName
                'DoCmd.OutputTo acOutputReport, "Report", acFormatPDF, OutputPathFileName, False
    
            'Next varItem
    
        Else
            MsgBox "Please selete one or more States"
        End If
    You can delete the red lines after the testing.
    I presume that you want all the names of states into report's filename.

    Also, I suggest to avoid blank spaces in path strings and filenames.

  4. #4
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    317
    Paul - I just returned on checking for a proposed solution. Thank you for providing this example... very nice!!

    John - your solution is superb... it fits nicely into my current process and I like that the selected listbox values are appended to the filename. Awesome!!!

    Again, thank you both, Paul & John, for providing some fantastic assistance.

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

Similar Threads

  1. Pass listbox value into query criteria
    By skydivetom in forum Programming
    Replies: 3
    Last Post: 10-16-2019, 02:23 PM
  2. Function to Pass Multiple Values as Array
    By Ellpee in forum Programming
    Replies: 1
    Last Post: 03-31-2019, 10:56 AM
  3. Replies: 4
    Last Post: 03-27-2018, 12:30 PM
  4. Replies: 12
    Last Post: 05-05-2014, 09:23 PM
  5. Replies: 4
    Last Post: 02-14-2013, 09:33 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