Alternative approach to multivalue field

[dunebuggy]

From what I've read it seems best to avoid using multivalue fields for a number of reasons. I haven't been able to find out the ideal way to approach it, however.

I want to design a form to capture the various countries in which a study was performed. Without using the mvf option in Access, how would I design a form to efficiently capture:

Study 1: Countries UK, US, ES, etc
Study 2: Countries IT, RU etc

I guess it's a many-to-many relationship between studies and countries, with a join table?

Thank you.

 

[Minty]

tblStudies is the primary table,
tblStudyCountries would be a child table with

StudyCountryID - PK - Autonumber
StudyID - FK - relating back to the study
CountryID - FK - Assuming you have a table of countries.

 

[dunebuggy]

Thank you for replying Minty. I understand the relationships better now.

With reference to form design, what kind of a control would I put on a form, making it as easy as possible to choose several countries?

 

[isladogs]

I would use a multiselect listbox.

 

[The_Doc_Man]

Since you seem to be relatively new to using Access, Colin's suggestion of using a "multiselect" listbox is correct but here is how you do it. First, build a listbox. Then in the control properties for the list box, you have the option of making it single-select or multi-select. When you do that, you need to read up on how to step through the various possible values of the listbox to see which one is selected.

I'm on a limited system right now so cannot do a quick search, but the topic to look for is the listbox's .Selected property, which is indexed by a row-number derived from the row on which that value was shown as a selectable option. And note when enumerating the rows of a listbox in a loop that Access numbers its rows started from 0, not 1. So the loop might be from 0 to (number-of-rows - 1).

 

[theDBguy]

Hi. Just in case helps, study this demo.

 

[Galaxiom]

I'm on a limited system right now so cannot do a quick search, but the topic to look for is the listbox's .Selected property,

The ItemsSelected collection is generally the best way to determine the selected items with a multiselect listbox. The Selected property applies to single rows and would require every item to be addressed and tested for True or False with a loop.

 

[The_Doc_Man]

Whoops, forgot about that collection. It's been a while and I was sending from a one-lung machine when I wrote that. But you are right - the .ItemsSelected collection is easier to enumerate than the whole list.

 

[Mark_]

Dunebuggy,
any other information you need to record by country? i.e. number of participants, date (or date range), coordinator, ect?

 

[Pat Hartman]

The "no code" solution is to use a subform. You can make it small and frameless if you prefer that look. The subform would use a combo to select the country.