MS Access Multi-select Combo Box Select All/None

Question

I have a combo box on a form that is linked to a SharePoint field, the combo box populates correctly however I am having difficulty trying to add VBA code to select all of the options or to unselect all options.

With a standard combo box you can use:

cmbBox1.value = ""

and that will reset the field. The same thing can be done with a list box that has multi-select enabled however this tosses an error, "This control is read-only and cannot be modified", with the combo box that has multi-select because of the lookup.

I have done some searching however no one seems to have a real answer other than to use a listbox instead and that isn't a solution here.

Has anyone worked with one of these fields and know how to select all of the options using VBA?

Here is a link describing this type of field but it does not discuss how to interact with it using VBA - http://office.microsoft.com/en-us/access-help/use-a-list-that-stores-multiple-values-HA010031117.aspx.

UPDATE:

There has been some confusion about the type of field I was describing so I have added some screen captures to show the difference between a combo box that allows multiselect, a list box that allows multiple options and a combo box with the option added.

First the field I was describing:

MultiSelect Combo Boc

Second the list box:

Listbox with multivalue enabled

Lastly the combobox:

Combobox with a Select All option

These images visualize the issue that was described. As you can see there are multiple check boxes that need to be selected or unselected. Normally I would not create a field like this but as described above this is how Access interprets a combobox from SharePoint that allows for multiple selections.

possible duplicate of How to add functionality for ComboBox <Select All> in Microsoft Access forms — Fionnuala
As I explained below this is not a duplicate of the Select All. The reason is that select all option is added by changing the query that feeds the combo box to include a new option. The combo box that I descride has the functionality to select multiple items by clicking in check boxes next to each of the items. Two very different functionalities. So the prolem was how to check or uncheck all of the boxes. My answer to that is below. — AxGryndr
Apologies, if I had realized that a single close vote was sufficient, I would not have voted. — Fionnuala
I have added some screen captures to the question to hopefully clear up the issue for anyone else looking for this solution. You were not the first person to misunderstand so I hope this will help. — AxGryndr

AxGryndr AxGryndr · Accepted Answer · 2013-05-27T09:51:14

After a ton of searching and trial and error I figured it out.

To unselect all of the check boxes it is

cmbBox1.Value = Array()

So with this information I figured that to select items they have to be in an array. Creating an array with all of the items that are in the combo box and then setting the combo box equal to the array will select all of the items.

I used a basic loop to set each element of the array

Dim SelVals(), i
ReDim SelVals(0 to cmbBox1.ListCount - 1)
For i = 0 to cmbBox1.ListCount - 1
     SelVals(i) = cmbBox1.Column(1,i)
Next i
cmbBox1.Value = SelVals

Obviously then you aren't limited to only using the entire contents - you could assign any array and those would be the values selected.

MS Access Multi-select Combo Box Select All/None

2 Answers