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

[DakotaRidge]

Whilst of course I wouldn't recommend it, there is no reason why you can't use URLs as MVF selections:

View attachment 120792

Colin, are the URLs still hotlinks in your example? Just having multiple attributes is not enough.

When I viewed them in my form, they were text strings. The URLs were concatenated to the field name. Did I do something wrong? Please advise.

Perhaps this is another time when MVFs do not work as needed. Are multiple URLs in a combo box hot?

 

[MajP]

The URL in the displayed MVF would be concatenated strings not URLs. Remember as shown that field is really holding foreign keys not the value you see shown. To get to the URLs you would have to link to the lookup table.

 

[isladogs]

Agreed.
It doesn't matter whether you use a value list or a lookup table for your MVF field. The displayed value(s) will just be strings and not active.
To have URLs that are active, these need to be individual records in a standard text box

 

[gemma-the-husky]

That's the good thing about Access, you can do IT your way. When A2007 was introduced, I saw MVFs as something new to learn and use. I did a presentation about them at a local UG meeting. I started using Access in 1998. It was a good program then, even better now.

It took MS a few years to explain what it had done. Other companies had MVFs before A2007. By the time MS explained how MVFs work, many professional developers had abandoned them. But they are good for them because non-coders and people who do not use combo boxes will use MVFs and get some of the same benefits. After we screw up things, developers can be hired to implement combo boxes and fix the problems. Both users and developers win. Users get a solution quickly, and then developers get hired later.

Without MVFs, users wouldn't know what to ask for. "Today, I want to select 20 companies from a list of 1000. Tomorrow I want to select another 20 companies. Some of the same companies can be in both lists. Now let me create both lists."

If MVFs can do the job, then the organization wins. Organizations should bring in the developers to do the coding, and build the analytical and statistical tools.

I just hope that AI won't take projects away from developers. It is scary thinking that all that code and those combo boxes will be at your fingertips soon. What will old people do? Colleges won't need to teach Access anymore. There won't be a need for more Access textbooks. MS will eliminate the Team. This is scarier than I originally thought.

But a lot of this starts with analysing the data correctly, and constructing a well normalised data schema. It's not that an inexperienced Access user has a way to do something. It's more that he is sure that the shortcut he is using produces the correct result. The underlying issue is that "apparent" solutions can conceal problems within the database. For instance if your system contains 1000 sales orders, you want to be sure that a report of a section of those orders showing 250 orders WOULD show 1000 orders if you selected every record - and that you haven't lost some along the way because the data structure left some orphan records. The more data you have the more vital it is to make sure there are no data leaks. You need all the order dates to be sensible for instance. No nulls, no bad/out of range date values, as well as all records linked to companies and no orphans. I find a lot of my Access development is defensive. Even as the developer I use the forms, and not just use tables directly, because I want the protection of all the validity and sense checks I built into those forms.

 

[DakotaRidge]

Thanks, Pete and Colin. I was hoping that I could put live URLs into an MVF, but it won't work.

 

[DakotaRidge]

But a lot of this starts with analysing the data correctly, and constructing a well normalised data schema. It's not that an inexperienced Access user has a way to do something. It's more that he is sure that the shortcut he is using produces the correct result. The underlying issue is that "apparent" solutions can conceal problems within the database. For instance if your system contains 1000 sales orders, you want to be sure that a report of a section of those orders showing 250 orders WOULD show 1000 orders if you selected every record - and that you haven't lost some along the way because the data structure left some orphan records. The more data you have the more vital it is to make sure there are no data leaks. You need all the order dates to be sensible for instance. No nulls, no bad/out of range date values, as well as all records linked to companies and no orphans. I find a lot of my Access development is defensive. Even as the developer I use the forms, and not just use tables directly, because I want the protection of all the validity and sense checks I built into those forms.

Dave, you are a lucky guy to have data when you start work on a database. I have always had to conceive of an application and compile the data. When I got started on my finance and health database in January, I only had a vague idea for what I wanted. I mentioned that to a user group, and George suggested that I subscribe to Quicken. Knowing that I don't like COTS, I poo-pooed that idea. But I went to the library and checked out "Quicken for Dummies." The book showed me that Quicken isn't for me. There are no images and no speech in it. I must have both.

So I purchased an accounting textbook and later a copy of one of Dave Ramsey's books. Then I discovered the Kiplinger Personal Finance magazine at the library, and it inspired me to create several tables/forms/reports.

A couple of months later, a relative told me that he was suffering from a major disease. That sent me off into the personal health world. I justified the shift because dealing with any health problem costs money. There are doctors and dentists to pay, and meds to purchase. So. every module in the health domains has a field for cost. Every disease or illness is assigned to a family member. So. each module is a one-to-many relation in several ways. I use lookup tables multiple times, so I consider the design to be many-to-many-to-many. But I wouldn't call the design DKNF.

A couple of months ago, I had my annual medical exam. My report had 46 tests that I built into the database. Because I need graphics to work with data, I created pseudo-pie charts for the four main diseases: diabetes, liver disease, heart disease, and kidney disease. The charts use checkboxes to tell a person if their test scores are in or out of range for their age, race, and gender. I haven't gotten around to cancer yet, just too much information about cancer to get my head around.

