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

[MajP]

Saving a Related Key or the Actual Value in MVF.

My previous discussion on using a look up table was not complete.

This obfuscation is one of the things that drives people absolutly crazy about MVFs

1. From a lookup table you will save either the PK of the lookup as the value in the hidden table or you will save the actual value. You may find when using the wizard that it creates a relationship using the key when you think you are saving an actual value. As far as I can tell this is automatic and depends on how you do the wizard and if the lookup table has a PK. And depends on how many columns you show in the pull down. Lets assume the lookup table has a PK In this case tblRegions with a StateID pk.

Most of the time a users wants to save a key, but there are times the users want to save just the value (or at least thinks that is what they are doing.)
If using the wizard and choose just the State value (not the ID), the user may assume they are storing the actual state value in the MVF. They only choose one field and did not include the StateID

However, the wizard automatically pulls in the StateID into the rowsource and binds the StateID to the hidden table. The user may think they are storing "Arizona", but are storing the ID 4.
The next screen in the wizard is

Unless the user chooses to show both columns in the pull down the user does not get an option to choose which field to bind.


But you can trick the wizard, if you simply want to store a value. You can do this by first making a query of tblRegion and return only the State name. Then If I use the lookup wizard on my query I can select state and it will store the actual state name and not a key in the value.

Now your better option is to use the properties where you can more clearly set the rowsource, column count, column widths, and bound column. But novice users may not be good with those properties.

2. You can see this here were I have 2 regions field. One is an MVF with a key stored in the hidden table and one with the actual State name stored.

Notice one field says number the other says short text.

3. What is so utterly confusing and will get both seasoned and novice users is that this is completely obfuscated. They appear identical except one value property show text but is actually storing a number, the other is showing text and actually storing text.

It is worse when including the value field

Both value fields show a name, but the first does not hold a name but a value.
To prove this here are the two queries that will return the same records.

 

[isladogs]

1. From a lookup table you will save either the PK of the lookup as the value in the hidden table or you will save the actual value. You do not have much control over this. As far as I can tell this is automatic and depends on how you do the wizard and if the lookup table has a PK.

Although I totally agree with how confusing it can be, I don't completely agree with your comments above.

If you are using the wizard with a lookup table such as tblColors where ColorID is the PK field, the next screen allows you to choose the field(s).

In this case, I'll select both
Next I'm asked about a sort order. I can skip this or I can choose e.g. Color

Next I can choose whether or not to hide the key column - normally I'd tick the checkbox (default value)

If I do show both columns, then I'm asked which field should be stored.

No matter whether I choose ColorID (number) or Color (short text), then I see the PK values in the table as the field only shows the first field selected. In this case I've chosen ColorID
Either way, I'll see both fields in the multiselect combo box

However, If having chosen both fields, I set the first column width to 0, then the Color field is displayed in the table

Of course, this would be easier to do by only selecting the Color field - see first screenshot above ....
BUT the wizard output will still show the MVF field as Number type even though it shows the text Color field
So yes, that is confusing

Of course, there is no need to use the wizard. The MVF details can be added to suit your requirements from the property sheet
Then you have total control and can have the MVF field shown as Short Text

Yes its complicated! Hope all of that makes sense to anyone reading this

 

[MajP]

@isladogs
IMO, I think your example is more an exception vice the norm. The wizard only allows you to choose which field to bind if you display more than 1 field in the pulldown, but does not if you show only 1 column.

So i am going to guess that the vast majority of the time the user does not gets to pick which field to bind when using the wizard.

The below window only appears if you choose to show more than one field to display. Which again I will assume most people to not do. At least I do not see a lot of multi column comboboxes.

IMO this is the more likely scenario.
Assume the user wants to store the state value ("Alaska", "Arizona") and not the key.
They probably assume if they pick just the state it is going to save data from that field and not create a relationship based on the key. That is what I would assume.

But that is not what happens it pulls in the StateID field into rowsource even though it was never selected and creates the hidden table to store the key and not to store the value.

Maybe at this point the novice users is going to figure it out that it is storing a key, but probably not. At this point no choice is provided to say what field do you want to bind. The choice window only appears if you select 2 fields to show. Why they provided different functionality when you show two fields vice hide a field makes no sense. If you show both choices in the pulldown you get to pick which one to bind. If you hide the key field you do not get a choice and will store the key. Even though the two pull downs have the same row source.

 

[isladogs]

I would normally only show one field if I was going to use a multiselect combo box.
Nevertheless, the option is there to have multiple fields even though it would be confusing for many users.

However, your comments about using the wizard to create an MVF field structure based on a lookup table also apply if you use a 'standard' table level lookup field (NOT multiselect). The displayed value may be text but the stored value is the PK field (normally number / autonumber)

In other word, this isn't an issue specific to MVFs

 

