X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Multi Value Fields    
post Mar 27 2012, 04:37 AM

UtterAccess Editor
Posts: 10,112
Joined: 8-November 07
From: South coast, England

Multi Value Fields (MVFs) were introduced into Microsoft Access with the 2007 release and are more and more frequently being used in Access Applications.
VFs are used on a form in conjunction with either ‘MVF’ Combo, or List, boxes, which differ from standard combo or list boxes as they are multi-select. Selections are automatically stored in the MVF table. Standard Combo boxes do not have a multi-select facility.
At first site MVFs appear to conflict with relational database normalisation rules as the MVF selections all appear in a single field separated by commas. This however is misleading as it is only a representation of the stored data. When an MVF field is created Access stores the selected data in a hidden table, which cannot be directly accessed, and hence normalisation rules are obeyed.
MVFs do require a different approach and the attached demo illustrates how to emulate standard combo box functionality using them.
The following functions are demonstrated.
1. Using multiple MVFs to select and display records in a subform (equivalent to Master/Child relationship)
2. Using ‘AND’ or ‘OR’ Boolean functions for MVF values.
3. Adding records that are ‘Not In List’
4. Removing values from an MVF field using code, either for a single record or group of records
5. Adding values to MVF fields using code;
6. Deleting records which have selected values in an MVF field in the record
7. Clearing MVF combo boxes
8. Opening a report filtered by MFV combo box selections
9. Report based on a query with both MVF records and separate records based on MVFs.
The coding required for MVFs is not dissimilar to coding required for standard fields; the main difference is in adding the term ‘.value’ to the MVF field when referencing them..
4 Functions are provided in the module ‘Mod_MVF_Functions’
Parameters for the functions in this module are ‘database independent’ as the specifics for the database are passed to the functions in the function arguments
1. AddMVFRecord
To implement the ‘Not In List’ function.
2. FindMVFText
This function returns the text displayed in the MVF combo box, it is the equivalent of (e.g.) Text = Me.cboCntrlName.Column(1)
3. fFindMVFRelTable
This returns the tablename, PKfieldName and the second column of the table related to the MVF field passed to the function
4. fMVFCountRecords
Finds the number of records in a table that has certain values in an MVF field that are passed to the function in a criteria string.
The one piece of code that does differ significantly from standard code is in the function ‘fFindMVFRelTable’; Here the demo uses a query that, given the source table and MVF fieldname, looks up the related source table using the system tables: ‘MSysComplexColumns’, ‘MSysObjects’, ‘MSysRelationships’ To use this, the relationship between the MVF fields and the ‘source’ table must be shown in the database relationships diagram. The code is set up to work for tables in the FE or in a linked BE database. If the relationship is not available in the relationships table then the source table details must be ‘hard coded’
I’d be pleased to hear any comments or suggestions you might have.
WARNING: MVFs are specific to MS Access (2007 and later) but they are NOT supported by other RDBMS (Relational Database Management Systems) e.g. MySQL, SQLite, SQLServer, etc. etc. While you could export your MVF data as a standard join table you would still need to rewrite any queries, forms, reports and code that use MVFs. If there is any possibility that you may need to update your db to use a different RDBMS then you are strongly advised NOT to use Access' Multi Value Field.
See also the Microsoft website 'using-multivalued-fields-in-queries' for more information.
Code fault in the original attachment: in function 'fFindMVFRelTable' when checking if tables are in separate BE files
strFileName = DLookup("Database", "MSysObjects", "ForeignName='" & strTblName & "'")
should read:
strFileName = Nz(DLookup("Database", "MSysObjects", "ForeignName='" & strTblName & "'"), "")
Edit 2:
Added link to MS website
Attached File(s)
Attached File  MVFs_II_V2.zip ( 73.92K )Number of downloads: 638
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 07:08 AM