Results 1 to 5 of 5

Multi-select listbox with State values Plus "All"

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

    Multi-select listbox with State values Plus "All"

    Experts:

    I need some assistance tweaking a process utilizing a listbox.

    Please find attached sample database containing the following objects:
    1. T01_SourceData (only containing some sample data)
    2. Q01_Query
    3. Form

    Allow me to summarize the current process (which works fine):
    - Open "Form" -- the listbox displays all "State" values.
    - I then either select "California" (by itself) or I can select "California" and "Texas".
    - Then I click "Submit Query" **
    - Now, for each selected value in the listbox, a **separate** Excel file was created. This is working great!

    ** Note: In for the process to work for you, please ensure to create a (temporary) "Reports" folder and then update its folder path in the VBA: ReportPath = "C:\Users\dellc\Desktop\Reports


    What I need some help with:
    - As previously stated, the listbox dynamically shows distinct "State" values based on "T01_SourceData"
    - Also, for each "State" value it will create a separate Excel.
    - However, I'd like to tweak the process so that I can also have an **All** value which will include all 11 records when selected.


    - So, I'd like my listbox to also include "All" such as:

    All
    California
    Colorado
    DC/VA
    Delaware
    Florida
    Texas
    Virginia

    So, while I want to continue to get 7 separate Excel files (if chosen), I'd also like to get a single Excel (with 11 records) when selecting "All".

    My question: First, how can I add the "All" to the dynamic list? And then, I need to have to some IF function where -- when "All was selected -- no criteria is passed into the query.

    Thank you,
    EEH
    Attached Files Attached Files

  2. #2
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    322
    Never mind... I figured out a solution. No further assistance is needed on this post.

  3. #3
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    419
    Be aware that State and Date are listed as Problem names and reserved words in Access

    http://allenbrowne.com/AppIssueBadWord.html#T

  4. #4
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    322
    moke123 - roger... thanks. Actual database uses different fieldnames. Appreciate the link to Allen Browne's site though.

  5. #5
    A S MANN is offline Advanced System Analyst
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    India
    Posts
    149
    Kindly see the attached DB as your answer.
    Attached Files Attached Files

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

Similar Threads

  1. Replies: 10
    Last Post: 08-08-2019, 11:05 AM
  2. Replies: 1
    Last Post: 02-28-2017, 12:29 PM
  3. Replies: 24
    Last Post: 08-08-2015, 09:19 AM
  4. Replies: 1
    Last Post: 10-08-2012, 09:01 AM
  5. Replies: 16
    Last Post: 11-01-2011, 01:35 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