Results 1 to 8 of 8
  1. #1
    float is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    6

    Multi select box with several options

    Hello everyone,



    Could someone give me a start of a solution with the following:

    We have a list of all countries in the world and want to achieve that users first have to select one or more/all continents. After this selection they have the option to select one or more/all countries. After the selection has finished the 'program' needs to enter every country with the text on a separate row (no multiple values) in the table.

    The possibility to select the countries is in a subform. The country/ies selected via the above tool will always be linked to a text that was submitted in the form (not the subform).

    Thank you for everyone that can give me a start on how to solve this! :-)

  2. #2
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    I think I understand most of what you're asking, but I do have one question:

    What's the purpose of linking the Multi-select Box in the Sub Form to a Text Box in the main Form?

  3. #3
    float is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    6
    Sorry for the misunderstanding, I was talking about the database structure.

    Table 1: the text and other information
    Table 2: the text from table 1 + the country

    Is it possible that the tool looks like windows explorer but in stead of the folders at the end check boxes?

  4. #4
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Then I guess I don't really understand what you're asking. Is there any way you can give me more information?

    Primarily I'm looking for a basic Table layout and how the two Tables are linked. Also, a more detailed understanding of exactly what you're trying to accomplish would be helpful.

  5. #5
    float is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    6

    Red face

    Off course :-)

    Table 1: the text of the recommandation and all kinds of other information
    Table 2: the countries
    Table 3: the text + the country

    The text of table 1 is linked to table 3 and so is the country from table 2.

    What I'm trying to accomplish:

    In the subform there will be a 'tool' that looks like windows explorer where the user can select to which country/ies the text of the recommandation applies.
    Example:
    (1) The user can select one of more continents:
    Europe
    North-America
    South-America

    (2) The user can stop there when the recommandation applies to every country in that continent group. When this happens the tool must put the text and all the countries of the continent in table 3. If the user selects 2 continents the tool must put the text and all the countries of the continents in table 3, but every selection on a different row.
    Example:
    text 1 Belgium
    text 1 Netherlands
    text 1 Washington
    text 1 California

    (3) On the event that the user continues the tool will show every country in the continent group (a + like in windows explorer where the user can click on) and the user can select one, more or all countries in that group. Every country that is selected will be added in table 3 together with the text of the recommandation
    Example:
    text 1 Belgium
    text 1 Netherlands

    IS this any help?

  6. #6
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    OK, you're asking for a lot there

    I don't know if the Explorer-like layout (with the +sign and expanding lists, etc.) are feasible in Access. I would imagine it's possible, but doing something like that from scratch would require an ENORMOUS amount of programming and design.

    I also have some recommendations on changing your Table layout. I'd go from three Tables to four like so:

    OtherData (Table 1)
    Code:
    DataID|TextField|Other Fields. . .
    1|Some comment goes here!|. . .
    2|Another comment goes here!|. . .
    Continents (Table2)
    Code:
    ContinentID|ContinentName
    1|North America
    2|South America
    3|Europe
    Countries (Table 3)
    Code:
    ContinentID|CountryID|CountryName
    1|1|Washington D.C.
    1|2|California
    2|1|Brazil
    2|2|Equador
    3|1|France
    3|2|Germany
    Table 4 - You'll need to name this one
    Code:
    DataID|ContinentID|CountryID
    1|1|1
    1|1|2
    1|2|2
    2|3|1
    2|3|2
    In the above example, the first three Tables each contain a piece of the information. Since you're just aggregating the information together with your Form, the 4th Table is simply made up of links to the data in the the other three Tables.

    Once expanded, a Report based on the 4th Table's data would look like below, but would save an enormous amount of space:

    Expanded 4th Table
    Code:
    DataField|Continent|Country
    Some comment goes here!|North America|Washington D.C.
    Some comment goes here!|North America|California
    Some comment goes here!|South America|Brazil
    Another comment goes here!|Europe|France
     Another comment goes here!|Europe|Germany
    Last edited by Rawb; 09-29-2010 at 03:06 PM. Reason: Corrected formatting!

  7. #7
    float is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    6
    Thanks for you help.

    In stead of the explorer thing.
    Is it feasible that I put a checkbox that all the countries will appear in the form with a checkbox in front of them?
    How would I do such a thing?

  8. #8
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Sorry for the delay, but I work at a manufacturing plant and we had to do a year-end inventory last week so I didn't have much free time. . .

    I've been trying to think up an easy way to do something similar to what you want, but Access just wasn't designed to handle that wort of thing. You could probably figure something out with a collapsible sub Form (which would most likely be in a sub Form of it's own), but it would require a bit of work.

    Really, the best way to handle a multi-step process like this would be to use a more Wizard-style approach:

    1. Have the first Form allow the user to enter the text data then click a "Next" button.
    2. Have the Next button open a second Form where they select continents then click another "Next" button.
    3. Then have that Next button open a final Form which allows the user to select the countries.

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

Similar Threads

  1. Replies: 7
    Last Post: 11-24-2014, 02:11 PM
  2. 2 multi select list boxes on one search form
    By woodey2002 in forum Forms
    Replies: 2
    Last Post: 11-05-2010, 12:44 PM
  3. Multi-select List Boxes
    By Rawb in forum Programming
    Replies: 6
    Last Post: 09-21-2010, 09:02 AM
  4. Replies: 1
    Last Post: 03-01-2009, 09:53 AM
  5. Multi-select listbox and update flag in table
    By Suresh in forum Programming
    Replies: 0
    Last Post: 12-19-2007, 01:04 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