Date overlap***UP***

[Jon]

Greeting

I have a form that has 4 fields as follows:
empID
Work Date
No of hours
Group
I have 2 tables:
Employee info which has the following fields:
EmpID
EmpName
Group
HrsScheduled >>>>Default value =12
And it has EmpID as primary key

EmpWorkingHours
Which has the following fields:
empID
Work Date
No of hours



Each employee must not excess 12 hours per day and I would like to create a
list box to shows the number of hours available for this employee and others
within his group to be alternative options for the supervisor to assign the
work to another employee how do not have extra hours.
For example:

Current employee
employeeName work date Hours Group
EmployeeA 07/18/2009 4 A

Once I input above data and employee has more than 12 hours the following
msg appears:

This employee has 16 hours and you can not assign more hours to him, please
see select another employee from the list below


***Here is my question****

List box

EmployeeName Work Date Available hours Group

EmployeeB 07/18/2009 12 A
EmployeeC 07/18/2009 8 A
EmployeeD 07/18/2009 10 A
EmployeeE 07/18/2009 0 A

How to do the list box in order to select another employee ??

 

[Ray]

The following assumes your listbox is called lstEmployeeSelection and your
date selector is called txtWorkDate.

Create a sub as follows:

Private Sub lstEmployeeSelectionRequery

Dim S as string


' This will list all employees, with a field AvailableHours that is 12
minus the number of hours worked
' on the specified date.
S = "SELECT Employee.empID, EmpName, [Work Date], exprHoursWorked, 12 -
exprHoursWorked AS AvailableHours, " & _
"Group FROM Employee " & _
"LEFT JOIN (SELECT empID, SUM([Work Hours] As exprHoursWorked FROM
EmpWorkingHours " & _
"WHERE [Work Date] = '" & txtWorkDate & "' GROUP BY empID) EmpHours "
& _
"ON Employee.EmpId = EmpHours.empID"

lstEmployeeSelection.RowSource = S

End Sub

Then, in the AfterUpdate event of the text box txtWorkDate, call the sub
lstEmployeeSelectionRequery. The result is that
when you change the date in the box, it will update the listbox with the
employees and working hours for the selected date. Of course, you will need
to set some properties for the listbox. Something like this:

lstEmployeeSelection.ColumnCount = 5
lstEmployeeSelection.ColumnWidths = 0;2";1";0";0.5";0.5"

Then, presumably you also have a textbox (txtProposedWorkHours) where the
user enters the number of hours they want
the employee to work, and a button (btnAddWorkRecord) that will create the
new work record. Your OnClick event of
the button could be something like this:

Private Sub btnAddWorkRecord_OnClick

' Make sure there are items in the list
if lstEmployeeSelection.ListCount < 1 Then

msgbox "Nothing in the list!"

' Make sure a selection is made
elseif lstEmployeeSelection.ListIndex < 0 Then

msgbox "You must select an employee!"

' And a number of hours has been entered
elseif txtProposedWorkHours = "" then

msgbox "Enter the number of hours to be worked!"

' if the employee has worked too many hours...
elseif lstEmployeeSelection.Column(lstEmployeeSelection.ListIndex, 4) <
txtProposedWorkHours

msgbox "Employee has already worked " &
lstEmployeeSelection.Column(lstEmployeeSelection.ListIndex, 3) & _
" hours and cannot work " & txtProposedWorkHours & " more."

else

' do whatever to create the new work record

end if

End Sub

In my opinion, a more elaborate solution would be to modify the query for
the listbox to only show those employees who have enough hours available to
work the shift.

Private Sub lstEmployeeSelectionRequery

Dim S as string


' This will list all employees, with a field AvailableHours that is 12
minus the number of hours worked
' on the specified date.
S = "SELECT Employee.empID, EmpName, [Work Date], exprHoursWorked, 12 -
exprHoursWorked AS AvailableHours, " & _
"Group FROM Employee " & _
"LEFT JOIN (SELECT empID, SUM([Work Hours] As exprHoursWorked FROM
EmpWorkingHours " & _
"WHERE [Work Date] = '" & txtWorkDate & "' GROUP BY empID) EmpHours "
& _
"ON Employee.EmpId = EmpHours.empID " & _
"WHERE (12 - exprHoursWorked) > " & txtProposedWorkHours

lstEmployeeSelection.RowSource = S

End Sub

Of course, if you do that, you would want to also call the sub from the
AfterUpdate event of the txtProposedWorkHours text box so that whenever
either the date OR the number of hours changes, the listbox is automatically
updated. Another thing is that you should probably add some validation to
ensure that txtProposedWorkHours has a valid number so it doesn't crash the
query.