List box selections

  • Thread starter Thread starter Martin Dashper
  • Start date Start date
M

Martin Dashper

I have a multi-select list box which is populated with items in date
order. I have inserted a blank row when the date column changes.

What I would like to do now is to prevent the blank item from being
selected either as a single selection or as part of a multiple
selection. Any ideas?

Martin Dashper
 
Martin Dashper said:
I have a multi-select list box which is populated with items in date
order. I have inserted a blank row when the date column changes.

What I would like to do now is to prevent the blank item from being
selected either as a single selection or as part of a multiple
selection. Any ideas?
Click to expand...

You could use the AfterUpdate event of the list box to scan through the
ItemsSelected collection and, for any selected item that is blank,
unselect it. Something like this:

Dim vRow As Variant

With Me.lstMyList
For Each vRow In .ItemsSelected
If Len(.ItemData(vRow) & vbNullString) = 0 Then
.Selected(vRow) = False
Exit For
End If
Next vRow
End With
 
You could use the AfterUpdate event of the list box to scan through the
ItemsSelected collection and, for any selected item that is blank,
unselect it. Something like this:

Dim vRow As Variant

With Me.lstMyList
For Each vRow In .ItemsSelected
If Len(.ItemData(vRow) & vbNullString) = 0 Then
.Selected(vRow) = False
Exit For
End If
Next vRow
End With

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Thanks Dirk, that almost works.
Unfortunately, for some reason, Len(.ItemData(vRow) & vbNullString)
evaluates to zero for all rows and hence any row is deselected.

Martin Dashper
 
Martin Dashper said:
Thanks Dirk, that almost works.
Unfortunately, for some reason, Len(.ItemData(vRow) & vbNullString)
evaluates to zero for all rows and hence any row is deselected.
Click to expand...

That's odd. What is the RowSource of the combo box, and what are its
Column Count and Bound Column properties?
 
Martin Dashper said:
Therein lies the problem. The listbox is unbound
Click to expand...

I don't see how that is the problem. If it's a multiselect list box it
*must* be unbound, but it still has Row Source, Column Count, and Bound
Column properties. The Bound Column identifies which column is
considered to hold the "value" of each row, but doesn't require that the
list box itself be bound, or have a Value property.
 
I don't see how that is the problem. If it's a multiselect list box it
*must* be unbound, but it still has Row Source, Column Count, and Bound
Column properties. The Bound Column identifies which column is
considered to hold the "value" of each row, but doesn't require that the
list box itself be bound, or have a Value property.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
OK, whatever row is selected I get the same values.

The RowSource is a very long string consisting of all the data in
every row with column values seperated by semicolons. This includes
the blank rows where there is simply a series of semicolons.
eg.
Fri;27/08/2004;GN2113;10:00;16:00;;;;;;Tue;14/12/2004;GN014;11:30;15:00;Tue;14/12/2004;GN016;12:30;14:00
etc. etc.

The ColumnCount is 5 (correct)
The BoundColumn is 0 (in all cases)

Martin
 
Martin Dashper said:
OK, whatever row is selected I get the same values.

The RowSource is a very long string consisting of all the data in
every row with column values seperated by semicolons. This includes
the blank rows where there is simply a series of semicolons.
eg.
Fri;27/08/2004;GN2113;10:00;16:00;;;;;;Tue;14/12/2004;GN014;11:30;15:00;
Tue;14/12/2004;GN016;12:30;14:00
etc. etc.

The ColumnCount is 5 (correct)
The BoundColumn is 0 (in all cases)
Click to expand...

Are you sayting that the Bound Column property, as displayed on the list
box's property sheet, is 0? That's not a valid value, since -- on the
property sheet, as opposed to VBA code -- the columns are numbered
starting with 1. I just tried this out, using the row source you
provided and the code I suggested, and with the Bound Column property
set to 1 on the property sheet, and it worked fine. I then changed the
Bound Column property to 0, and sure enough ItemData was always Null.
 
Are you sayting that the Bound Column property, as displayed on the list
box's property sheet, is 0? That's not a valid value, since -- on the
property sheet, as opposed to VBA code -- the columns are numbered
starting with 1. I just tried this out, using the row source you
provided and the code I suggested, and with the Bound Column property
set to 1 on the property sheet, and it worked fine. I then changed the
Bound Column property to 0, and sure enough ItemData was always Null.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Yes, that was the case. I have just changed the value to 1 and it
works fine.

Thanks very much

Martin

Martin
 
You could use the AfterUpdate event of the list box to scan through the
ItemsSelected collection and, for any selected item that is blank,
unselect it. Something like this:

Dim vRow As Variant

