Alternative approach to multivalue field (1 Viewer)

dunebuggy

New member
Local time
Today, 13:25
Joined
Mar 7, 2017
Messages
6
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

AWF VIP
Local time
Today, 13:25
Joined
Jul 26, 2013
Messages
10,355
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

New member
Local time
Today, 13:25
Joined
Mar 7, 2017
Messages
6
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?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:25
Joined
Feb 28, 2001
Messages
27,001
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).
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:25
Joined
Jan 20, 2009
Messages
12,849
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

Immoderate Moderator
Staff member
Local time
Today, 08:25
Joined
Feb 28, 2001
Messages
27,001
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_

Longboard on the internet
Local time
Today, 06:25
Joined
Sep 12, 2017
Messages
2,111
Dunebuggy,
any other information you need to record by country? i.e. number of participants, date (or date range), coordinator, ect?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:25
Joined
Feb 19, 2002
Messages
42,981
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.
 

Users who are viewing this thread

Top Bottom