Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-26-2019, 01:04 AM   #1
dunebuggy
Newly Registered User
 
Join Date: Mar 2017
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
dunebuggy is on a distinguished road
Alternative approach to multivalue field

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.

dunebuggy is offline   Reply With Quote
Old 09-26-2019, 01:09 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,432
Thanks: 165
Thanked 1,736 Times in 1,706 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Alternative approach to multivalue field

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 09-26-2019, 01:28 AM   #3
dunebuggy
Newly Registered User
 
Join Date: Mar 2017
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
dunebuggy is on a distinguished road
Re: Alternative approach to multivalue field

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?

dunebuggy is offline   Reply With Quote
Old 09-26-2019, 01:37 AM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,884
Thanks: 114
Thanked 2,978 Times in 2,710 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Alternative approach to multivalue field

I would use a multiselect listbox.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 09-26-2019, 05:17 AM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,507
Thanks: 92
Thanked 1,669 Times in 1,550 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Alternative approach to multivalue field

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).
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 09-26-2019, 05:27 AM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,592
Thanks: 56
Thanked 1,232 Times in 1,213 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Alternative approach to multivalue field

Hi. Just in case helps, study this demo.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 09-26-2019, 02:35 PM   #7
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,637
Thanks: 95
Thanked 1,498 Times in 1,413 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Alternative approach to multivalue field

Quote:
Originally Posted by The_Doc_Man View Post
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.

Galaxiom is offline   Reply With Quote
Old 09-26-2019, 07:11 PM   #8
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,507
Thanks: 92
Thanked 1,669 Times in 1,550 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Alternative approach to multivalue field

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 09-26-2019, 08:04 PM   #9
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,032
Thanks: 20
Thanked 381 Times in 374 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Alternative approach to multivalue field

Dunebuggy,
any other information you need to record by country? i.e. number of participants, date (or date range), coordinator, ect?
Mark_ is offline   Reply With Quote
Old 09-26-2019, 09:16 PM   #10
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,238
Thanks: 15
Thanked 1,591 Times in 1,511 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Alternative approach to multivalue field

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with updating multivalue field Cyberg Modules & VBA 3 10-04-2018 12:58 PM
Comparing multivalue combo box to multivalue table/query field Kronix Forms 1 03-30-2018 05:43 AM
Using multivalue listbox to filter records via another multivalue field WJB General 4 01-25-2017 09:52 AM
Querying a multivalue field. The Archn00b Queries 3 12-23-2013 06:54 AM
Auto-populating a multivalue field by another multi-value field checks MyWebdots Forms 1 02-23-2011 02:04 AM




All times are GMT -8. The time now is 01:53 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World