[isladogs]

Multiselect listboxes are an excellent Access feature which have nothing to do with MVFs

@arnelgp's unusual approach to storing MVFs in a standard normalised table is actually far more complex than a standard MVF.
It requires an additional table for storing data plus a hidden attached table for displaying the MVF fields
Lots of code required to manage the process. Querying appears to be far more complicated

 

[MajP]

All I care about is being able to transparently CRUD values in standard child tables. I am not concerned with the inner workings of how he stores multiple values in regular tables

Why not expand on the idea I did. It does all that. Shows the concatenated values, relatively easy to reuse and reconfigure, stores the data in a standard table. No hidden things. Just needs some work to position where you want it so it is sized and appears below the combo.

With some more thought you can make it more reusable. On a single form view you can use a subform instead of a pop up form making it look better. The in memory recordset is nice since it requires no additional table.

 

[RonPaii]

There is no reason to store the data as a delimited list. Store it normalized and display it as delimited text. I started with MajP's sample db and replace the list with a treeview control. The sample form has 2 "MVF", controls displaying the selections as a list and the other fill's the combo box value with delimited list. The user selections tables differ only in name. I am still working on positioning and sizing.

 

[MajP]

Why can't a real listbox control be used to select multiple values that binds each value to a regular table?

Obviously you can do that. That is what people have been doing for years. But that does not give the feel of the MVF combo. The idea was to give you some of the function of an MVF control. Expandable working on the combobox down arrow, checkboxes, ability to close the list. In my case in addition the ability to select all, deselect all, and order the list.

That's right!.. That's just what I said to Isladogs and MajP. Store each value, one per record, in a regular child table so I can slice and dice the data with regular queries

And that is what my original demo showed.

 

[RonPaii]

Yes, I am using 64-bit Access it works in 32bit without change. The treeview control is filled the same as the original forms in-memory recordset. The data is written back to the user selections table when the ok button is clicked by going though the tree looking for newly checked and un-checked leafs. The available selections table has 3 columns, [SelectionID],[ValueText],[ParentID], processed recursively starting with [ParentID] = 0. Selection tables without structure like the color table pass a query column 0 As [ParentID].

 

[RonPaii]

Microsoft has released a x64 version of common controlls that also works in x32 without change.

The only part of the tree saved to user selections are the leafs i.e. no child nodes "M1", the rest are for grouping.

 

[RonPaii]

The 4 tables used for selections look like the following.

The queries for the record sets passed to the tree are as follows

        ' Available Selections Recordset
        Set AvailSelections _
                = CurrentDb.OpenRecordset( _
                    "SELECT tblSelectionsTree.SelectionID, " & _
                           "tblSelectionsTree.SelectionText, " & _
                           "tblSelectionsTree.ParentID " & _
                    "FROM tblSelectionsTree;", _
                    RecordsetTypeEnum.dbOpenSnapshot)
        ' User's current selections Recordset
        ' Edited by MVF form
        Set UserSelections _
                = CurrentDb.OpenRecordset( _
                    "SELECT tblUserSelections.UserID_FK, " & _
                           "tblUserSelections.SelectionID_FK " & _
                    "FROM tblUserSelections " & _
                    "WHERE tblUserSelections.UserID_FK=" & Me.UserID & ";", _
                    RecordsetTypeEnum.dbOpenDynaset, _
                    RecordsetOptionEnum.dbFailOnError)

'--------------- Color -----------'
        ' Available Selections Recordset
        Set AvailSelections _
                = CurrentDb.OpenRecordset( _
                    "SELECT tblColorSelections.SelectionID, " & _
                           "tblColorSelections.SelectionText, " & _
                           "0 as ParentID " & _
                    "FROM tblColorSelections " & _
                    "ORDER BY tblColorSelections.SelectionText;", _
                    RecordsetTypeEnum.dbOpenSnapshot)
        ' User's current selections Recordset
        ' Edited by MVF form
        Set UserSelections _
                = CurrentDb.OpenRecordset( _
                    "SELECT tblUserColorSelections.UserID_FK, " & _
                           "tblUserColorSelections.SelectionID_FK " & _
                    "FROM tblUserColorSelections " & _
                    "WHERE tblUserColorSelections.UserID_FK=" & Me.UserID & ";", _
                    RecordsetTypeEnum.dbOpenDynaset, _
                    RecordsetOptionEnum.dbFailOnError)

 

[isladogs]

Really? I thought multiselect list boxes used MVF's to store multiple selections.

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.

However, you can also choose a listbox control when creating an MVF field. That also allows multiple sections and does show checkboxes
The two approaches may look 'similar' but work in different ways.

 

[MajP]

I've always noticed in field properties that you can choose multiselect yes/no for combo and list controls. So without the checkboxes, how does one multiselect, hold down the shift key and click on multiple values?

