Hi Experts,
I need your suggestion in updating this code. Currently the code compares and suggests which task can be assigned to which employee by comparing Tasks with Employees till the specific employees available time (emp available) field which is in tasks table but due to needs
that field is moved to emp table and named avlbto.
1) Now how could I perform the same comparison?
2) Apart from that there is one more field that is added as avlblfrom to the emp table i.e emp5 who is available from 9 pm should be assigned for the tasks whose tasktime is after 9 pm in this case "Task13" ,"Task14" etc. but not
for tasks with tasktime earlier than 9 PM. How will I accomplish this? Could you suggest as you always did?
I have attached the db for reference code is attached to dashboard forms open event
I need your suggestion in updating this code. Currently the code compares and suggests which task can be assigned to which employee by comparing Tasks with Employees till the specific employees available time (emp available) field which is in tasks table but due to needs
that field is moved to emp table and named avlbto.
1) Now how could I perform the same comparison?
2) Apart from that there is one more field that is added as avlblfrom to the emp table i.e emp5 who is available from 9 pm should be assigned for the tasks whose tasktime is after 9 pm in this case "Task13" ,"Task14" etc. but not
for tasks with tasktime earlier than 9 PM. How will I accomplish this? Could you suggest as you always did?
I have attached the db for reference code is attached to dashboard forms open event
Code:
Private Sub Form_Open(Cancel As Integer)
Dim rsTask As DAO.Recordset
Dim rsTask2 As DAO.Recordset
Dim rsEmp As DAO.Recordset
Dim bolsecondpass As Boolean
Dim intCount As Integer
Dim intNextCount As Integer
Dim arrEmp() As String
Dim arrAvail() As Date
ReDim arrEmp(0)
ReDim arrAvail(0)
Set rsTask = CurrentDb.OpenRecordset("select * from tasks " & _
"where (taskdate is null) or (taskdate=#" & Format(Date, "mm/dd/yyyy") & "#) " & _
"order by tasktime;")
With rsTask
.MoveFirst
intNextCount = 1
While Not .EOF
If (!employee & "") <> "" And IsNull(![emp available]) = False Then
ReDim Preserve arrEmp(UBound(arrEmp) + 1)
ReDim Preserve arrAvail(UBound(arrAvail) + 1)
arrEmp(UBound(arrEmp)) = !employee
arrAvail(UBound(arrAvail)) = ![emp available]
Else
For intCount = intNextCount To UBound(arrEmp)
If arrAvail(intCount) > !tasktime Then
.Edit
!employee = arrEmp(intCount)
![emp available] = arrAvail(intCount)
.Update
intNextCount = intNextCount + 1
If intNextCount > UBound(arrEmp) Then intNextCount = 1
Exit For
End If
Next
End If
.MoveNext
Wend
End With
Set Me.Queform.Form.Recordset = rsTask
Set rsTask = Nothing
Set rsTask2 = Nothing
Set rsEmp = Nothing
Me.ests_subform.Form.RecordSource = "Select * from [ests]"
Me.Queform.SetFocus
End Sub