multiselect listbox

[angie]

i have the following code for my multiselect listbox:

Private Sub VEHICLE_AfterUpdate()
Dim qdfCurr As DAO.QueryDef
Dim strSelected As String
Dim strSQL As String
Dim varSelected As Variant

If Me.VEHICLE.ItemsSelected.Count = 0 Then
' Nothing selected in the listbox: nothing needs to be done
Else
' Loop through all of the selected rows, adding their value to strSelected
' as a comma-separated list
For Each varSelected In Me.VEHICLE.ItemsSelected
strSelected = strSelected & """" & Me.VEHICLE.ItemData(varSelected) &
""", "
Next varSelected
' Remove the extra ", " from the end of strSelected
strSelected = Left(strSelected, Len(strSelected) - 2)
' Build the revised SQL for your query
strSQL = "SELECT itemcode,family " & _
"FROM [APPLICATIONS-TBL] " & _
"WHERE family IN (" & strSelected & ")"
' Update the query to use the new SQL
Set qdfCurr = CurrentDb().QueryDefs("APPLICATIONS")
qdfCurr.SQL = strSQL
qdfCurr.CLOSE
End If
End Sub

i want to replace the "Nothing selected in the listbox: nothing needs to be
done" with "return all the records". how do i have to put it in the code
correctly?

 

[Jim Burke in Novi]

To loop though every item in the list you want something like this:

For row = 1 To VEHICLE.listCount
' in here refer to items as VEHICLE(row) or whatever variable name you
use
for the 'For' loop.
Next row

I think that if you have the listboxes columnheadings property set to true
than the '0' item is the header - that's why I have mine starting at 1 and
going to the listCount. If you don't have headings I think you'd go from 0 to
listCount - 1.

 

[Jim Burke in Novi]

WHoops, left something out. I always refer to items using the column property
- I guess maybe ItemData does the same thing. So you would need
VEHICLE.ItemData(row) or VEHICLE.Column(row)

To loop though every item in the list you want something like this:

For row = 1 To VEHICLE.listCount
' in here refer to items as VEHICLE(row) or whatever variable name you
use
for the 'For' loop.
Next row

I think that if you have the listboxes columnheadings property set to true
than the '0' item is the header - that's why I have mine starting at 1 and
going to the listCount. If you don't have headings I think you'd go from 0 to
listCount - 1.

i have the following code for my multiselect listbox:

Private Sub VEHICLE_AfterUpdate()
Dim qdfCurr As DAO.QueryDef
Dim strSelected As String
Dim strSQL As String
Dim varSelected As Variant

If Me.VEHICLE.ItemsSelected.Count = 0 Then
' Nothing selected in the listbox: nothing needs to be done
Else
' Loop through all of the selected rows, adding their value to strSelected
' as a comma-separated list
For Each varSelected In Me.VEHICLE.ItemsSelected
strSelected = strSelected & """" & Me.VEHICLE.ItemData(varSelected) &
""", "
Next varSelected
' Remove the extra ", " from the end of strSelected
strSelected = Left(strSelected, Len(strSelected) - 2)
' Build the revised SQL for your query
strSQL = "SELECT itemcode,family " & _
"FROM [APPLICATIONS-TBL] " & _
"WHERE family IN (" & strSelected & ")"
' Update the query to use the new SQL
Set qdfCurr = CurrentDb().QueryDefs("APPLICATIONS")
qdfCurr.SQL = strSQL
qdfCurr.CLOSE
End If
End Sub

i want to replace the "Nothing selected in the listbox: nothing needs to be
done" with "return all the records". how do i have to put it in the code
correctly?

 

[John Spencer]

Private Sub VEHICLE_AfterUpdate()
Dim qdfCurr As DAO.QueryDef
Dim strSelected As String
Dim strSQL As String
Dim varSelected As Variant

If Me.VEHICLE.ItemsSelected.Count = 0 Then
'Add the following
strSQL = "SELECT itemcode,family " & _
"FROM [APPLICATIONS-TBL] "

Else
' Loop through all of the selected rows,
' adding their value to strSelected
' as a comma-separated list
For Each varSelected In Me.VEHICLE.ItemsSelected
strSelected = strSelected & """" & _
Me.VEHICLE.ItemData(varSelected) & _
""", "
Next varSelected

' Remove the extra ", " from the end of strSelected
strSelected = Left(strSelected, Len(strSelected) - 2)

' Build the SQL for your query
strSQL = "SELECT itemcode,family " & _
"FROM [APPLICATIONS-TBL] " & _
"WHERE family IN (" & strSelected & ")"
End If

' Update the query to use the new SQL
Set qdfCurr = CurrentDb().QueryDefs("APPLICATIONS")
qdfCurr.SQL = strSQL
qdfCurr.CLOSE

End Sub

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================