Assistance needed in finding the correct order of the recordset

[Voyager]

Hi all,
I am using the given code to update a set of data which partially meets my need.
1) I want rsemp recordset to be sorted based on "freeat" as soon as update event occurs
2) e.g. if first employee is free before the second employee the next loop should show first employee's name for the next task rather than showing second employee's name ( Which is currently happening is there a way to achieve this order)

 Set rsTask = CurrentDb.OpenRecordset("select * from tasks where employee is null order by taskpick;")

    With rsTask
            .MoveFirst
            Set rsemp = CurrentDb.OpenRecordset("select ests.* from ests order by freeat", dbOpenSnapshot)
    
        rsemp.MoveFirst
       
       Do While Not rsTask.EOF
                     .Edit
                        !employee = rsemp!ename
                        ![avlto] = rsemp![avlto]
                        ![freeat] = DateAdd("n", Nz(rsTask![tasktime]) * 60, DMax("[freeat]", "ests", "[Ename]='" & rsemp!ename & "'"))
                        .Update

                        rsTask.MoveNext
                        rsemp.MoveNext

                        If rsemp.EOF Then
                        rsemp.MoveFirst
                          bolsecondpass = True
                      
                    End If
                    Loop
    End With

 

[Ranman256]

you don't need code to update, that is what an update query is for. (and faster).
sort the data in the query.

 

[Voyager]

Hi Ranman,
Thank you. But I am not very strong in creating a query which could meet this requirement
Is there a way in vba itself?

 

[bastanu]

Hi Voyager,
If I understand you correctly you need to reset your rsemp recordset after each rsTask.Update
Maybe something like this:

Set rsTask = CurrentDb.OpenRecordset("select * from tasks where employee is null order by taskpick;")

    With rsTask
            .MoveFirst
            'Set rsemp = CurrentDb.OpenRecordset("select ests.* from ests order by freeat", dbOpenSnapshot)    
        'rsemp.MoveFirst
       
       Do While Not rsTask.EOF
            Set rsemp = CurrentDb.OpenRecordset("select ests.* from ests order by freeat", dbOpenSnapshot)
             rsemp.MoveFirst
                     .Edit
                        !employee = rsemp!ename
                        ![avlto] = rsemp![avlto]
                        ![freeat] = DateAdd("n", Nz(rsTask![tasktime]) * 60, DMax("[freeat]", "ests", "[Ename]='" & rsemp!ename & "'"))
                        .Update

                        rsTask.MoveNext
                        'rsemp.MoveNext vlad - need to reset it to apply the order by
                       

                       ' If rsemp.EOF Then
                       ' rsemp.MoveFirst
                         ' bolsecondpass = True
                      
                    'End If
                    Loop
    End With

Cheers,
Vlad

 

[Voyager]

Hi Vlad,
Thanks it’s very close to what I want.
If it’s the first loop I want the first record of rsemp recordset for the second loop i want to move to second recordse for third loop third recordset of rsemp. How to achieve it.

 

[bastanu]

But after the update takes place the first emp freeat gets increased ( ![freeat] = DateAdd("n", Nz(rsTask![tasktime]) * 60, DMax("[freeat]", "ests", "[Ename]='" & rsemp!ename & "'"))) so the second emp record would now become first when the rsemp gets reset. Isn't that the case?

Cheers,
Vlad

 

[Voyager]

Initially I had the same opinion which you had. But that is not the case.
Let me explain with an example of two employees, if the first employee is given a task worth 1 hour and second employee with task worth 5 hours the first employee will be free well before second emp. So first employees name should be there in second recordset too.

 

[bastanu]

Ok, but why skip it then in the second loop?

 

[Voyager]

I think we need that functionality to achieve the correct order of delegating tasks. Since when some 50 plus employees are there one person could have taken multiple short tasks and get free well before others so this persons name should repeatedly come in the loop than with others who has larger tasks. Can we use any counter functionality or bookmark functionality. Bookmark disappears after requery and not sure about counter.

 

[bastanu]

But according to your logic even if they come back into the loop they will get skipped in the second loop (you want to move to the second employee) and yet again in the third one and so on. I think the best approach is to always reset the rsemp like in my example so the each task gets assigned to the first available employee.

 

[Voyager]

A doubt there. Since we are ordering the recordset by freeat and using .movefirst isn’t the same record or the same employee will come first for every loop.

 

[bastanu]

No, it will be the first available employee.

 

[Voyager]

You are correct. However in the second loop I want either second employee or first employees second pickup time whichever is less. So in the second loop it should show us the employee listed in the second recordset in the third loop person in the third recordset and so on. For achieving this I want to move the recordset by one in each loop. Is my logic correct.

 

[bastanu]

Unless you have some fair workplace rules that you haven't discussed your logic doesn't really work. Forget about the order in the first loop. If the second employee' s freeat time would be less than the first one the second will be listed first when you reset the recordset ordered by freeat. So basically you need to loop through all the tasks and assign each to the first available employee, regardless what position he or she occupied in any previous loop.
Cheers,
Vlad

 

[The_Doc_Man]

Write a query similar in concept to this and call it BatterUp:

SELECT TOP 1 EName FROM ests ORDER BY FreeAt ;

Then use DLookup( "[EName]", "BatterUp" ) to see who is at the top of the list right now.
When you do that, you can ALSO build a dynamic SQL and execute it:

Dim MaxD as Date
Dim TopE as String
Dim DynSQL as String
...

TopE = DLookup( "[ests]", "BatterUp" )
MaxD = DMax( "[freeat]", "ests", "[Ename] = '" & TopE & "'" )
DynSQL = "UPDATE ests SET [freeat] = DateAdd(""n"", Nz(rsTask![tasktime]) * 60, MaxD)"
DoCmd.RunSQL DynSQL

 

[bastanu]

Sorry doc_man, but wouldn't that do the same thing as my code? And you would still have to wrapped a loop around it to go through all the tasks to be assigned, isn't that so?

Cheers,
Vlad

 

[Voyager]

Hi Bastanu / Doc Man,
Thanks for your immediate response yes both of your code works.Ofcourse I did make certain changes to meet my need...
Thanks for your timely help.

 

[bastanu]

Glad to hear you got it working!

Cheers,
Vlad