FAO Spinks (?) re : list box multiple selections

[Guest]

Hi there,

Think I managed to delete a thread by marking your answer as useful.

Just to say thanks for the code for selecting multiple entries from a list
box - it worked a treat and was exactly what I was looking for.

Well, almost. It works fine, but when you move off a current record, the
items remain selected, and you have to manually deselct them before selecting
items for the next record. This isn't ideal - especially as it means if you
choose to subsequently simply add more items to to a particular field, you
can't without reselecting the existing ones.

It may just be a limitation I'll have to live with, but wondered if there
were any fixes?

Also - will something similar work with combo rather than list boxes?

Either way, thanks again for your help.

Iain

 

[Douglas J Steele]

Only List Boxes allow multiselect.

To clear what's been selected when you move to the next record, you can set
the list box to Null in the form's Current event.

 

[Dirk Goldgar]

Only List Boxes allow multiselect.

To clear what's been selected when you move to the next record, you
can set the list box to Null in the form's Current event.

If the list box is multiselect, I don't think that will work. Unless
I'm overlooking something, Iain will need to cycle through the list
box's ItemsSelected collection and set each item's Selected property to
false. For example,

Dim intI As Integer

With Me.lstMyListBox

For intI = (.ItemsSelected.Count - 1) To 0 Step -1
.Selected(.ItemsSelected(intI)) = False
Next intI

End With

 

[Guest]

So where would that code go?

(Unfortunately I'm not from a coding background.)

Iain

 

[Dirk Goldgar]

So where would that code go?

(Unfortunately I'm not from a coding background.)

Iain

I don't have all the background on your original thread, but if you want
the list box to start out with no items selected each time you move to a
different record, you'd create an event procedure for the form's Current
event, and put the code I posted in there. Of course, you have to
replace "lstMyListBox" with the actual name of your list box.

Is that enough information to let you get the job done?

 

[Douglas J. Steele]

If the list box is multiselect, I don't think that will work.

Setting it equal to Null works fine for me, Dirk, even with multiselect list
boxes. Are you seeing some other behaviour?

 

[Dirk Goldgar]

Setting it equal to Null works fine for me, Dirk, even with
multiselect list boxes. Are you seeing some other behaviour?

Yes, I am. The Value property of a multiselect list box is always Null,
and setting the list box to Null has no effect on the selected items. I
tested to confirm this, using Access 2002. I assume you tested also;
does Access 2003 behave differently?

 

[Guest]

Thanks Dirk,

That works a treat, and is exactly what I was looking to achive.

Hopefully at some point I'll figure out how to recreate that in a web page
using something like ASP, but for the moment all the project's immediate
problems have been solved - ie a complex search on a web page using ASP, and
efficient, typo free keyword entry.

Cheers,
Iain

 

[Douglas J Steele]

Yes, I am. The Value property of a multiselect list box is always Null,
and setting the list box to Null has no effect on the selected items. I
tested to confirm this, using Access 2002. I assume you tested also;
does Access 2003 behave differently?

I'll have to double-check when I get home, but I'm pretty sure Access 2003
accepts Nulls (it better, or I have to do a quick rewrite of part of my
March column!)

I know that Access 97 works with setting it to Null: I tested yet again
before posting this.

 

[Douglas J Steele]

For those of you who are following this discussion between Dirk and myself,
more research has been done, and it turns out we're both right.

If the MultiSelect property is set to None or Extended, setting the list box
to Null will deselect all selected rows. If it's set to Simple, it won't:

Me.MyListBox = Null

If the MultiSelect property is set to Simple or Extended, resetting the list
box's RowSource will deselect all selected rows. If it's set to None, it
won't:

Me.MyListBox.RowSource = Me.MyListBox.RowSource

Regardless of what the MultiSelect property is set to, looping through the
ItemsSelected collection and setting the Selected property will deselect all
selected rows:

Dim varSelected As Variant

For Each varSelected In Me.MyListBox.ItemsSelected
Me.MyListBox.Selected(varSelected) = False
Next varSelected

You could, of course, loop through all rows in the list box and set the
Selected property to False, but that would be less efficient:

Dim intLoop As Integer

For intLoop = 0 To (Me.MyListBox.Listcount - 1)
Me.MyListBox.Selected(intLoop) = False
Next intLoop

The behaviour is the same in Access 97, Access 2000, Access 2002 and Access
2003.

 

[Fred Boer]

For those of you who are following this discussion between Dirk and

myself,
more research has been done, and it turns out we're both right.

Aw, nuts! And I was so looking forward to watching a no-holds-barred death
match between the two of you...

Cheers!
Fred

 

[Dirk Goldgar]

For those of you who are following this discussion between Dirk and
myself, more research has been done, and it turns out we're both
right.

Oh, good!

If the MultiSelect property is set to None or Extended, setting the
list box to Null will deselect all selected rows. If it's set to
Simple, it won't:

Me.MyListBox = Null

Interesting. It would never have occurred to me that Extended
MultiSelect would work differently than Simple in this regard, and I
never ran across it because I *hate* Extended MultiSelect.

If the MultiSelect property is set to Simple or Extended, resetting
the list box's RowSource will deselect all selected rows. If it's set
to None, it won't:

Me.MyListBox.RowSource = Me.MyListBox.RowSource

Interesting that resetting the RowSource does that, when just requerying
the list box doesn't. I'd guessed that it was the forced requery that
cleared the selections, but a quick test shows that isn't the case.

Regardless of what the MultiSelect property is set to, looping
through the ItemsSelected collection and setting the Selected
property will deselect all selected rows:

Dim varSelected As Variant

For Each varSelected In Me.MyListBox.ItemsSelected
Me.MyListBox.Selected(varSelected) = False
Next varSelected

Yet another new thing learned! I thought this was one of those cases
where you have to loop backward through the collection to avoid missing
items as the collection is reordered upon removing an item. That's what
the code I've been using does. But I just checked this out, and it
works fine. Your code is simpler, and I'll use it from now on. Thanks,
Doug.

 

[Douglas J Steele]

Aw, nuts! And I was so looking forward to watching a no-holds-barred death
match between the two of you...

Given Dirk's a champion fencer, I wouldn't like my chances! <g>

 

[Guest]

Glad something good came out of it in the end!

Me? I'm going to quit qhile I'm ahead and it's working!

Cheers guys,
Iain