Results 1 to 11 of 11
  1. #1
    reesim06 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2016
    Posts
    5

    How do I adapt my query?

    I've a set of tables/form/macro/queries setup in access, although it fundamentally works, I'd like a better solution to how the form pulls data from the tables.




    Basically I have tables which consist of a whole bunch of entries and I want to generate a new table which pulls all the data from the original table (but doesn't pull those rows I don't need). At the moment this works by the first column having a unique entry, and the form/query pulls the information from 20 drop-down boxes on the form which generates the new table from those drop-downs. I'd like to get rid of the drop-downs, and either have tick-boxes or a CTRL+Select from a long list.


    The form is using the following Query Criteria: [Forms]![QBF_Form]![Vessel] Or [Forms]![QBF_Form]![Vessel2] Or [Forms]![QBF_Form]![Vessel3] Or [Forms]![QBF_Form]![Vessel4] Or [Forms]![QBF_Form]![Vessel5] Or [Forms]![QBF_Form]![Vessel6] (etc)


    Any thoughts?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If you're opening a form or report, I'd use a multiselect listbox:

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

  3. #3
    reesim06 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2016
    Posts
    5
    Thanks for the reply.

    I briefly looked at the list box, but my current query criteria doesn't seem to translate to the list box correctly... Should it work or is there a different criteria I should look into to pull the info in a multi-select fashion?

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    2 comments:

    1) Constantly creating tables is a good way to corrupt your dB.
    Create the table once and delete all of the records before appending new records.

    2) If you have a table with fields named like: "[Vessel], [Vessel2], [Vessel3], [Vessel4], [Vessel5], [Vessel6], ..., [Vessel20]" you have a design flaw.
    You should read up on Normalization.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, a query can't use a multi-select listbox directly. The method I posted would take the criteria out of the query, and then use code to open a form or report.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe this will help:
    MultiSelectListboxes.mdb (A2000 version)
    http://www.rogersaccesslibrary.com/forum/topic315.html

  7. #7
    reesim06 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2016
    Posts
    5
    Quote Originally Posted by ssanfu View Post
    2 comments:

    1) Constantly creating tables is a good way to corrupt your dB.
    Create the table once and delete all of the records before appending new records.

    2) If you have a table with fields named like: "[Vessel], [Vessel2], [Vessel3], [Vessel4], [Vessel5], [Vessel6], ..., [Vessel20]" you have a design flaw.
    You should read up on Normalization.
    The form pulls relevant rows from a table which rarely changes. There are 4 such tables for different environments (all seperate with no reliance or relationships to each other). Once the form is run and the relevant row are "generated" by the query, I copy them into excel so I can tweak colouring, printing format and delete columns which are no applicable (a row may only have entries in a small number of columns, therefore some will be empty and need removing). I do these bits in excel as I havn't managed to get access to do them yet.

    The [vessel2] etc is related to the drop-off box name. Using the drop-down box, you select a record which is added to the output table.

    Once tables are generated, the user copies them into excel and are then no longer required so are not saved.

    Edit: I see I need to read up more on multi-select boxes!
    Last edited by reesim06; 05-19-2016 at 03:40 PM. Reason: Some replies while I was writing

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'll get out of the way.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Paul
    Apologies....Didn't mean to step on toes. Don't know how I missed the link to your MultiSelect example... guess I need to read slower and pay more attention.

  10. #10
    reesim06 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2016
    Posts
    5
    Quote Originally Posted by pbaldy View Post
    I'll get out of the way.
    It's appreciated, many thanks 😊

  11. #11
    reesim06 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2016
    Posts
    5
    Excellent links, many thanks!

    I've managed to set it up as a multi select box, outputting to a table nicely. Sorting issues as I see them come up...

    Now to try and get all 4 tables to populate with a single click!

    Thanks again!

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

Similar Threads

  1. Replies: 16
    Last Post: 11-01-2011, 01:35 PM
  2. adapt the given fragment code
    By jumpingtree in forum Queries
    Replies: 2
    Last Post: 09-06-2010, 08:54 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
  •  
Other Forums: Microsoft Office Forums