Can't view Listbox selections

[rjchristensen]

Hello all,

I am trying to use the selections from a multi-select listbox to modify selection criteria. When I try to reference either the listbox.column or listbox.itemdata values, the system jumps to the error area (see code). I am at my wits end with this one. Any ideas?

Form Code

If Me!lstBoard.ItemsSelected.Count > 0 Then
If Left(substrSQL, 1) <> "(" And substrSQL <> vbNullString Then
substrSQL = "(" & substrSQL & ")"
End If
BoardList = ListBoxContents(Me!lstBoard)
If numFound = 1 Then
substrSQL = substrSQL & "AND qryDispBoard.[Board] = " & BoardList
Else
substrSQL = substrSQL & "AND (qryDispBoard.[Board] = " & BoardList & ")"
End If
End If


Module Code

Public Function ListBoxContents(LB As ListBox) As String
'Returns extended SQL code if selected entries are found
'Null if none are selected
Dim intCurrentRow As Integer
ListBoxContents = vbNullString
numFound = 0
For intCurrentRow = 0 To LB.ColumnCount - 1
If LB.ItemData(intCurrentRow) Then
numFound = numFound + 1
If numFound > 1 Then
ListBoxContents = ListBoxContents & " OR " & LB.ItemData(intCurrentRow)
Else
ListBoxContents = LB.ItemData(intCurrentRow)
End If
End If
Next intCurrentRow
End Function


When I use the debugger and watch LB, there are no references to Column or ItemData. I tried using both LB.ItemData(intCurrentRow) as seen above as well as LB.Column(0, intCurrentRow).

Neither one works. Any ideas or suggestions would be greatly appreciated.

Thanks,

Rich

[MMcCarthy]

Hi Rich,

Try this ...

Dim valSel As Variant
 
If Me!lstBoard.ItemsSelected.Count > 0 Then
   If Left(substrSQL, 1) <> "(" And substrSQL <> vbNullString Then
      substrSQL = "(" & substrSQL & ")"
   End If
 
   substrSQL = substrSQL & "AND qryDispBoard.[Board] IN ("
   For Each valSel In Me!lstBoard.ItemsSelected
      substrSQL = substrSQL & "'" & Me!lstBoard.ItemData(valSel) & "', "
   Next
 
   substrSQL = Left(substrSQL, Len(substrSQL)-2) & ")"
End If
 

[rjchristensen]

Thanks for the suggestion, but the SQL code works fine. It's the Access part that I am having a problem with. I can NOT see the data in either LB.Column or LB.ItemData - it doesn't exist. Any reference to it errors out. The system knows that items have been selected by looking at Items.Selected.Count. This returns the correct value, but when it comes to retrieveing the actual data, I get nothing but errors.

Thanks,

Rich

Hi Rich,

Try this ...

Dim valSel As Variant
 
If Me!lstBoard.ItemsSelected.Count > 0 Then
   If Left(substrSQL, 1) <> "(" And substrSQL <> vbNullString Then
      substrSQL = "(" & substrSQL & ")"
   End If
 
   substrSQL = substrSQL & "AND qryDispBoard.[Board] IN ("
   For Each valSel In Me!lstBoard.ItemsSelected
      substrSQL = substrSQL & "'" & Me!lstBoard.ItemData(valSel) & "', "
   Next
 
   substrSQL = Left(substrSQL, Len(substrSQL)-2) & ")"
End If
 

[MMcCarthy]

Thanks for the suggestion, but the SQL code works fine. It's the Access part that I am having a problem with. I can NOT see the data in either LB.Column or LB.ItemData - it doesn't exist. Any reference to it errors out. The system knows that items have been selected by looking at Items.Selected.Count. This returns the correct value, but when it comes to retrieveing the actual data, I get nothing but errors.

Thanks,

Rich

Which is why I've given you a For ... Each loop which is how you retrieve the selections for a multi select listbox

[rjchristensen]

Thank You!!!!!! Once I pulled my head out of my a$$ and paid attention to your fine advice, it worked like a charm!

Thanks again!!!

Which is why I've given you a For ... Each loop which is how you retrieve the selections for a multi select listbox

[MMcCarthy]

Thank You!!!!!! Once I pulled my head out of my a$$ and paid attention to your fine advice, it worked like a charm!

Thanks again!!!

You're welcome.

I understand when the advice you get isn't what you expect it can throw you.

Mary