MVF Technical Discussion and Uses (If you plan to Use Them) (3 Viewers)

[BlueSpruce]

No.
A standard multiselect listbox (simple or extended) has been available since at least A2000.
It does not depend on MVFs. It allows multiple selections on a form but does not have checkboxes.

I don't understand why MS didn't provide a multiselect list box control with checkboxes that maps to normalized tables. It would've actually been easier to implement that solution from the very beginning. So now we have users creating denormalized structures just to support desired user interfaces, and developers who simply won't use MVF controls because of all its shortcomings. MS should've done a better job in enforcing proper relational design.

 

[MajP]

I don't understand why MS didn't provide a multiselect list box control with checkboxes that maps to normalized tables.

1. The MVF control maps to a normalized table, as I have repeatedly shown. Maybe you mean a visible table or a user defined table.

2. If they did make this "bound multi select listbox " the properties would be so complex that most users would have to use a wizard to figure it out. And even then it would be extremely difficult to make wizard flexible enough to support all types of user defined tables. That is why they build the table so it can work as a junction table or a one to many table. Then it knows exactly what to do with the fields.

Assume the datatable is Companies

Companies
--CompanyID
--CompanyName

The reference table is regions

Regions
- StateID
-StateName

The junction table is Company_Regions
Comapany_Regions
--CompanyID_FK
--StateID_FK



Assume on the company form you drop this "bound Multi select listbox". You would have to correctly provide all of these properties in order to create the relationships and display. It would be pretty confusing to the average user. It would need to know information from 3 tables. This is probably why MS decided to make the junction table and to hide it. That limits all the possibilities.

You would have these properties and have to correctly provide the correct names.
MainTable - Company
MainTableLinkField - CompanyID
ReferenceTable - Regions
RefenceTableLinkField - RegionID
ReferenceTableDisplayField - StateName
JunctionTable - Company_Regions
JunctionTable_MainTableLinkField - CompanyID_FK
JunctionTable_ReferenceTableLinkField - ReferenceID_FK

The user would also have to correctly create the junction table

4. A potential easier solution as @Pat Hartman pointed out, would be if MS still created the "hidden tables", but at least made them visible. That could alleviate some of the concern and mystery. It would still have to be locked down.

5. From all this discussion I would be far less hesitant to use MVFs because of this I understand it way better and looked hard at the possible limitations. It has far less limitations than even Access experts state. Most of the criticisms of MVF are wrong. The real problem is that what is going on is OK, but the fact that you do not know or can see it is at least confusing.

4. What I do not understand is why the Listbox does not have a "check" property. I think people like the checkboxes as an interface more than the highlighted rows. That could be easily done for any listbox.

5. If they built a "bound multi select" then they would likely need a "Bound concatenated textbox". This suffers from the same problem of infinite possibilities of table design and display. So again it gets real difficult you make the properties simple enough so that a user can just tell it what to concatenate. Since MS has a hidden table with specific design and column names it can figure out what to display.

 

[BlueSpruce]

1. The MVF control maps to a normalized table, as I have repeatedly shown. Maybe you mean a visible table or a user defined table.

2. If they did make this "bound multi select listbox " the properties would be so complex that most users would have to use a wizard to figure it out. And even then it would be extremely difficult to make wizard flexible enough to support all types of user defined tables. That is why they build the table so it can work as a junction table or a one to many table. Then it knows exactly what to do with the fields.

Assume the datatable is Companies

Companies
--CompanyID
--CompanyName

The reference table is regions

Regions
- StateID
-StateName

The junction table is Company_Regions
Comapany_Regions
--CompanyID_FK
--StateID_FK



Assume on the company form you drop this "bound Multi select listbox". You would have to correctly provide all of these properties in order to create the relationships and display. It would be pretty confusing to the average user. It would need to know information from 3 tables. This is probably why MS decided to make the junction table and to hide it. That limits all the possibilities.

You would have these properties and have to correctly provide the correct names.
MainTable - Company
MainTableLinkField - CompanyID
ReferenceTable - Regions
RefenceTableLinkField - RegionID
ReferenceTableDisplayField - StateName
JunctionTable - Company_Regions
JunctionTable_MainTableLinkField - CompanyID_FK
JunctionTable_ReferenceTableLinkField - ReferenceID_FK

The user would also have to correctly create the junction table

4. A potential easier solution as @Pat Hartman pointed out, would be if MS still created the "hidden tables", but at least made them visible. That could alleviate some of the concern and mystery. It would still have to be locked down.

5. From all this discussion I would be far less hesitant to use MVFs because of this I understand it way better and looked hard at the possible limitations. It has far less limitations than even Access experts state. Most of the criticisms of MVF are wrong. The real problem is that what is going on is OK, but the fact that you do not know or can see it is at least confusing.

4. What I do not understand is why the Listbox does not have a "check" property. I think people like the checkboxes as an interface more than the highlighted rows. That could be easily done for any listbox.

