Select All

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DS - 16 Jul 2007 01:28 GMT

This works fine if I select one or all, by clicking one by one.
I want that if nothing is selected then that means all are selected,
then the code runs for all records in the listbox.

Dim CashSQL As String
Dim varSelected As Variant
DoCmd.SetWarnings False
For Each varSelected In Me.ListEmp.ItemsSelected
CASHSQL = "UPDATE tblEmployees " & _
"SET tblEmployees.EmpCashedOut = -1 " & _
"WHERE tblEmployees.EmployeeId = " & _
Me.ListEmp.ItemData(varSelected)
DoCmd.RunSQL (CASHSQL)
Next

Any help appreciated,
Thanks,
DS

Reply to this Message

John W. Vinson - 16 Jul 2007 01:54 GMT

>This works fine if I select one or all, by clicking one by one.
>I want that if nothing is selected then that means all are selected,
>then the code runs for all records in the listbox.

Dim CashSQL As String
Dim varSelected As Variant
DoCmd.SetWarnings False
If Me.ListEmp.ItemsSelected.Count = 0 Then
CASHSQL = "UPDATE tblEmployees " & )
"SET tblEmployees.EmpCashedOut = -1"
Else
For Each varSelected In Me.ListEmp.ItemsSelected
CASHSQL = "UPDATE tblEmployees " & _
"SET tblEmployees.EmpCashedOut = -1 " & _
"WHERE tblEmployees.EmployeeId = " & _
Me.ListEmp.ItemData(varSelected)
Next
End If
DoCmd.RunSQL (CASHSQL)

This will update all employees in the entire table - you'll need to add some
criteria (those from the listbox's rowsource) if that's not what you want.

John W. Vinson [MVP]

Reply to this Message

DS - 16 Jul 2007 03:23 GMT

>>This works fine if I select one or all, by clicking one by one.
>>I want that if nothing is selected then that means all are selected,
[quoted text clipped - 20 lines]
>
> John W. Vinson [MVP]

Hi John, Thank You. It does update the entire table....but just how do
I do just the Employees that are in the listbox since I'm not selecting
any employees in the listbox...

Thanks
DS

Reply to this Message

John W. Vinson - 16 Jul 2007 06:55 GMT

>> This will update all employees in the entire table - you'll need to add some
>> criteria (those from the listbox's rowsource) if that's not what you want.
[quoted text clipped - 3 lines]
>I do just the Employees that are in the listbox since I'm not selecting
>any employees in the listbox...

Since I don't know how your listbox limits the employees (presumably a query?)
all I can do is repeat the advice above. Take a look at your listbox's
RowSource query and replicate it in the SQL string of the "null" branch.

John W. Vinson [MVP]

Reply to this Message

DS - 16 Jul 2007 14:06 GMT

>>>This will update all employees in the entire table - you'll need to add some
>>>criteria (those from the listbox's rowsource) if that's not what you want.
[quoted text clipped - 10 lines]
>
> John W. Vinson [MVP]

Thanks John I should have pasted the rowsource code...I tried taking
your advice but alas I am still coming up short on the solution...here
is the code...
Thanks
DS

With Me.ListEmp
.RowSource = "SELECT tblEmployees.EmployeeID, [EmpFirstName] & "" "" &
[EmpLastName] AS EMP, " & _
"tblEmployees.EmpSignedIN, tblEmployees.EmpCashedOut " & _
"FROM tblEmployees " & _
"WHERE (((tblEmployees.EmployeeID) Not In (SELECT tblChecks.ChkServer " & _
"FROM tblChecks INNER JOIN tblEmployees ON tblChecks.ChkServer =
tblEmployees.EmployeeID " & _
"WHERE (((tblChecks.ChkCancelled) = 0) " & _
"And ((tblChecks.ChkPaid) = 0) " & _
"And ((tblEmployees.EmpSignedIN) = -1) " & _
"And ((tblEmployees.EmpCashedOut) = 0)) " & _
"ORDER BY [EmpFirstName] & "" "" & [EmpLastName])) " & _
"AND ((tblEmployees.EmpSignedIN)=-1) " & _
"AND ((tblEmployees.EmpCashedOut)=0));"
.ColumnCount = 4
.ColumnWidths = "0 in;2 in;0 in;0 in"
.Requery
End With

Reply to this Message

John W. Vinson - 17 Jul 2007 01:45 GMT

>>>>This will update all employees in the entire table - you'll need to add some
>>>>criteria (those from the listbox's rowsource) if that's not what you want.
[quoted text clipped - 35 lines]
>.Requery
>End With

That's complex enough that I'd suggest making it a stored Query and using that
query in your code (rather than the tablename).

John W. Vinson [MVP]

Reply to this Message