Multi select box with several options

[float]

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! :-)

[Rawb]

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?

[float]

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?

[Rawb]

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.

[float]

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?

[Rawb]

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)

DataID|TextField|Other Fields. . .
1|Some comment goes here!|. . .
2|Another comment goes here!|. . .

Continents (Table2)

ContinentID|ContinentName
1|North America
2|South America
3|Europe

Countries (Table 3)

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

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

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

[float]

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?

[Rawb]

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.