A few weeks ago, I learned about the food recalls announced by the CDC and USDA. There is a recall every few days. I have encoded these data into the database. I found a map in a Kiplinger article, and I digitized it in PowerPoint. I used the map for several forms, and then I had the idea to move labels and checkboxes in the form. I learned a little about animation in Access five years ago at DevCon. When I ran into a problem, I asked developers on AW about animation.

Recall data keeps coming in from the CDC, USDA, and MSN. The challenge is that these organizations publish different data, they are not consistent. That means that no matter what the tables are designed to do, there are always missing data in a recall announcement. Sometimes an announcement lacks the states impacted by a recall, other times there may not be a name for the bacterium. I only care about recalled foods in the five states where my family members live.

It gets even more challenging because the database needs to store detailed information about diseases, medicines, and nutrients. I am not a physician nor a nutritionist so I am always learning something new. When I get detailed data into the database, I then need to add graphics and speech.

I would love to have had data for the application when I started creating it. In a way, it is good that I didn't. I love doing research and compiling disparate data. Don't show me a page of data for anything, I can't handle it. Show me graphics, no charts, graphics.

 

[arnelgp]

i must submit may entry.
if you find errors on the sample db, just rectify them.
this is for you to develop further.

please open 1_README form for some background
and attributions.

 

[DakotaRidge]

i must submit may entry.
if you find errors on the sample db, just rectify them.
this is for you to develop further.

please open 1_README form for some background
and attributions.View attachment 120798View attachment 120799View attachment 120800View attachment 120801

Arnelgp, that's an interesting use of an MVF.

i must submit may entry.
if you find errors on the sample db, just rectify them.
this is for you to develop further.

please open 1_README form for some background
and attributions.View attachment 120798View attachment 120799View attachment 120800View attachment 120801

Arnelgp, that's an interesting use of MVFs. I would expand it to something like this, where the statements are longer. Both the country name and the policy are MVFs. The team would have to agree on the text.

The team could add and delete statements later. It could add others for Race, Languages Spoken, Political Party, Currency, etc.

I have no idea if there is a maximum number of MVFs allowed in Access. It would be interesting to find out. The maximum number that I have ever had in a table/form/report is ten. I only stopped because I ran out of screenspace.

 

[isladogs]

@DakotaRidge
Just to be clear, @arnelgp is NOT using a standard MVF structure in his example database

@arnelgp
That is indeed an interesting way of faking an MVF and it appears to work perfectly using the ConcatRelated function

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

• f_34A1435FD319449FAEFEB746E6C0A455_TempField*0 for the multiselect combo box

Also when I imported your tblDummyMVF into another database it created another deep hidden attached table:

• f_7042B221BCAF40019C9B08D67A5EBFB5_LukID for table tblDummyMVF

In other words, although its not using a standard MVF field for the FlagColors, behind the scenes the approach still depends on MVF functionality

 

[arnelgp]

it is an MVF. See the table structure you will notice that it is combobox, multi-select. maybe you have learned something new aablut MVF.

 

[isladogs]

I agree that table tblDummyMVF has an MVF table structure and which is used in the generated form frmMVF
I'll need to study the code more carefully to understand how exactly it works

Question:
How could you use this to query filtering for countries with a certain combination of flag colours e.g. red, white and blue such as UK & USA

 

[arnelgp]

you browse to code of the Public Sub Init() of from frmMVF.
the Init() sub, does some initialization, like where to position the pop-up form (frmMVF)
and setting the Listbox Rowsource. you pass the SQL string as the rowsource.
the Listbox is bound to LukID field of table tblDummyMVF.
since that field is already designed as MVF, you do not need to
set the Listbox's (multi-select) setting.
also (on V2), the listbox Font/Fontsize is copied from the underlying combobox font/size.
you pass the tablename of the juction table(tblCountryFlagColor), the first FK fieldname("CountryID_FK") and it's current value (from the form),
you also pass the second FK ("ColorID_FK") to the public sub Init().
then down the code you instantiate a recordset filtered to current CountryID.
you loop though the the ListItems of the listbox and check if it is in the recordset (.FindFirst).
if it is then, checked mark it (by setting Selected(index) = True).
if it is not continue with other items on the list.

that's how the items are checked when the form (frmMVF) finally shows up.
note that the form is set as DataEntry, so you are always adding to the table.
so before closing, you delete the New record on this form by using Me.Undo.

record is saved on junction table by the OK button.

 

[isladogs]

Thank you. That’s very helpful.
I’m not at my computer at the moment but on a first read that all makes sense to me.

Have you had time to think about querying the countries and flag colours?

 

[arnelgp]

i will look into it later.
but in reality, max flag colors can be 3 to 4 colors. The colors on the Textbox is for display only. the actual records are save to the Junction table.

 

[arnelgp]

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.

 

[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 Acceess manages these using system tables.

www.isladogs.co.uk

Hope that helps