5. If they built a "bound multi select" then they would likely need a "Bound concatenated textbox". This suffers from the same problem of infinite possibilities of table design and display. So again it gets real difficult you make the properties simple enough so that a user can just tell it what to concatenate. Since MS has a hidden table with specific design and column names it can figure out what to display.

Understood about the complexities. Was MS' reason for hiding the junction table so users could not work with it, yet developers can because they can figure out how to? What could go wrong with querying and updating a visible junction table?

 

[Pat Hartman]

Understood about the complexities. Was MS' reason for hiding the junction table so users could not work with it, yet developers can because they can figure out how to? What could go wrong with querying and updating a visible junction table?

MS doesn't want us to work with the table under any conditions. That is why they hid it. All of the "abomination" data types have hidden tables so all are technically normalized except that you have to use special, non-standard SQL to work with them.

 

[gemma-the-husky]

I don't understand why MS didn't provide a multiselect list box control with checkboxes that maps to normalized tables. It would've actually been easier to implement that solution from the very beginning. So now we have users creating denormalized structures just to support desired user interfaces, and developers who simply won't use MVF controls because of all its shortcomings. MS should've done a better job in enforcing proper relational design.

Because multi select anything is really not relational, more presentational. I had a system where selling prices were displayed in a grid with products as row entries, and packsizes as column entries. That's non relational, so I stored the data as relational, Rows with column entries for product, pack size, price, but then reprocessed the items to present them in a grid.

Then after changes were made, I had to turn the grid back into the normalised storage arrangement..

Just because the users/company wanted the data presented in a particular way - admittedly a useful feature.

 

[MajP]

As @Pat Hartman stated they do not want you messing with the table because in order to get the presentation of an MVF the hidden table needs to be very standard. The hidden table has specific field names and datatypes. If you alter this it could easily mess things up. I think they could of mad it visible and even editable, but likely the average user could screw it up. So they are hiding it to be safe. I think they may have erred on the cautious side. They were cautious so a user cannot mess it up, but this made it so obfuscated that most users would not use them. I think they would have been better off making it visible and they would have been more adopted.

The MVF was a solution to Sharepoint. Sharepoint does not have any kind of subform and mostly just a "datasheet" view. So they needed somehow to make an interface to display and select multiple choices. So they needed it to work with SP and then figured make it available for Access.

 

[BlueSpruce]

The MVF was a solution to Sharepoint. Sharepoint does not have any kind of subform and mostly just a "datasheet" view. So they needed somehow to make an interface to display and select multiple choices. So they needed it to work with SP and then figured make it available for Access.

I thought MS provided MVF's in Access for compatibility with db servers that support collections, like Informix and Oracle. Ironically, SQL Server doesn't support native MVF datatype, but complex UDF's can be created.

Why is it necessary to put non_MVF multiselect controls in an Access subform? Is it because you can only store one value per record in a detail table?

 

[Pat Hartman]

Why is it necessary to put non_MVF multiselect controls in an Access subform? Is it because you can only store one value per record in a detail table?

That isn't true. list boxes can go on main forms also but they are not bound when they are multi-select

 

[MajP]

Why is it necessary to put non_MVF multiselect controls in an Access subform? Is it because you can only store one value per record in a detail table?

I do not understand that question. On a main form you can have a an multiselect listbox and not in a subform. This demo shows that

Form Design to Allow Multiple Selections (Add your Ideas)

In response to this @DakotaRidge and I are very interested in examining all possibilities for implementing controls with multiselection capability and store those values in normal child tables...

www.access-programmers.co.uk

Here is a listbox on the mainform showing selections for that record.

You cannot do this in a continuous form because it is an unbound control formatted through code. If it was continuous each record would display the same results.

 

[BlueSpruce]

I do not understand that question. On a main form you can have a an multiselect listbox and not in a subform. This demo shows that

Form Design to Allow Multiple Selections (Add your Ideas)

In response to this @DakotaRidge and I are very interested in examining all possibilities for implementing controls with multiselection capability and store those values in normal child tables...

www.access-programmers.co.uk

Here is a listbox on the mainform showing selections for that record.

View attachment 121235

You cannot do this in a continuous form because it is an unbound control formatted through code. If it was continuous each record would display the same results.

I'm also confused, as I thought the demo solutions presented in this thread are using popup modal subforms to simulate multiselect listbox controls?

 

[MajP]

thought the demo solutions presented in this thread are using popup modal subforms to simulate multiselect listbox controls?

Not sure what to say. Either look at the demo or look at the very clear summary. One of four uses a subform.

This thread is designed to allow people to showcase techniques they use.
The example I am posting is the common checklist.
This uses 4 modifications to the same technique
1. using a subform
2. using a multi select listbox
3. Using an in memory ADO recordset
4. using a crosstab