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

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