How to change .Value of multiselect ComboBox

[mwcrepeau]

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?

 

[scalextric59]

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

 

[wiklendt]

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...

 

[scalextric59]

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.

 

[mwcrepeau]

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!

 

[wiklendt]

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...

 

[mwcrepeau]

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!

 

[boblarson]

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

 

[wiklendt]

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!

 

[wiklendt]

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

 

[wiklendt]

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.

 

[DCrake]

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

 

[mwcrepeau]

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?

 

[boblarson]

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).

 

[wiklendt]

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.

 

[pono1]

Perhaps...

Me.ComboName.Selected(someItem) = True

http://msdn.microsoft.com/en-us/library/bb240110.aspx

Regards,
Tim

 

[TheDuck65]

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

 

[wstlaurent]

I have come across this in Access 2013 and haven't built a database in a while so it is very new to me. I formerly used junction tables to create many-to-many relationships and this seems like it could be easier. I use a multi select combo box to select (for example) multiple employees (from Employees table) who participate in a single event (from Events table). The result is saved as a single record in the EventParticipation table (my junction table). It saves time. For example, instead of having to enter 12 records (one for each of 12 participants) I can enter one. When viewing that table I will only see the one record, however, if I create a simple query I am able to view individual records (one for each participant). When I build the query (design view) and show the table containing the field that stored the selection there are these "sub fields," which when added to the query will display each of the individual parts of the multi selected combo box. From this query I build reports to display the results desired.

I do have to caution though, the disadvantage I have found (and only one so far) is that when using a multi select combo box (or list box for that matter) to create a record in a table is the following. You can always view the separate records through a query you build, but if you want to edit any of the other data for those records the change will be made to each of the other Employees' record which were created in the multi select step. For example, if you add a copy of an agenda for an Event to an EventParticipation record in which you selected multiple participants using a multi select combo box then that attachment will be part of the query results for each of the participants. If you wanted to attach a different document or add another document to just one person's record using the query that additional document or different document would be reflected as a change back to the record in the EventParticipant table showing for each of the other 11 Employees' records. Anyway, not a major problem as long as you don't plan on editing records through a query. I think of the database world this way...

Use forms to enter records into tables only (and make changes later)
Use tables to view records (and make changes later - although prefer forms)
Use queries to view records (never to make changes to records)
Use reports to view records (never to make changes to records)

If you abide by these rules then the multi select combo box seems to work fine.