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

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

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.

No. Can you not read? I clearly spelled out the intent of this thread. The intent of this thread was to explain some ways to work with MVF data, not to beat some dead horse on why you should or should not use MVFs. The hope was to have a clear discussion of how MVFs work and some tips and tricks to using them. Isladogs, Arnelgp discussions are on point. Then readers could have a place to come to get information on the topic. Not a catch all for unrelated BS.

If we just fill it with other BS like explaining Dakota's db or criticizing it or telling us how much you love mvf or how much you hate it, then for other users to get to the technical discussion on using MVFs they have to wade through all the BS not related to the topic.

You do not seem to be able to help yourself from hijacking threads and discussing unrelated concepts. If it is slightly related then create a new thread and put a link to one or more related threads. I often do that and say something like "this thread is in response to ...link"
If you want a thread on why you should not use MVFs create a new one. And you can beat that dead horse by providing hundreds of links to other threads addressing that topic. But if you want a discussion on the tips, tricks, and techniques of using MVFs

I should have put this in a moderated forum to ensure discussion was limited to the topic.

 

[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 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. Lets assume the lookup table has a PK In this case tblRegions with a StateID pk.

When using the lookup wizard and I point to this table with a PK it does not give me a choice to store the Key or the Value. So it will store the key since I pointed to a lookup table with a PK.

But you can trick it, 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.
Or after you use the wizard change the bound column to the State column.

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.

 

[MajP]

Not sure how much more clear I can be. When using the wizard and you select the lookup table, and that table has a PK it does not give you the option to store the State Name. The wizard will bind the PK column for the lookup choice. You can trick it ahead of time or change the bound column after, but AFAIK you do not get a choice from the wizard to which column to bind. All you just did was parrot what I just explained, that the default (not selectable in the wizard) is the PK if it exists.

 

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

 

[MajP]

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.

That is fine. Create your own thread and have at it. Discuss all the design decisions and pros and cons you want. Have a field day. You can reference this thread and other threads. Again, not the intent here. There are no pros and cons here or design decisions. It is a discussion of HOW IT WORKS and HOW IT DOES NOT WORK. What is so hard about that? If you make a thread so encompassing it looses any value, which this one is quickly starting.

 

[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