How to Properly Query and Search Multi-Valued Fields in a Form?

[Anurag R]

Hello everyone,

I am new to MS Access and I'm stuck on a problem with searching multi-valued fields. I am hoping for some guidance on the correct way to solve this.

My Goal:
I am building a database to track project details. A key requirement is that some fields, like Sensors and Activities, must store multiple selections for a single project.

My Setup:
I have a main table with several multi-valued fields. These were created using the Lookup Wizard and are based on simple lookup tables (e.g., a Sensors table with SensorID and SensorName).

The data entry part works perfectly. I can create a new project, select multiple sensors (e.g., "IMU Data" and "Vicon") from a checkbox list, and it saves correctly.

The Problem I Am Facing:
I am completely stuck trying to create a search form. My goal is to allow a user to select one or more sensors from a multi-select list box on a search form, and then display all projects that use any of the selected sensors.

For example, if the user selects "Vicon", the form should show all projects that used a "Vicon" sensor, even if they also used other sensors.

I tried to add criteria directly to the form's source query, like this:
Like "*" & [Forms]![Search_Form]![lstSensors] & "*"

When I do this, I get an "Enter Parameter Value" pop-up for the list box control on my form. I understand that Access cannot read the selections from a multi-select list box this way.

My Question:
What is the correct method to create a search form to retrieve the data from a multi-valued field column similar to the other simple text column?

Also want to know, whether it possible to do something like this in MS Access as I am not able to find any related videos regarding this specific requirement, which specifically shows how to retrieve the information in the search form using Multifield combo box. I would really appreciate your guidance on this unique problem.

I would really appreciate your guidance on this. Thank you!

 

[Uncle Gizmo]

there is a video on my website here https://niftyaccess.com/multi-value-fields-links/ -- by Takeshi K ---- which demonstrates some very interesting VBA for extracting information from multi-value fields ... might well provide you with some inspiration ---

 

[theDBguy]

My Question:
What is the correct method to create a search form to retrieve the data from a multi-valued field column similar to the other simple text column?

Hi. Welcome to AWF!

As you may know, an MVF column is not the same as a simple text column, so searching within an MVF would need special handling. I believe what you want to achieve is possible, but if you can avoid using MVFs, then your job would become easier.

To help you with your current problem, you might consider posting a small sample db with test data, so we can try to see how we can make it work.

 

[MajP]

See attached mvf search form.
Thread 'MVF Technical Discussion and Uses (If you plan to Use Them)' https://www.access-programmers.co.u...sion-and-uses-if-you-plan-to-use-them.334422/
Yes very doable, but other alternatives exist.

 

[isladogs]

Also have a look at my article:

Multivalued Fields

Multivalued fields (MVFs) were introduced with Access 2007. This article explains what MVFs are and how they can be used. However, most experienced developers avoid them completely. This article explains why this feature is really not a good idea

www.isladogs.co.uk

For info, multiselect listboxes are not the same thing as multivalued fields

 

[Anurag R]

Hi. Welcome to AWF!

As you may know, an MVF column is not the same as a simple text column, so searching within an MVF would need special handling. I believe what you want to achieve is possible, but if you can avoid using MVFs, then your job would become easier.

To help you with your current problem, you might consider posting a small sample db with test data, so we can try to see how we can make it work.

Thank you for you response. I have attached sample DB on which I am working

 

[June7]

What worked for me in my db test:
1. VBA builds comma-delimited string of UNBOUND listbox selections
2. VBA sets form Filter property with: Me.MyMVField.Value IN(strList)
3. VBA sets form FilterOn property to true

Code to build filter criteria in step 1 will certainly be a bit more complicated to allow for selection in multiple listboxes and to allow for any combination of listboxes. For example of building criteria from multiple controls, review http://allenbrowne.com/ser-62.html

 

[MajP]

You can use a MVF in several ways
You can use an MVF control to filter a standard field or an MVF field
You can use a multi select listbox to filter a standard field or an MVF field.

Since you want to have lots of other controls in you search form I think it would be easier to use a standard multi select listbox to query both types of fields.

I will have to do a demo of that. There is an example (I linked) of using an MVF control to query an MVF field.
I think this shows using an MVF control to query a standard field

Multi-value field (MVF) Faked Form.

simple demo to save in Normal table the values selected in Multi-value list. Related threads: https://www.access-programmers.co.uk/forums/threads/sorry-folks-access-world-will-not-accept-my-posts.334416/page-4#post-1970100...

www.access-programmers.co.uk

But I do not think anyone has demoed using a standard multiselect listbox to query an MVF. However, I think that is some version of what June7 proposes. In truth it is really no different than querying the standard field just applied to the value field of the MVF.

 

