Selecting List items in Multiselect list box

  • Thread starter Thread starter BobRoyAce
  • Start date Start date
B

BobRoyAce

I have a table of categories where I pull a category name (RS.Fields(0)) and
a field that indicates whether that category should be included
RS.Fields(1). Then, I try to populate a list box, who's Multiselect property
is set to Extended, placing all categories into it and selecting the ones
for which the Include field value is TRUE. When the code shown below is run,
the list box is populated with all categories, but only the last one is
actually selected (even though, in this case, all of them should be
selected). What am I doing wrong here?

--- CODE BEGINS ---

iCategoryCount = 0

DoEvents
If Not RS.EOF Then
lstCategories.RowSource = """" & RS.Fields(0) & """"
lstCategories.Selected(iCategoryCount) = RS.Fields(1)

iCategoryCount = 1
RS.MoveNext
End If

While Not RS.EOF
lstCategories.RowSource = lstCategories.RowSource & ";""" &
RS.Fields(0) & """"
lstCategories.Selected(iCategoryCount) = RS.Fields(1)

iCategoryCount = iCategoryCount + 1

RS.MoveNext
Wend
DoEvents

--- CODE ENDS ---
 
BobRoyAce said:
I have a table of categories where I pull a category name
(RS.Fields(0)) and a field that indicates whether that category
should be included RS.Fields(1). Then, I try to populate a list box,
who's Multiselect property is set to Extended, placing all categories
into it and selecting the ones for which the Include field value is
TRUE. When the code shown below is run, the list box is populated
with all categories, but only the last one is actually selected (even
though, in this case, all of them should be selected). What am I
doing wrong here?

--- CODE BEGINS ---

iCategoryCount = 0

DoEvents
If Not RS.EOF Then
lstCategories.RowSource = """" & RS.Fields(0) & """"
lstCategories.Selected(iCategoryCount) = RS.Fields(1)

iCategoryCount = 1
RS.MoveNext
End If

While Not RS.EOF
lstCategories.RowSource = lstCategories.RowSource & ";""" &
RS.Fields(0) & """"
lstCategories.Selected(iCategoryCount) = RS.Fields(1)

iCategoryCount = iCategoryCount + 1

RS.MoveNext
Wend
DoEvents

--- CODE ENDS ---
Click to expand...

I think that each time you reset the rowsource (which you do for each
record in the recordset) all the selections are reset. You could check
this by stepping through the code and observing what happens to the list
box. If so, you could fix it by looping once through the recordset to
set the rowsource, and then going back to the top of the recordset and
looping through again and setting the Selected property (without
resetting the rowsource).

However, I don't think I would do it that way. Why don't you just set
the list box's rowsource once, to a query that selects the
[CategoryName] and [Included] field from the source table? The list box
would have to have its ColumnCount property set to 2, but the second
column would have its width set to 0. Then loop through the items in
the list box and select those for which the second column --
Column(1) -- is True? It might look something like this:

'---- start of example "air code" -----
Dim lngRow As Long

With Me.lstCategories

.RowSource = _
"SELECT CategoryName, Included FROM Categories " & _
"ORDER BY CategoryName"

For lngRow = 0 To (.ListCount - 1)

If CBool(.Column(1, lngRow)) = True Then
.Selected(lngRow) = True
End If

Next lngRow

End With
'---- end of example "air code" -----
 
That was it...should have thought of that. Thanks also for the query
Rowsource suggestion...

Dirk Goldgar said:
BobRoyAce said:
I have a table of categories where I pull a category name
(RS.Fields(0)) and a field that indicates whether that category
should be included RS.Fields(1). Then, I try to populate a list box,
who's Multiselect property is set to Extended, placing all categories
into it and selecting the ones for which the Include field value is
TRUE. When the code shown below is run, the list box is populated
with all categories, but only the last one is actually selected (even
though, in this case, all of them should be selected). What am I
doing wrong here?

--- CODE BEGINS ---

iCategoryCount = 0

DoEvents
If Not RS.EOF Then
lstCategories.RowSource = """" & RS.Fields(0) & """"
lstCategories.Selected(iCategoryCount) = RS.Fields(1)

iCategoryCount = 1
RS.MoveNext
End If

While Not RS.EOF
lstCategories.RowSource = lstCategories.RowSource & ";""" &
RS.Fields(0) & """"
lstCategories.Selected(iCategoryCount) = RS.Fields(1)

iCategoryCount = iCategoryCount + 1

RS.MoveNext
Wend
DoEvents

--- CODE ENDS ---
Click to expand...

I think that each time you reset the rowsource (which you do for each
record in the recordset) all the selections are reset. You could check
this by stepping through the code and observing what happens to the list
box. If so, you could fix it by looping once through the recordset to
set the rowsource, and then going back to the top of the recordset and
looping through again and setting the Selected property (without
resetting the rowsource).

However, I don't think I would do it that way. Why don't you just set
the list box's rowsource once, to a query that selects the
[CategoryName] and [Included] field from the source table? The list box
would have to have its ColumnCount property set to 2, but the second
column would have its width set to 0. Then loop through the items in
the list box and select those for which the second column --
Column(1) -- is True? It might look something like this:

'---- start of example "air code" -----
Dim lngRow As Long

With Me.lstCategories

.RowSource = _
"SELECT CategoryName, Included FROM Categories " & _
"ORDER BY CategoryName"

For lngRow = 0 To (.ListCount - 1)

If CBool(.Column(1, lngRow)) = True Then
.Selected(lngRow) = True
End If

Next lngRow

End With
'---- end of example "air code" -----

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

(please reply to the newsgroup)
Click to expand...
 
Back
Top