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

[BlueSpruce]

i tested it, and it did not occur to me the foreign characters.
although the string is truncated in display.
there is a Note on the ConcatRelated function, have you read it?
also, we are using combobox and not textbox.
for combobox, i don't know the limit of text.

Hmmm, strange it didn't happen to you. I read the func notes and observed Allen Browne's repro of the bug. Did you select enough colors to concat more than 255 chars? I also don't kno combo display text limit. I thought displaying long text was irrelevant because it gets truncated?

 

[arnelgp]

i think, in real life, you will only select small number of items.
i made a demo, somewhat same as you post on the snapshot.

 

[Edgar_]

hello everyone

if you find errors on the sample db, just rectify them.

I won't rectify, but I will let you know that attempting to drop the menus down would simply not show anything or let me do anything until I press the escape key

As you are probably aware, it does still depend on a deep hidden attached table

I didn't see in the article the methodology to make those hidden tables available, you only mentioned "trickery" as the method. But I do see the names in the objects and relationships tables.

Anyway, I DO NOT USE MVFs, so this is a first for me. Seeing that one of the problems is querying the MVFs, have you guys tried filtering the form by the MVF? if you attempt to do it it says it can't be done.

But then, if you right click a textbox bound to the MVF, it shows a few filter options that let you filter. The syntax is different, but the form still supports filtering if you use syntax like this:

Private Sub txtUsageFilter_AfterUpdate()
    Me.Filter = "([Lookup_Usage].[UsageName] LIKE '*" & Me.txtUsageFilter.Text & "*')"
    Me.FilterOn = True
End Sub

In the form, the control is named Usage, the field in the table is named UsageName. So it seems to require something like Lookup_ControlName.FieldName, you may keep adding filters by using operators, just check what the form does and try to modify it.

Check the attached file to test this claim. It's a products database where the user selects the usage for each product via a MVF. If you want to know what products are for a particular usage, you can search for the term from the textbox at the top. It does not require to enter the whole term, it's a simple LIKE '*searchTerm*' search.

Note: If you needed to query by the ID, I saw that there's some stuff in the Hyperlink.TextToDisplay property of the combo box

You might want to try something with it.

 

[arnelgp]

I won't rectify, but I will let you know that attempting to drop the menus down would simply not show anything or let me do anything until I press the escape key

sorry I can't replicate such error. Many have downloaded it, but only you mention such error.

 

[Edgar_]

sorry I can't replicate such error. Many have downloaded it, but only you mention such error.

It's most likely the version of Access where I tested it. Not sure, but if a form has to show, it's probably out of my screen.

 

[isladogs]

Although I like the idea of using ConcatRelated to store the data in a normalized table, I think it is much easier to understand what is going on and indeed work with a 'standard' MVF structure

The attached demo uses the same country & colors tables but the FlagColors is a standard MVF field based on tblColors
I have included both a datasheet and a continuous form.
Some filtering is possible with the continuous form

I have also included various queries.
One of these is the very simple filter that I don't yet know how to do in @arnelgp's example

SELECT
    tblCountries.CountryID,
    tblCountries.CountryName,
    tblCountries.FlagColors
FROM
    tblCountries
WHERE
    (
        (
            (tblCountries.FlagColors.Value) = "Blue"
            AND (tblCountries.FlagColors.Value) = "Red"
            AND (tblCountries.FlagColors.Value) = "White"
        )
    );

Query qryComplexAttachedTables shows the name of the attached table for each complex table / field

SELECT
    MSysObjects.Name AS ComplexTable,
    MSysComplexColumns.ColumnName,
    MSysObjects_1.Name AS AttachedTable
FROM
    MSysObjects
    INNER JOIN (
        MSysObjects AS MSysObjects_1
        INNER JOIN MSysComplexColumns ON MSysObjects_1.Id = MSysComplexColumns.FlatTableID
    ) ON MSysObjects.Id = MSysComplexColumns.ConceptualTableID;

I've also exposed the deep hidden attached table so you can see the effects of editing the values in the MVF field

Another of my articles may be helpful:

Complex Data Types in Access

This article explains what the various complex datatypes are and how to work with these safely.

www.isladogs.co.uk

Hope that helps

 

[BlueSpruce]

We do not need to know why you do not use MVFs for various reasons. That dead horse has been beaten thoroughly.

I think it's important to know their reasons for not using MVF's, as well as why some participants in this thread prefer a particular demo app presented in this thread. I personally prefer to use the solution that's easiest to work with in order to accomplish my objectives in the shortest amount of time. I don't think it's easier to work with a standard MVF structure, as currently implemented in Access.

 

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

 

[BlueSpruce]

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.

What do you mean by "do not have much control over this"? ... I created an MVF with the wizard, specifying that the row source of the values are in a lookup table. By default, the autonumber ID values are what's bound to the table. I prefer storing ID's versus text descriptions because descriptions can change over time, but the ID never changes.

 

[BlueSpruce]

You do not seem to be able to help yourself from hijacking threads and discussing unrelated concepts.

Everything including likes, dislikes, pros, and cons is relevant to this discussion because it influences design and decision making. I am not a short_sighted person, I look at every aspect, including sentiment. Do you design an app without first consulting with stakeholders and users?

 

[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

 

[BlueSpruce]

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

View attachment 120864

That's correct, Colin, the wizard lets you choose which column to bind to the table, or you can specify it in the field properties. Thanks for clarifying this.

I personally prefer storing key values because text descriptions can change, and then you would have to change all code that has those hardcoded text dependencies. Storing keys is also more efficient for storage and processing. Many developers don't like storing keys because it's harder to understand the code logic, but that doesn't bother me because I can view the lookup table and after a short while I remember what each key value means.

 

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

 

[BlueSpruce]

@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.
View attachment 120879

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.
View attachment 120880

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.

View attachment 120881

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.

What happened to your previous post?

I will probably need to go back and clarify what I said, but still believe what I said is correct and what you show supports my point not counters it.

 

[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

 

[BlueSpruce]

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

Agreed, the wizard behaves the same when creating non-MVF cbo's. I personally don't provide MVF's to users and always store the key for reasons I previously explained. I actually display 3 columns in the dropdown lists, the key, text description, and an X in the 3rd column if the value has been discontinued.

I would like to provide multiselect list boxes to users while storing each value in normal child tables, one value per record. I am tempted to try Arnel's tool because it concats multiple values and stores them in regular text fields vs MVF's.

 

[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

 

[BlueSpruce]

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

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.

 

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

 

[BlueSpruce]

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.
View attachment 120889
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.

I will revisit your idea. So you're using a popup modal form that mimics a multiselect list box? Why can't a real listbox control be used to select multiple values that binds each value to a regular table?