Clearing a multivalued combo box

[John Harrington]

In VBA, how do you clear a multivalued combo box?

Simply setting it to Null doesn't work.

Thanks in advance,
John

 

[Arvin Meyer [MVP]]

This ought to work:

Dim varItem As Variant

For Each varItem In Me.lstElevation.ItemsSelected
Me.lstElevation.Selected(varItem) = 0
Next varItem
Me.lstElevation.Requery

 

[Albert D. Kallal]

In VBA, how do you clear a multivalued combo box?

Simply setting it to Null doesn't work.

Thanks in advance,
John

Those multi-value listbox, or combo boxs are artually a realted table.

You can use the folwling code:

Private Sub cmdClear_Click()

' Clear all values...

Dim rstDel As DAO.Recordset

' delete selected records...
Set rstDel = Me.Recordset!FavColors.Value
Do While rstDel.EOF = False
rstDel.Delete
rstDel.MoveNext
Loop

Me.Refresh

End Sub

 

[John Harrington]

Thanks to both of you for your answers, but neither solution works (I
tried both and am pretty certain I tried them right).

This one does, though:

Me.MyMultiValue = Array() ' clears a multivalued combo box
called MyMultiValue.


Best regards,
John

 

[Dirk Goldgar]

In VBA, how do you clear a multivalued combo box?

Simply setting it to Null doesn't work.

I vaguely recall, but can't test it at the moment, that you can do it by
assigning an empty array to the combo box. Something like

Me.cboMultivalue = Array()

However, I could be remembering this wrong. If you test it, please post
back with the result.

 

[Arvin Meyer [MVP]]

Thanks to both of you for your answers, but neither solution works (I
tried both and am pretty certain I tried them right).

This one does, though:

Me.MyMultiValue = Array() ' clears a multivalued combo box
called MyMultiValue.

I'm glad that Dirk found something that works for you. The code I posted has
been working daily for the last 8 years, and I can't imagine why it didn't
work for you.

 

[Dirk Goldgar]

I'm glad that Dirk found something that works for you.

I think he found it on his own, just before I posted.

The code I posted has been working daily for the last 8 years, and I can't
imagine why it didn't work for you.

Your code is for a multiselect list box, while John was dealing with a combo
box bound to a multi-value field.

 

[Jeff Boyce]

Arvin

Could this be one of those instances when a bang ("!") is more appropriate
than a dot (".")? It's seemed to me, over the years, that Access has gotten
more and more finicky about having Me!MyOwnControl ...

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Albert D. Kallal]

Thanks to both of you for your answers, but neither solution works (I
tried both and am pretty certain I tried them right).

This one does, though:

Me.MyMultiValue = Array() ' clears a multivalued combo box
called MyMultiValue.

There is been some confusing in this post as to multi-select vs that of
multi-value....

The example code I have works for multi-value (curious..did the sample I
posted compile???).

 

[Albert D. Kallal]

I think he found it on his own, just before I posted.


Your code is for a multiselect list box, while John was dealing with a
combo box bound to a multi-value field.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

Hum, interesting, Arvin's code actually DOES work for a bound multi-value
combo box.

So, the following works for me:

Dim varItem As Variant

For Each varItem In Me.FavColors.ItemsSelected
Me.FavColors.Selected(varItem) = 0
Next varItem
Me.FavColors.Requery

End Sub

and:


' Clear all values...

Dim rstDel As DAO.Recordset

' delete any possbile selected records...
Set rstDel = Me.Recordset!FavColors.Value
Do While rstDel.EOF = False
rstDel.Delete
rstDel.MoveNext
Loop

Me.Refresh

and:


Me.FavColors = Array()


The above last use of the array function is new to me. I not even sure what
exactly the Array() function without any parameters returns...