Hi Experts,
I need your support in fixing a code. My requirement is to add names of employees from "emp" table to "Tasks" table where employee name is not available (is null) and the name repetion should happen if the availability time of
employee is less than task time. Emp1 who will be free first at 1:00 should pick the first pending task "Task5"
One of the expert gave the below code and it was working fine with "tasks2" but due to various reason I am supposed to remove the table and I tried making some changes to the current code ( second code ) to match but its not working.
Could you assist.
Original code:
New code (Not working):
I need your support in fixing a code. My requirement is to add names of employees from "emp" table to "Tasks" table where employee name is not available (is null) and the name repetion should happen if the availability time of
employee is less than task time. Emp1 who will be free first at 1:00 should pick the first pending task "Task5"
One of the expert gave the below code and it was working fine with "tasks2" but due to various reason I am supposed to remove the table and I tried making some changes to the current code ( second code ) to match but its not working.
Could you assist.
Original code:
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
Set rsTask = CurrentDb.OpenRecordset("select * from tasks where taskdate=#" & Format(Date, "mm/dd/yyyy") & "#")
With rsTask
If (.BOF And .EOF) Then
Set rsTask2 = CurrentDb.OpenRecordset("select task2.* from task2 order by id", dbOpenSnapshot)
Set rsEmp = CurrentDb.OpenRecordset("select emp.* from emp order by id", dbOpenSnapshot)
rsTask2.MoveFirst
rsEmp.MoveFirst
Do While Not rsTask2.EOF
.AddNew
!taskid = rsTask2!taskid
!taskdate = Date
!tasktime = rsTask2!tasktime
If bolsecondpass Then
!status = "Pending"
Else
!status = "In Progress"
End If
!employee = rsEmp!ename
.Update
rsTask2.MoveNext
rsEmp.MoveNext
If rsEmp.EOF Then
rsEmp.MoveFirst
bolsecondpass = True
End If
Loop
End If
End With
Set Me.Queform.Form.Recordset = rsTask
Set rsTask = Nothing
Set rsTask2 = Nothing
Set rsEmp = Nothing
Me.ests_subform.Form.RecordSource = "select employee as ename,tasktime from tasks " & _
"where taskdate=#" & Format(Date, "mm/dd/yyyy") & "# order by id"
Me.Queform.SetFocus
End Sub
New code (Not working):
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
Set rsTask = CurrentDb.OpenRecordset("select * from tasks where taskdate=#" & Format(Date, "mm/dd/yyyy") & "#")
With rsTask
If (.BOF And .EOF) Then
[COLOR="red"]Set rsTask2 = CurrentDb.OpenRecordset("select tasks.* from tasks order by id", dbOpenSnapshot)
[/COLOR]Set rsEmp = CurrentDb.OpenRecordset("select emp.* from emp order by id", dbOpenSnapshot)
rsTask2.MoveFirst
rsEmp.MoveFirst
Do While Not rsTask2.EOF
[COLOR="Red"] .edit[/COLOR]
!taskid = rsTask2!taskid
!taskdate = Date
!tasktime = rsTask2!tasktime
If bolsecondpass Then
!status = "Pending"
Else
!status = "In Progress"
End If
!employee = rsEmp!ename
.Update
rsTask2.MoveNext
rsEmp.MoveNext
If rsEmp.EOF Then
rsEmp.MoveFirst
bolsecondpass = True
End If
Loop
End If
End With
Set Me.Queform.Form.Recordset = rsTask
Set rsTask = Nothing
Set rsTask2 = Nothing
Set rsEmp = Nothing
Me.ests_subform.Form.RecordSource = "select employee as ename,tasktime from tasks " & _
"where taskdate=#" & Format(Date, "mm/dd/yyyy") & "# order by id"
Me.Queform.SetFocus
End Sub