I've got a multiselect ComboBox and I'd like to manipulate the collection of selected items from within my VBA module. I've learned that I can iterate through the .Value property with a "For Each varVariant in cboComboBox.Value" construct to see the collection of all items selected from the list. But how can I change the selected items? The .Value property seems to be read-only as far as I can tell. Statements like "cboComboBox.Value = 9" or "cboComboBox.Value = varNewVariant" always generate error messages. What am I missing?

The selection is boolean, so if one item is selected and your want it deselected you should do Me.cmbComboCox.selected(X) = False

do you mean multi-select LISTbox ? i don't think combo boxes can be multi-selected....? maybe that's why you're receiving the error...

Yeah, it has to be a listbox. There is no way to do it with a combo box. In my previous posting I was thinking on a listbox. If mwcrepeau is using a combobox then that code is useless.

Yes, it's a combo box. They can be multi-selected in Access 2007 (but not earlier versions). ItemsSelected and Selected do not, however, seem to work. The former is always an empty collection, and the latter are always false, even for items that are clearly selected. .Value is the only thing that correctly reflects which items are selected. It works the way ItemsSelected is supposed to. But it seems to be read-only. I can't figure out how to change it!

Yes, it's a combo box. They can be multi-selected in Access 2007 (but not earlier versions). ItemsSelected and Selected do not, however, seem to work

ookkkkkkaaayyy.... yo umust have a different version fo 2007 to my 2007... or you've misunderstood what it is you're looking at: can you post a picture of your form and point to the thing that you are calling a combobox? and also, just because i searched and could not find: (edit: could you please ;)) tell us where the setting is for the combobox control to turn it into multiselect?

a combo box is a one-line control with a downarrow buttony looking thing which, once pressed expands the control to look more like a list.

a listbox is a multi-line control WITHOUT a dropdown buttony thing.

see my attachments for the two different types. as you can see, i'm using 2007, - the selected type in acces 2007 is highlighted with an orange border in design view...

Sure. Attached are three images. The first is the table the form is bound to, with the field in question (Trainees). In the Lookup properties tab you can see that it is a combo box and that "Allow Multiple Values" is set. In the second image you see the actual form, and the control (again Trainees) that is bound to the table field. The drop-down list has appeared and the names of three trainees are selected. In the third image I have clicked OK and the three names are displayed in the text box portion of the combo box.

I hope that helps!

Yes, it is the new multi-value fields (of which most Access developers despise as it is really just a lookup table which is obscured instead of designing with putting a junction table of your own in).

Anyway, check out this link and go to the part where Albert D. Kallal posts as it might hold the key to your problem:
http://microsoft-news.for-um.de/archive/index.php/t-45702.html

Sure. Attached are three images. The first is the table the form is bound to, with the field in question (Trainees). In the Lookup properties tab you can see that it is a combo box and that "Allow Multiple Values" is set. In the second image you see the actual form, and the control (again Trainees) that is bound to the table field. The drop-down list has appeared and the names of three trainees are selected. In the third image I have clicked OK and the three names are displayed in the text box portion of the combo box.

I hope that helps!

wow! that's the strangest thing i've ever seen in my life!

Yes, it is the new multi-value fields (of which most Access developers despise as it is really just a lookup table which is obscured instead of designing with putting a junction table of your own in).

Anyway, check out this link and go to the part where Albert D. Kallal posts as it might hold the key to your problem:
http://microsoft-news.for-um.de/archive/index.php/t-45702.html
fabulous thread, thanks boB :)

Sure. Attached are three images. The first is the table the form is bound to, with the field in question (Trainees). In the Lookup properties tab you can see that it is a combo box and that "Allow Multiple Values" is set. In the second image you see the actual form, and the control (again Trainees) that is bound to the table field. The drop-down list has appeared and the names of three trainees are selected. In the third image I have clicked OK and the three names are displayed in the text box portion of the combo box.

I hope that helps!

i can see one application of that which would be fabulous: emailing. select all your recipients and it's folded back down to one line.

nice.

After all the discussions I have come across about the ability to have tick boxes in list boxes it looks like there may actually be a solution if you use this mv combobox. May need a little manipulation but on the first glance it may be worth while giving it a go.


David

There's definitely some good info in Albert D. Kallal's post. I am exactly what he describes: a novice developer trying to do things the easiest way possible (actually this is my first attempt at creating an Access DB and writing VBA modules).

But I'm afraid I still don't quite understand how to modify the collection of selected items. Kallal says "you just stuff the field into a recordset..and deal with recordset code as you always done, and this allows you to use/read/update the mv data". But how do I do that? Kallal says that multi-value fields "are in fact regular normalized tables behind the scenes that you simply don't see". Since the table is "behind the scenes", how am I supposed to "stuff it into a recordset"? Normally I'd do CurrentDb.OpenRecordset, but in this case, what are the names of the table and fields?

Any ideas?

My personal view - go away from the multi-valued fields and go to something that is more easily manipulated. I haven't used the multi-value fields and probably won't, at least in the forseeable future. So I would just use an extra lookup table/junction table setup so I can work with it the way I know.

I haven't even tried to do the stuff Albert has done and therefore I sent you to it since it will work for you but you'll need to figure it out (or we find someone else on the forum who has programmatically dealt with these and I wonder how many there are, if any).

i agree with boblarson. especially being a novice (as i am also), i would use the "tried and true" method until more people/gurus have paved the code for manipulating the multiselect comboboxes. i'm sure it's a nice feature and people will use it in time, but unless you are willing to be the beacon of light for everyone to follow your example, i'd just stick with what has worked for everyone in the past - at least that way you'd have more documentation online to follow suit on, and more people would be able to help you troubleshoot.

Perhaps...

Me.ComboName.Selected(someItem) = True
http://msdn.microsoft.com/en-us/library/bb240110.aspx

Regards,
Tim

This doesn't directly answer the question, but this is some code we've been using at work that selects/deselects all the items in a multiselect combobox. The combobox is named "ReportList". When the button changes the items to all selected, it also changes the caption on the button to "Clear All". When selected again, it clears the selections and changes the caption to "Select All."

Private Sub cmd_ClrSel_Click()
Dim X(), i, j, SelectedVals
ReDim X(0 To Me.ReportList.ListCount - 1)

For i = 0 To Me.ReportList.ListCount - 1
X(i) = Me.ReportList.Column(1, i)
Next i

SelectedVals = Me.ReportList.Value

If IsNull(SelectedVals) Then
Me.ReportList.Value = X
Me.cmd_ClrSel.Caption = "Clear All"
Else
Me.ReportList.Value = Array()
Me.cmd_ClrSel.Caption = "Select All"
End If
Me.Requery
End Sub