ItemsSelected Collection

[Guest]

I have a listbox called lbSelectList. It has 5 columns. I want to rifle
through the selecteditems collection and use columns 1,2,and 3 in sql
statements (not column 0...). I can't seem to refer to the columns properly.
Can you provide some advice for me? I'd guess it's something like:

dim x as variant
with lbSelectList
for each x in .SelectedItems
debug.print x.column(1) x.column(2) x.column(3)
next
end with

 

[Guest]

Here's the actual code I can't get to work...

With lbSelectList
Dim TableName As String
Dim SheetSpec As String
Dim vIndex As Variant
If .ItemsSelected.Count > 0 Then
For Each vIndex In .ItemsSelected
TableName = .ItemsSelected.Column(3, vIndex)
SheetSpec = .ItemsSelected.Column(1, vIndex) & "!" &
..ItemsSelected.Column(2, vIndex)
DoCmd.RunSQL "Delete * from " & TableName
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9,
TableName, varGetFilename, True, SheetSpec
MsgBox varGetFilename & vbCrLf & "Import Done"
Next
End If
End With

 

[Guest]

Here is a function I use for that. In this case, there is only one column,
but at least it should give you an idea of how it should work.
As to the SQL, you will have to use VBA to create a string to help create
your SQL. SQL cannot see the content of a multi select list box not can it
understand column references to either combo box or list box controls.

 

[Douglas J. Steele]

Very succinct function, Dave! <g>

 

[Guest]

Dave, did you forget to reference the function?

 

[Guest]

Okay, here is the long version

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function