Already provided you the demo along with a video presentation on how to do this.

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

Can controls other than lists be used with mvf's and the checkboxes will appear?

There is a listbox, textbox, and combobox control designed for MVF. The checkboxes appear in the listbox and combobox. These are not the same controls as a multiselect listbox or standard combo.

 

[isladogs]

I've always noticed in field properties that you can choose multiselect yes/no for combo and list controls. So without the checkboxes, how does one multiselect, hold down the shift key and click on multiple values?

Depends on whether its a simple or extended multiselect listbox.

ListBox.MultiSelect property (Access)

Access VBA reference

learn.microsoft.com

 

[MajP]

and those controls are for exclusive use with mvf's, and cannot be adapted to work directly with normal tables, thus reason for mimicking them with popup modal form, right?

Have you looked at the Demo? As you asked the demo shows multiple ways to select multiple values and store in a normal junction table. The subform and pop up have checkboxes. The multi select does not. The crosstab uses just a click.
The specific MVF controls only work with MVF fields and cannot be used on a normal junction table. You can fake it like @arnelgp did where you are using an MVF field but saving somewhere in addition. A hybrid. @Josef P. also adapts this idea here where you leverage the MVF field only to support the MVF control.

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

 

[isladogs]

enum 2, extended, uses shift key, but none of those options display checkboxes.

Yes. As already explained by both myself and @MajP

 

[MajP]

Converting MVF to a Standard Junction / Child table.

One concern about MVFs can be alleviated because if you ever want to convert it to a traditional junction table or child table, it is extremely simple to do. For example if you decide that you want to convert to SQL Server or another RDMS or if you find a potential limitation.

In the example Companies have an MVF field to hold Regions. This lookup is storing the foreign key to the tblRegions

Although you see a list of states that is only display. What is being stored is the foreign key to the Regions table in the hidden junction table. It is normalized.

But if you wanted to create the junction table and migrate to a standard design you could make a junction table like this

Simply run an append query like this

And your results are

So my opinion has changed in that you really cannot paint yourself into a corner that you cannot get out of if you had to. Obviously you would then have to modify your forms. You could use some of the Fake MVF ideas that would use the junction table but require little modification to the forms since you would not have to add a subform for the child records.

 

[MajP]

Union an MVF
The question was asked here if you could union an MVF.

Error: The multi-valued field cannot be used in a UNION query

Hi everyone, I am working on a database with my team and we have three tables with three types of data. They're all pretty much made in the same way with different "themes" and we fill them out thanks to three different forms. I want to extract the 3 first columns of these three different...

www.access-programmers.co.uk

If by now you understand that an MVF if not a field but a display of a hidden table, then unioning an MVF is no different than what you would do if you could see the junction tables.

I added a second table called tblExternalCompanies to union with tblCompanies. Just like tblCompanies, It has a company name field and an MVF Regions using the State lookup table.

1. First create two like queries for each table linking to the lookup table by StateID . To pull in the State Names.
Remember that even though Regions shows a concatenation of text it is storing a StateID in the hidden table.

2. Make sure to Alias your names if not you we get some painful field names pulling in the table names

3. Now union these two queries

4. If you did this with MVF or a traditional design you could build a function to concatenate the records

Public Function ConcatStates(CompanyName As String) As String
  Dim rs As DAO.Recordset
  Dim strOut As String
  Set rs = CurrentDb.OpenRecordset("select State from qryUnionCompanyRegions where companyName = '" & CompanyName & "'")
  Do While Not rs.EOF
    If strOut = "" Then
      strOut = rs!State
    Else
     strOut = strOut & ", " & rs!State
    End If
    rs.MoveNext
  Loop
  ConcatStates = strOut
End Function

5. Then you could use that in a query

SELECT qryUnionCompanyRegions.CompanyName, concatStates([companyname]) AS Regions
FROM qryUnionCompanyRegions
GROUP BY qryUnionCompanyRegions.CompanyName, concatStates([companyname]);

7. Results

Bottom line. If you understand that an MVF field is not a field but a display of concatenated fields in a hidden table then this method is exactly how you would do this with a standard design. Since an MVF is not a field but a display, the fact that you cannot Union them is not really a drawback of an MVF, it is just not an added feature.

 

[MajP]

Update Query
One common statement about MVF is that you cannot easily update them. However here again the update is exactly how you would do it in a traditional design. Like the other examples the hard part is understanding the hidden table.

1. The MVF Region field is a numeric storing the Key to the State table in the hidden table. But the MVF display shows concatenated states. But again that is just a display.

If the companies that use to work in CA all work in Texas you want to update California to Texas.

2. The data really looks like this in the hidden table where it is a junction table

3. The keys for CA are 6 and 51 for TX in the Regions table
4.

5 Results

 

[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.