Assistance needed in allocating tasks (1 Viewer)

[Voyager]

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:

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):

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

 

[arnelgp]

why don't you need task2 table.
in my view this is the Template of all tasks.
it is in this table where you want to increase the task or decrease it.
it's the standard.

rename it to something, like taskTemplate, etc.

 

[Voyager]

Hi Sir,
The tasks will automatically get downloaded to the "Tasks" table so is there a way to use it as template instead of "task2" because we are using the Tasks table as primary table .
1)So instead of adding an entry to tasks table can we just edit and add the emp name
2)One more thing can we stop such repetition of emp name till his available time for eg emp1 name can be repeated till Task time is less than 10:00 since he is available till 10:00

I have attached the db for reference

 

[arnelgp]

here try this if this is what you want.

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 & "") <> "" Then
                ReDim Preserve arrEmp(UBound(arrEmp) + 1)
                ReDim Preserve arrAvail(UBound(arrAvail) + 1)
                arrEmp(UBound(arrEmp)) = !employee
                arrAvail(UBound(arrAvail)) = ![Emp Available]
            Else
                If Trim(!employee.Value & "") = "" Then
                    For intCount = intNextCount To UBound(arrEmp)
                        If arrAvail(intCount) > !tasktime Then
                            .Edit
                            !employee = arrEmp(intCount)
                            !taskdate = Date
                            .Update
                            intNextCount = intNextCount + 1
                            If intNextCount > UBound(arrEmp) Then intNextCount = 1
                            Exit For
                        End If
                    Next
                End If
            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 employee as ename,tasktime from tasks " & _
        "where taskdate=#" & Format(Date, "mm/dd/yyyy") & "# order by tasktime"
    Me.Queform.SetFocus
End Sub

 

[Voyager]

Hi Sir,
This code works like a breeze and this is what I wanted. One small problem when I close the form and reopen it I am getting " Run time error 94 Invalid use of Null"

I understood the line

arrAvail(UBound(arrAvail)) = ![Emp Available]

Is not updating the time , I tried adding # to the code but it is not working too

 

[arnelgp]

change this portion to:

    With rsTask
        .MoveFirst
        intNextCount = 1
        While Not .EOF
            If (!employee & "") <> "" And IsNull(![emp Available]) = False Then

 

[Voyager]

Sir,,
Almost everything is fine. I am able to open the form repeatedly now.
But Availability time is not getting updated for the succeeding set of employees.. I mean If the availability time is also shown next to the Emp name it will be good.

 

[arnelgp]

update this one to

    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)
                        !taskdate = Date
                        ![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

 

[Voyager]

Hi Sir,
Resolved, I owe you a ton. I really could not say in words how many employees hours of worth work you have saved by giving this code.
Thank you thank you thank you.

 

[arnelgp]

cheers my friend!