Select All

  • Thread starter Thread starter John W. Vinson
  • Start date Start date
J

John W. Vinson

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.
Click to expand...

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]
 
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
 
John said:
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]
Click to expand...
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
 
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...
Click to expand...

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]
 
John said:
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]
Click to expand...
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
 
John said:
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]
Click to expand...
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
Click to expand...


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]
 
Back
Top