Multi-Value Fields. - A Curse? or A Bless?

[prabha_friend]

Please share your opinion.

 

[ebs17]

A Bless ... for those who don't have a clue and don't have big claims.

Otherwise, you should keep in mind that additional invisible tables and invisible relationships are used here. It will regularly make more sense and be easier for a developer to work with known and self-created tables and relationships.

 

[isladogs]

Multivalued Fields

Multivalued fields (MVFs) were introduced with Access 2007. This article explains what MVFs are and how they can be used. However, most experienced developers avoid them completely. This article explains why this feature is really not a good idea

www.isladogs.co.uk

 

[GPGeorge]

They are neither, IMO. Like many other features in Access, they are a tool which savvy users can safely use. They should be avoided except for the niche applications where they pose less risk and impose fewer limitations, though.

 

[Pat Hartman]

They look good in principle and the control is cool but in reality, there are few good examples of the SQL needed to work with them. Plus they are very limited in their use. The biggest example of their weakness is that you get only a single field. How can this be such a problem? Say you have a simple list of options (that is all you can use them for anyway) - Apples, Bananas, Pears, Peaches. But you've decided that Pears is no longer an option going forward. You don't want to change history so you can't update existing records to remove Pears but you want to prevent it from being chosen going forward. Good luck with that.

 

[isladogs]

They look good in principle and the control is cool but in reality, there are few good examples of the SQL needed to work with them. Plus they are very limited in their use. The biggest example of their weakness is that you get only a single field. How can this be such a problem? Say you have a simple list of options (that is all you can use them for anyway) - Apples, Bananas, Pears, Peaches. But you've decided that Pears is no longer an option going forward. You don't want to change history so you can't update existing records to remove Pears but you want to prevent it from being chosen going forward. Good luck with that.

There are so many things that aren't good about MVFs and my article linked in my earlier post mentions many of those

However, your example above is completely wrong as you will see if you try it for yourself.
Changing the list of options does not and indeed cannot alter existing data retrospectively

In the example below, I added apples, oranges, pears & plums to a MVF field value list and after adding 4 records I deleted plums from the list

 

[Pat Hartman]

I did not say that removing the option group modifies existing data. I said that there is no way to keep it in the list for historical reference but prevent it from being applied to new records. Yes, you can remove it from the list but that just causes confusion. Using a form, what does the control show for row 3? It still shows plums, which it should, but you can't tell that plums are no longer a valid option. If you are not the programmer who removed plums, your head will be spinning.

 

[isladogs]

That's slightly different from what your previous post seemed to be saying.

Once the row source for the dropdown is edited it will of course no longer show the deleted item in records already created.

However your point has absolutely nothing to do with MVFs as such.
Exactly the same thing applies to a standard (not MVF) field selection based on a value list or lookup table

 

[Uncle Gizmo]

I found this video by Takeshi K very useful:-

Access2010 MultiValued ComboBox​

But as others have said, they are best avoided...

Multi Value Fields Links​

There's some useful links at the bottom of this page:-

Multi Value Fields Links - Nifty Access

Multi Value Fields Links Consider a scene at a Microsoft office where an innovative individual, perhaps from the marketing team, suggests, "MS Access requires 'Multivalued Fields' 💡 - our aim is to simplify the usage of MS Access for individuals!" Envision the developers emphatically voicing...

www.niftyaccess.com

 

[isladogs]

Tony
For info - the outdated link on that page to my old website is now as given in post #3: https://www.isladogs.co.uk/multivalued-fields/

 

[Uncle Gizmo]

the outdated link on that page to my old website is now as given in post #3:

Updated!

 

[Pat Hartman]

Once the row source for the dropdown is edited it will of course no longer show the deleted item in records already created.

If the underlying data included plums, the MVF control shows the original four items. If the underlying data did not include plums or this is a new record, the MVF does NOT show plums. The controls shows the current three items. The control correctly shows the old values. Maybe you wouldn't be confused by this, but most would be.

However your point has absolutely nothing to do with MVFs as such.

It does because if you do this properly with a subform, YOU have control over the underlying table and YOU can include an ActiveFlg which allows old data to stay but the programmer can prevent the use of old items in new rows by checking the ActiveFlg.