With Me.lstMyList
For Each vRow In .ItemsSelected
If Len(.ItemData(vRow) & vbNullString) = 0 Then
.Selected(vRow) = False
Exit For
End If
Next vRow
End With

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
It's me again. I wish I could get my head round listboxes.
Anyway, one more question.
How do I get to toggle the row selection on & off? By default,
clicking on a row selects it, but the only way to de-select it is to
click on another row.
I've tried the following:

Dim vRow As Variant

With Me.List
For Each vRow In .ItemsSelected
If .Selected(vRow) = True Then
.Selected(vRow) = False
Exit For
End If
Next vRow
End With

but I can't work out which event to attach it to.
Using BeforeUpdate, AfterUpdate and Click, it just flashes the
selection on then off.

Martin
 
Martin Dashper said:
It's me again. I wish I could get my head round listboxes.
Anyway, one more question.
How do I get to toggle the row selection on & off? By default,
clicking on a row selects it, but the only way to de-select it is to
click on another row.
I've tried the following:

Dim vRow As Variant

With Me.List
For Each vRow In .ItemsSelected
If .Selected(vRow) = True Then
.Selected(vRow) = False
Exit For
End If
Next vRow
End With

but I can't work out which event to attach it to.
Using BeforeUpdate, AfterUpdate and Click, it just flashes the
selection on then off.
Click to expand...

You really should have posted this as a new question, but let me see if
I can work out a solution to your problem.

Earlier you were tasking about multiselect list boxes, but I now you
must be dealing with a "regular", non-multiselect list box, because
multiselect list boxes already behave the way you want. That is,
clicking on a selected row deselects it. But this is not the normal
behavior for a non-multiselect list box.

If the list box is bound, pressing the Esc key will undo the change you
just made to it, but that won't work if it's unbound, and it's not
exactly the behavior you want: "click selected item to deselect it".
You could try code like this in the list box's Click event:

'----- start of proposed code -----
Private Sub lstMyList_Click()

Static vOldValue As Variant

If Me.lstMyList = vOldValue Then
Me.lstMyList = Null
End If

vOldValue = Me.lstMyList

End Sub

'----- end of proposed code -----

Note that this won't work for a multiselect list box, because they don't
have a single value. But the problem doesn't arise for multiselect list
boxes anyway. Note also that, if the list box is bound to a required
field, you'll get an error message when you try to set it to Null. The
only way around that, that I can think of offhand, is to check whether
the control is bound; if it is, use the control's Undo method to undo
the selection rather than setting the list box to Null. That wouldn't
be quite the behavior you want, though, since the list box could be set
to a non-null value that it had before you clicked on it.
 
You really should have posted this as a new question, but let me see if
I can work out a solution to your problem.

Earlier you were tasking about multiselect list boxes, but I now you
must be dealing with a "regular", non-multiselect list box, because
multiselect list boxes already behave the way you want. That is,
clicking on a selected row deselects it. But this is not the normal
behavior for a non-multiselect list box.

If the list box is bound, pressing the Esc key will undo the change you
just made to it, but that won't work if it's unbound, and it's not
exactly the behavior you want: "click selected item to deselect it".
You could try code like this in the list box's Click event:

'----- start of proposed code -----
Private Sub lstMyList_Click()

Static vOldValue As Variant

If Me.lstMyList = vOldValue Then
Me.lstMyList = Null
End If

vOldValue = Me.lstMyList

End Sub

'----- end of proposed code -----

Note that this won't work for a multiselect list box, because they don't
have a single value. But the problem doesn't arise for multiselect list
boxes anyway. Note also that, if the list box is bound to a required
field, you'll get an error message when you try to set it to Null. The
only way around that, that I can think of offhand, is to check whether
the control is bound; if it is, use the control's Undo method to undo
the selection rather than setting the list box to Null. That wouldn't
be quite the behavior you want, though, since the list box could be set
to a non-null value that it had before you clicked on it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Thanks Dirk. Your comments about MultiSelect box behaviour made me
look again at the 'Help' page for the MultiSelect property and, in
fact, Holding down CTRL and clicking an item selects or deselects that
item. That will do me.

Thanks again

Martin
 
Martin Dashper said:
Thanks Dirk. Your comments about MultiSelect box behaviour made me
look again at the 'Help' page for the MultiSelect property and, in
fact, Holding down CTRL and clicking an item selects or deselects that
item. That will do me.
Click to expand...

Ah, there was a third possibility I hadn't even counted upon! It never
occurred to me that you were using Extended Multiselect, and I forgot
that its behavior would also give the result you reported. I believe I
actually led you astray, but you found your way back to the true path by
yourself. Sorry about that, and congratulations!
 
Back
Top