Clearing a multivalued combo box

  • Thread starter Thread starter John Harrington
  • Start date Start date
J

John Harrington

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

Simply setting it to Null doesn't work.

Thanks in advance,
John
 
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
 
John Harrington said:
In VBA, how do you clear a multivalued combo box?

Simply setting it to Null doesn't work.

Thanks in advance,
John
Click to expand...

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
 
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
 
John Harrington said:
In VBA, how do you clear a multivalued combo box?

Simply setting it to Null doesn't work.
Click to expand...


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.
 
John Harrington said:
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.
Click to expand...

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.
 
Arvin Meyer said:
I'm glad that Dirk found something that works for you.
Click to expand...

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.
Click to expand...

Your code is for a multiselect list box, while John was dealing with a combo
box bound to a multi-value field.
 
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.
 
John Harrington said:
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.
Click to expand...

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???).
 
Dirk Goldgar said:
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)
Click to expand...

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