Results 1 to 5 of 5
  1. #1
    rjv888 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Posts
    6

    Query Criteria from Form Multi-Select Listbox

    Hello Access gurus,

    I need help writing a short macro that converts multiple selections from a listbox to query criteria. The form is SystemHistory, my query is System_Query, and my report is SystemsReport. The input for System Code is on the Systems Table. Use 314 for test purposes.

    Mechanical.zip

    I found several sources online about this issue, but cannot get them to work with my form SystemHistory. My cmdOK button was setup with the macro builder, and I do not know how to make the button do all the things I need.

    1) Close report SystemsReport (if the report was open before)


    2) Open report SystemsReport with refreshed criteria from form. (I figured just refreshing the report wasn't what I wanted because I want the report "front and center" after clicking OK. If there is a better way to accomplish this then please let me know)
    3) Convert selections in listbox CatList to query criteria for query System_Query. The query field is the last one added "Category"

    Sites I found that I could not get to work:
    http://access.mvps.org/access/forms/frm0007.htm
    http://support.microsoft.com/kb/135546


    I appreciate any help.

    Thanks,
    Raul

  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,825
    Maybe Allen Browne can help 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
    rjv888 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Posts
    6
    How can I add this via Macro builder? Or I guess I could add the closing and opening of the report via VBA?

  4. #4
    rjv888 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Posts
    6
    Nevermind, Allen Browne's tutorial worked. Thanks June7

  5. #5
    rjv888 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Posts
    6
    Quick question, Allen Browne's macro opens my report in Print Preview view. Any idea how to get it to open in Report View?

    EDIT: I found this line:
    'Omit the last argument for Access 2000 and earlier. See note 4.
    DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip

    I imagine i need to change acViewPreview. Googling now

    EDIT 2: Nevermind. Changed acViewPreview to acViewReport
    easy as pie.

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

Similar Threads

  1. Multi Select Listbox parameter for Query
    By KBAR12 in forum Programming
    Replies: 70
    Last Post: 03-26-2014, 11:22 AM
  2. Replies: 4
    Last Post: 02-21-2014, 06:35 PM
  3. Replies: 1
    Last Post: 02-12-2013, 10:41 AM
  4. Replies: 6
    Last Post: 11-02-2012, 12:48 PM
  5. Replies: 5
    Last Post: 08-02-2012, 09:44 AM

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