Multi Select Listbox

[DS]

I have a multiselect listbox. What I would like to do is when I click
on a command button I want the selected items in the listbox to change
value. The value I need to change is a yes/no field. I nwould like to
change it from 0 to -1 or visa versa. Any help appreciated.
Thanks
DS

 

[Ron Weiner]

I do not understand exactly what you want to do but here is an outline in
VBA to get you started.

For i = 0 To YourListBox.ListCount - 1
If YourListBox.Selected(i) Then
' This is where you put the code to change
' what ever you want to change when a
' List Item from YourListBox is selected
End If
Next

Hope this helps.

 

[DS]

I do not understand exactly what you want to do but here is an outline in
VBA to get you started.

For i = 0 To YourListBox.ListCount - 1
If YourListBox.Selected(i) Then
' This is where you put the code to change
' what ever you want to change when a
' List Item from YourListBox is selected
End If
Next

Hope this helps.

Thanks, I basically want to change whatever I select in the multi-select
listbox to -1. I'll give it a try and let you know how it turns out.
Thanks
DS

 

[DS]

Thanks, I basically want to change whatever I select in the multi-select
listbox to -1. I'll give it a try and let you know how it turns out.
Thanks
DS

OK this is where I am. It kinda works. The problem is that it changes
the CashedOut field to -1 regardless if the item is selected or not.
Thanks
DS
Private Sub CommandCashOut_Click()
Dim I As Integer
For I = 0 To Me.ListEmp.ListCount - 1
If ListEmp.Selected(I) Then
Dim CashSQL As String
DoCmd.SetWarnings False
CashSQL = "UPDATE Employees SET Employees.CashedOut = -1"
DoCmd.RunSQL (CashSQL)
DoCmd.SetWarnings True
End If
Next
End Sub

 

[Douglas J. Steele]

OK this is where I am. It kinda works. The problem is that it changes
the CashedOut field to -1 regardless if the item is selected or not.
Thanks
DS
Private Sub CommandCashOut_Click()
Dim I As Integer
For I = 0 To Me.ListEmp.ListCount - 1
If ListEmp.Selected(I) Then
Dim CashSQL As String
DoCmd.SetWarnings False
CashSQL = "UPDATE Employees SET Employees.CashedOut = -1"
DoCmd.RunSQL (CashSQL)
DoCmd.SetWarnings True
End If
Next
End Sub

That's because you aren't putting the Employee Id into the SQL.

Assuming that the bound field of your listbox is the Employee Id (and that
the Employee Id is numeric), try something like:

Private Sub CommandCashOut_Click()
Dim CashSQL As String
Dim varSelected As Variant

DoCmd.SetWarnings False
For Each varSelected In Me.ListEmp.ItemsSelected
CashSQL = "UPDATE Employees " & _
"SET Employees.CashedOut = -1 " & _
"WHERE Employees.Id = " & _
Me.ListEmp.ItemData(varSelected)
DoCmd.RunSQL (CashSQL)
Next
DoCmd.SetWarnings True

End Sub

 

[Ron Weiner]

Your Sql Statement need to add a WHERE condition to limit the rows it is
going to set to -1. Something like

CashSQL = "UPDATE Employees SET Employees.CashedOut = -1 " _
& "WHERE Employees.SomeColumn = SomeValueFromThisRowInTheListBox"

 

[DS]

That's because you aren't putting the Employee Id into the SQL.

Assuming that the bound field of your listbox is the Employee Id (and that
the Employee Id is numeric), try something like:

Private Sub CommandCashOut_Click()
Dim CashSQL As String
Dim varSelected As Variant

DoCmd.SetWarnings False
For Each varSelected In Me.ListEmp.ItemsSelected
CashSQL = "UPDATE Employees " & _
"SET Employees.CashedOut = -1 " & _
"WHERE Employees.Id = " & _
Me.ListEmp.ItemData(varSelected)
DoCmd.RunSQL (CashSQL)
Next
DoCmd.SetWarnings True

End Sub

That worked! Thank you very much!
DS

 

[DS]

Your Sql Statement need to add a WHERE condition to limit the rows it is
going to set to -1. Something like

CashSQL = "UPDATE Employees SET Employees.CashedOut = -1 " _
& "WHERE Employees.SomeColumn = SomeValueFromThisRowInTheListBox"

Thanks, Rob. This works.

Private Sub CommandCash_Click()
Dim CashSQL As String
Dim varSelected As Variant

DoCmd.SetWarnings False
For Each varSelected In Me.ListEmp.ItemsSelected
CashSQL = "UPDATE Employees " & _
"SET Employees.CashedOut = -1 " & _
"WHERE Employees.EmployeeId = " & _
Me.ListEmp.ItemData(varSelected)
DoCmd.RunSQL (CashSQL)
Next
DoCmd.SetWarnings True
End Sub