[plog]

I think you need to ditch the multi value fields and fix your structure.

Before I get into that, don't use spaces in table/field names. It's going to make coding/querying difficult later on. Use underscores (Like_This) or camelcase (LikeThis).

In the database you posted you have a main table (AAS_Main Table) and then 4 very simple tables linking to it (Additional Devices, Activities, Sensors, Support System). Those four tables should be combined into one table to hold all that data with an additional field to hold what category each of the records is:

tblAttributes
AttributeID, AttributeType, Attribute
1, Sensor, Imu Data
2, Sensor, Vicon
...
5, Support System, none
6, Support System, Hip Exoskeleton
...
7, Activities, none
8, Activities, Overhead Screwing
...

Then you add a junction table that lines up records from the main table to each attribute it has:

tblMainAttributes
MainID, AttributeID
1, 3
2, 1
2, 3
2, 6
...

Then when you need to find out which project has each attribute it's your form based on a simple query.

 

[Anurag R]

I think you need to ditch the multi value fields and fix your structure.

Before I get into that, don't use spaces in table/field names. It's going to make coding/querying difficult later on. Use underscores (Like_This) or camelcase (LikeThis).

In the database you posted you have a main table (AAS_Main Table) and then 4 very simple tables linking to it (Additional Devices, Activities, Sensors, Support System). Those four tables should be combined into one table to hold all that data with an additional field to hold what category each of the records is:

tblAttributes
AttributeID, AttributeType, Attribute
1, Sensor, Imu Data
2, Sensor, Vicon
...
5, Support System, none
6, Support System, Hip Exoskeleton
...
7, Activities, none
8, Activities, Overhead Screwing
...

Then you add a junction table that lines up records from the main table to each attribute it has:

tblMainAttributes
MainID, AttributeID
1, 3
2, 1
2, 3
2, 6
...

Then when you need to find out which project has each attribute it's your form based on a simple query.

Thank you for your suggestion. I have a requirement that if I click sensor field on the on the entry form, it should display all the sensors that are entered and at the same time allow multiple entries as shown in the image and similarly also for other fields. However, if I follow your way then there is a problem of obtaining all the other fields such as Activities, support system along with the sensor which is not ideal. Please correct me if I am wrong

 

[June7]

With Plog's suggested data structure, all attributes are stored vertically. Each selected attribute is a record. This eliminates MVFs. Then if you want to display the attribute values under attribute type columns horizontally in query or report, would likely need VBA to concatenate into CSV strings. Review http://allenbrowne.com/func-concat.html

How many MVFs are there?

 

[plog]

I have a requirement that if I click sensor field on the on the entry form, it should display all the sensors that are entered and at the same time allow multiple entries

Subform(s).

Create a form that contains a subform (a one-to-many form) - Microsoft Support

Learn how to create a form in Access that contains a subform (also known as a one-to-many form, a parent/child form, or a hierarchical form).

support.microsoft.com

You remove the combo boxes for those 4 things and either make 1 subform for that entire table I described or make 4 subforms--1 for each attribute type (Additional Devices, Activities, Sensors, Support System). Subform(s) will operate just like what you have now and look better actually. You can show all the attributes associated with the record and at the bottom of each subform the user can add more attributes if needed.

Give the link I listed a check.

 

[June7]

Yes, subforms could work. I have actually done this (once). Build each subform with static criteria in Filter property for particular attribute type. A combobox will have attribute values relevant for that type only.

Might also be able to apply this concept to report with subreports and avoid the VBA I referenced before.

 

[Anurag R]

Yes, subforms could work. I have actually done this (once). Build each subform with static criteria in Filter property for particular attribute type. A combobox will have attribute values relevant for that type only.

Might also be able to apply this concept to report with subreports and avoid the VBA I referenced before.

I will give it a try. Thank you.

 

[arnelgp]

you need VBA to filter your Query.
see your Search form.

 

[Anurag R]

you need VBA to filter your Query.
see your Search form.

This is what I was looking for. Thank you so much, really appreciate your support. I will build it on from here based on your approach.

 

[Gasman]

With Plog's suggested data structure, all attributes are stored vertically. Each selected attribute is a record. This eliminates MVFs. Then if you want to display the attribute values under attribute type columns horizontally in query or report, would likely need VBA to concatenate into CSV strings. Review http://allenbrowne.com/func-concat.html

How many MVFs are there?

@theDBguy has a simpler version here

 

[RonPaii]

In sample databases you will find 2 solutions to display multiple selections without using MVF

arnelgp posted "Multi-value field (MVF) Faked Form.
I posted staring with arnelgp's db "Multi-value field (MVF) using tree control".

The frmUsers displays using both concatenated string and sub-form.