Making form more functional - too many items in combo box

[Reviewer]

I am making a form to enter our reviews that we receive from customers and categorize the topics that the review is talking about. We have put together a list of Positive Comment Categories (approx 16) and Negative Comment Categories (approx 17). Each of the categories has subcategories (the amount of subcategories can range from 5-30 items). A review can talk about many different topics discussed and I need to be able to select all of them.



The form I have put together is NOT efficient for data entry. I have all of the subcategories in the combo box. I spend a lot of time scrolling through the options. What would anyone recommend to do to prevent the time from scrolling. Keep in mind that I will need to choose multiple categories and subcategories.

[June7]

See if this will help http://datapigtechnologies.com/flash...combobox2.html

Are you using multi-value fields? I NEVER do.

[Reviewer]

Yes, I am using multivalue fields. Why shouldn't I? What would be a better way to do this?

[June7]

Multi-value fields have to be handled differently than other fields. Here is one example of an issue which seemed to be related to multi-value https://www.accessforums.net/forms/a...ing-54505.html

Also review

https://support.office.com/en-us/art...3-B6624E1E323A

https://www.accessforums.net/queries...eld-51850.html

[Micron]

Hey June, never say never! OK, so only once since Access 2.0 have I ever used a multi-value field, but it was a special case. Without it, 30 records per person (20 users) per workday would have been 240*20*5* the number of jobs they worked on that day, each workday, all the same except for one value. Of course, this could have been split, but even that table would get about 90,000 records per year. The other reason was that the input data had to be transposed from columns to rows. Everything is handled by the Split and Join functions.

Multi-value fields have to be handled differently than other fields. Here is one example of an issue which seemed to be related to multi-value https://www.accessforums.net/forms/a...ing-54505.html

Anyway, I'm surprised your Integrated/All solution works. No one seemed to notice that All is a reserved word.

[June7]

Multi-value or not, the number of records you calculated are still there - somewhere. Access maintains a hidden table 'behind the scenes' that hold the multi-value field data.

Surely Split() would not work directly on a true multi-value field. Split() is a string manipulation function. Multi-value field data is not a string. I am not familiar with Join().

[Micron]

I'm beginning to see it's not a multi-value field in the sense that has been created for later versions of Access. Perhaps I've mis-characterized it. It's a csv string comprising eight values (thus multi-valued entry) so it does reduce the record count. If only I had a newer version... .

Join concatenates the elements (from my form) into a delimited array, and the array becomes the field entry.
https://support.office.com/en-gb/art...a-bdf22140650f

[June7]

Access 2007 has multi-value field capability. An Attachment type field is a type of multi-value.

[Reviewer]

so what is a better way to organize the data rather than using a multivalue field?

[June7]

Conventional approach is a related dependent table. This makes the data relationships apparent, not transparent as is done with multi-value field.