Multivalued Combo Box

Question

I have a form with two cascading combo boxes and both of the "child" boxes are set to accept multiple values. The first one populates the table separating the values by comma (yay! - this is what I want) but the second one creates a combo box within the table.

I have compared the field properties of both and I cannot figure out how to get the second to populate the table with comma separators.

Do I need to "unbound" the form field from the table field to get to the "Look Up Wizard"? Is there a way to just do only that one field? I feel that I have to "unbound" everything from the form in order to do just one.

Thank you.

Answer 1

I used a query for the secondary combo box.

Row Source:  SELECT [queryChildren].[Child] FROM queryChildren;

Here's the query:

SELECT Children.Parent, Children.Child

FROM Children

WHERE (((Children.Parent)=[Forms]![RealForm]![cboParent]));

It builds the combo box just fine and I have the multiple values option selected, so that part works fine.

As you said:

"To configure a field as an MVF HAS to be done on the table level. When a control bound to a MVF displays its selected data it is in the form of a comma separated list."

Aha - the issue is that Access lists it in the table as both a dropdown box and if you click within the field and scroll to the right, it is also separated by commas.

Answer 2

And I disagree (and believe Ken will also). 

A Cascading Combobox is one that filters the RowSource by a selection in another combo. I'd be curious to see how you got that working from an MVF. I'm guessing your RowSource would look like:

SELECT ID, Description

FROM table

WHERE FK IN([Combo1].Value);

But that's just a an untried guess.

The two advantages to an MVF is that it presents a drop down with checkboxes for the user to select multiple values and then displays those values in a comma separated list. But when you try to use an MVF to filter other values it can get hairy. And that's why we recommend using an actual join table rather than the hidden one Access creates.

You said: "The second one, however, brings a dropdown box WITHIN the table being populated, which is NOT what I want - I want them separated by commas." I'm not sure what you mean by "Within" the table. To configure a field as an MVF HAS to be done on the table level. When a control bound to a MVF displays its selected data it is in the form of a comma separated list.

You can simulate this using a join table and a popup form.

Answer 3

As far as the cascading combo box goes - I've got that all settled and it works perfectly.

I apologize if I stated my question incorrectly earlier, I don't want to create a cascading combo from an MVF, I want the reverse - I have the cascaded combo box already in place, I just want the field it populates to hold more than one value.  (I might not be asking the question in the best way.)

I have two sets of cascading combo boxes - and one is working correctly (separating the descriptors by comma in the table being populated). The second one, however, brings a dropdown box WITHIN the table being populated, which is NOT what I want - I want them separated by commas.

The database we are adding records to requires one field to hold descriptors, sometimes multiple descriptors (think vendor with sometimes multiple products). It's going to be a really small database, maybe 200 records a year, but there's some functionality within Access I wanted to use versus writing VBA in Excel.

PLEASE, I realize that I could utilize subforms, but I don't think it will serve our purpose.

Answer 4

I agree with Ken, that the MVFs have a very limited use and do not constitute good database design. This also applies to the Lookup Wizard. Unless you are building WEB apps, lookup fields on the table level are not recommended.

If you can explain what your app does, what you are tracking and why you think you need MVFs, we may be able to help more. 

Creating a cascading combo from an MVF is extremely complex because of the many to many relationships involved. I would not even begin to attempt it.

Answer 5

Unless you have a real need to use a 'multi-valued field', i.e. your database is interfacing with SharePoint in the very specific context in which this feature is required, I would strongly advise against the use of this feature.  It conflicts with the basic principles of the database relational model as the table is not normalized to First Normal Form (1NF), whose definition is:

First Normal Form: A relvar is in 1NF if and only if, in every legal value of that relvar, every tuple contains exactly one value for each attribute.

Loosely speaking, in the language of the relational model, a relvar (relation variable) equates to a table, a tuple to a row (record) and an attribute to a column (field).

Instead, model the inherent many-to-many relationship type in the conventional way by means of a table which resolves the relationship type into two one-to-many relationship types.  If you have a need to return the values as a comma delimited string you can do so in a form or report.  You'll find an example of the former in StudentCourses.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file illustrates several possible interfaces for a binary (2-way) many-to-many relationship type, including presenting the values as a comma separated string expression.  Normally a conventional form/subform would be used, however.

For returning the values as a single string expression in a report a suitable VBA concatenation function can be used.  The Concat demo in my same OneDrive folder illustrates this using data from Northwind.  My file uses a function which calls the highly efficient GetString method of the ADO recordset object.  Allen Brown also provides a similar function using DAO at:

http://allenbrowne.com/func-concat.html

You refer to correlating (cascading) your combo boxes.  With a conventional model, subforms can be correlated, and the CorrelatedSubs demo in my OneDrive folder is a simple example, again using Northwind data.  A more advanced illustration of the use of correlated subforms is found in the StudentLog demo in the same folder.