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

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?
 
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.
 
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
 
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.
 
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.
 
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.
pic1.png
pic2.png
pic3.png
pic4.png
 

Attachments

Last edited:
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.

1754153516301.png
 
@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
 
Last edited:
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.
 
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
 
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.
 
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?
 
What is really going on there I cannot tell you. But somehow you are querying the hidden table.
Somehow?... That sounds mysteriously magical. Can a shadow child table be created and a table trigger inserts, updates, or deletes each value in the shadow?
 
Last edited:
If a person has a different need, then of course, his forms and reports would look and work differently. It is easy to create numerous forms/reports with MVFs, each for a different person, medicine, and disease. The user makes that decision by selecting from various models.
With properly normalized tables, everyone can use the same form without having to add or remove any controls and forms.
 
Since there is a lot of discussion now on MVF, I created this thread to provide discussion on what you can do with them and how to use them. There is plenty of discussion of the cons of MVF and what you cannot do with them, so no need to rehash that here.

But assuming you are planning to use them this thread should focus on things like
1. How they work especially what is happening behind the scenes
2. How to query them, especially related data in the selection table
3. How to access in code such as Recordset of related values
4. If using them where you can get into problems.

To start with recommend reading Colin's post

I will provide this example that I got from someone (not mine) that has some interesting functions that work with the MVF fields. I demo a report pulling the child records of the hidden form.


My opinion is that there is a lot you can do with MVF, but you really need to understand the limitations. My issue with them they are seen as a shortcut for novice developers, but that is likely the person who will get into trouble because they do not understand how they work. If you spend some time understanding a little how they work then you can make the decision if they are safe to use for your needs. If you do not understand them, then you could end up painting yourself in a corner where you cannot do what you want with the data.
Hi MajP,

I've been examining Bernie Chipstick's MVF's Search DB and I'm already painted in a corner. I cannot understand how the query he builds is able to read the values in the hidden table, or maybe I don't need to concern myself with how it's done, it just works.

Code:
strSql = "SELECT MSysRelationships.szReferencedObject, MSysRelationships.szReferencedColumn " & _
             "FROM (" & strFileName & "MSysObjects " & _
             "INNER JOIN " & strFileName & "MSysComplexColumns ON MSysObjects.Id = MSysComplexColumns.ConceptualTableID) " & _
             "INNER JOIN (" & strFileName & "MSysObjects AS MSysObjects_1 " & _
             "INNER JOIN " & strFileName & "MSysRelationships ON MSysObjects_1.Name = MSysRelationships.szObject) " & _
             "ON MSysComplexColumns.FlatTableID = MSysObjects_1.Id " & _
             "WHERE (((MSysObjects.Name)='" & strTblName & "') AND ((MSysComplexColumns.ColumnName)='" & strFldName & "'));"

MVFsSearchER.png


HiddenTableMVF.png


Although the ACE engine doesn't support table triggers, would it be possible to automatically execute a function that CRUD's MVF values to individual records in a standard table?
 
Last edited:
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
Hi Arnel,

I like this demo a lot! It's real clever how it concats multiple values and puts them in a regular text field. I selected many colors for the USA flag and guess I went overboard because it's displaying chinese characters at the end. Is there a way to limit selections so the concat doesn't store more than 254 characters in a text field? Can a memo field be used to increase capacity for values like links, paths, etc.?

Selections.PNG
TextFieldOverflow.png
 
Last edited:
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.
 
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.

Me thinks the overflow problem happens because recordsets are being used. What if there's a requirement to store long text, like paths, links?

Code:
Dim rs As DAO.Recordset         'Related records
Dim rsMV As DAO.Recordset       'Multi-valued field recordset
...
'Loop through the matching records
    Do While Not rs.EOF
        If bIsMultiValue Then
            'For multi-valued field, loop through the values
            Set rsMV = rs(0).Value
            Do While Not rsMV.EOF
                If Not IsNull(rsMV(0)) Then
                    strOut = strOut & rsMV(0) & strSeparator
                End If
                rsMV.MoveNext
            Loop
            Set rsMV = Nothing
        ElseIf Not IsNull(rs(0)) Then
            strOut = strOut & rs(0) & strSeparator
        End If
        rs.MoveNext
    Loop
    rs.Close
 
Last edited:
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.
 

Users who are viewing this thread

Back
Top Bottom