Display of only selected options in a multi-value control

[Hagridore]

I suspect I already know the answer I'm going to get on this, but I thought I'd ask anyway. I have a form with about 30 controls. I organized it carefully so that all the controls show on a single page. It actually looks really good. However, several of the controls allow multiple values. These appear as a check box showing up beside each item in the list from which these controls take their information. When I run a Filter by Form process, the form flawlessly pulls the data from the table that I'm seeking, and the check boxes are visible, both the ones with checks and without. In a few cases, in order to fit everything onto one page, I have most of the related list hidden, and use scroll bars. This works really well for data entry and for editing individual records.

Where it DOESN'T work like I want it to is in situations where the checked item(s) in the multi-value control don't show up in the box on the form without scrolling. In the table where these multi-value fields actually live, what shows up is just the code for each option, a simple numeral, with commas separating any multiple values. Is there a way to make my form show only the selected codes like in the table? My suspicion is that I'm going to be told no, that the best way to do this is to use a report and query. I hope I'm wrong.

 

[CJ_London]

If you insist on using multiselect fields, on your form, have a listbox control with your multiselect field as its controlsource, then size it to show all the options.

 

[Hagridore]

First of all, I understand that creating a control that shows all the options is one way to do it. I'm trying to find out if there is any way to have a control show only those options that have been selected.

Second, I'm really at a loss to understand the obvious distaste at best for what appears to me to be a very useful functionality. I keep seeing responses that indicate that people don't like the multi-value options newly offered in Access. These options appear to work well to me. But that aside, just exactly how would you go about doing what I need to do? I have several fields where there need to be multiple values in those fields. I can't get a whole lot more specific about my exact need, but lets say that it was a form that allows entry for each sales representative. There is a table for the sales representatives and another table that covers offices which includes cities. Different sales representatives are responsible for different cities, and each one likely is responsible for more than one. How would you allow for the entry of more than one city in each sales representative's record without using this functionality?

 

[CJ_London]

I don't think it is distaste - they have limited uses, but you can see from the forum that people misunderstand their limitations and use them inappropriately so they are advised not to use them. Multi Select listboxes have been around for a long time, but the point about these is that they are used on a form, not in a table. Now if you had the combo equivalent for use on a form - great, I'd go for that.

But by moving the functionality back to a table you are introducing inefficiencies in the design and making some things very difficult to do. A good example of inefficiency is indexing - multivalue fields cannot be indexed so if you have a large dataset and want to know how many people bought apples (assuming this is in a multivalue field) it will be very slow.

What you want to do will now be inefficient because of lack of indexing - probably not noticeable on small tables accessed locally, but large tables and/or across a slow network, it will be.

If you are using a multivalue field to store say which supervisor an employee works for, in the short term fine, but then the supervisor gets promoted and the employee is working for someone else. You can't put a date on it and you now need a whole routine to update the multivalue field - really it needs to be handled in a completely different way.

Where they can be useful is for small amounts of data where there are choices which don't change - for example a hotel room parameters, has bath/shower/sea view/pets allowed - but not which floor it is on (because the user could select more than one floor).

From my perspective as a professional developer, more and more clients are moving to SQL Server or MySQL as a back end so I need to preserve an upsize path.

But to answer your question, you'll need an unbound listbox with a rowsource to select your multivalue values from your main table with a criteria to limit the list to your current record. within this query you may need to link multivalue values to another table to provide descriptions

It would be something like

SELECT multivalue.value FROM myTable WHERE RecordPK=[RecordPK]

If you type this directly into your rowsource, the [RecordPK] will automatically pick up the RecordPK of your current record providing it is brought through in your form recordsource. Obviously change all names to suit.

If you weren't using a multivalue field then the code would be similar

SELECT myValue FROM refTable WHERE RecordFK=[RecordPK]

but with the benefit of indexing

 

[Hagridore]

Thank you for your tireless effort at answering my questions. I really appreciate it.

Is [RecordPK] an aspect or property of a control, or is it a field name that I need to change?

It appears to me that this might work as far as displaying information already stored in the table. But if someone needs to search based on the multivalue field, will it let them?

Again, thank you so much!