Assistance needed in finding the correct order of the recordset (1 Viewer)

Voyager

Registered User.
Local time
Today, 07:29
Joined
Sep 7, 2017
Messages
95
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)



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

Well-known member
Local time
Yesterday, 21:59
Joined
Apr 9, 2015
Messages
4,339
you don't need code to update, that is what an update query is for. (and faster).
sort the data in the query.
 

Voyager

Registered User.
Local time
Today, 07:29
Joined
Sep 7, 2017
Messages
95
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

AWF VIP
Local time
Yesterday, 18:59
Joined
Apr 13, 2010
Messages
1,401
Hi Voyager,
If I understand you correctly you need to reset your rsemp recordset after each rsTask.Update
Maybe something like this:
Code:
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

Registered User.
Local time
Today, 07:29
Joined
Sep 7, 2017
Messages
95
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

AWF VIP
Local time
Yesterday, 18:59
Joined
Apr 13, 2010
Messages
1,401
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

Registered User.
Local time
Today, 07:29
Joined
Sep 7, 2017
Messages
95
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

AWF VIP
Local time
Yesterday, 18:59
Joined
Apr 13, 2010
Messages
1,401
Ok, but why skip it then in the second loop?
 

Voyager

Registered User.
Local time
Today, 07:29
Joined
Sep 7, 2017
Messages
95
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

AWF VIP
Local time
Yesterday, 18:59
Joined
Apr 13, 2010
Messages
1,401
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

Registered User.
Local time
Today, 07:29
Joined
Sep 7, 2017
Messages
95
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

AWF VIP
Local time
Yesterday, 18:59
Joined
Apr 13, 2010
Messages
1,401
No, it will be the first available employee.
 

Voyager

Registered User.
Local time
Today, 07:29
Joined
Sep 7, 2017
Messages
95
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

AWF VIP
Local time
Yesterday, 18:59
Joined
Apr 13, 2010
Messages
1,401
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

Immoderate Moderator
Staff member
Local time
Yesterday, 20:59
Joined
Feb 28, 2001
Messages
27,001
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:

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

AWF VIP
Local time
Yesterday, 18:59
Joined
Apr 13, 2010
Messages
1,401
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

Registered User.
Local time
Today, 07:29
Joined
Sep 7, 2017
Messages
95
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

AWF VIP
Local time
Yesterday, 18:59
Joined
Apr 13, 2010
Messages
1,401
Glad to hear you got it working!

Cheers,
Vlad
 

Users who are viewing this thread

Top Bottom