Assistance needed in loop (1 Viewer)

Voyager

Registered User.
Local time
Today, 22:28
Joined
Sep 7, 2017
Messages
95
Hi experts,
I want your assistance in getting the loop correct.
"Taskpick" field in "tasks" table should be in between lfrm and lto fields of "ests" table
1) If the said condition is "not met" then vba should look for next record is ests table till this condition is met or till the end of ests table
how could I achieve it . I have upated question mark in the respective step

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

    With rsTask
            .MoveFirst
        Do While Not rsTask.EOF
            Set rsemp = CurrentDb.OpenRecordset("SELECT * FROM ests ORDER BY FreeAt", dbOpenSnapshot)
                rsemp.MoveFirst
                maxtme = DMax("[freeat]", "ests", "[Ename]='" & rsemp!ename & "'")
                Do While Not rsemp.EOF
                 If rsTask!taskpick >= rsemp!lfrm And rsTask!taskpick <= rsemp!lto Then
                     .Edit
                        !employee = rsemp!ename
                        ![freeat] = DateAdd("n", Nz(rsTask![tasktime]) * 60, maxtme)
                        .Update
                        Else
		[B][COLOR="Red"]???[/COLOR][/B]
                       
        
                 End If
                Loop
    

            rsTask.MoveNext
            rsemp.MoveNext
            If rsemp.EOF Then
            rsemp.MoveFirst
                        
                    End If
                    Loop
    End With
 
Last edited:

bastanu

AWF VIP
Local time
Today, 09:58
Joined
Apr 13, 2010
Messages
1,401
Care to explain in a few more words what are you trying to achieve? Is this a variation of your earlier post? The code you have right now should do exactly what you describe but once you have a match you should move to the next task:
Code:
Set rsTask = CurrentDb.OpenRecordset("select * from tasks where employee is null order by taskpick;")

    With rsTask
            .MoveFirst
        Do While Not rsTask.EOF
            Set rsemp = CurrentDb.OpenRecordset("SELECT * FROM ests ORDER BY FreeAt", dbOpenSnapshot)
                rsemp.MoveFirst
                maxtme = DMax("[freeat]", "ests", "[Ename]='" & rsemp!ename & "'")
                Do While Not rsemp.EOF
                 If rsTask!taskpick >= rsemp!lfrm And rsTask!taskpick <= rsemp!lto Then
                     .Edit
                        !employee = rsemp!ename
                        ![freeat] = DateAdd("n", Nz(rsTask![tasktime]) * 60, maxtme)
                        .Update
                        GoTo NEXT_TASK
                        Else
		???
                        End If
                     rsemp.MoveNext
                 End If
                Loop
    
NEXT_TASK:
            rsTask.MoveNext
            'rsemp.MoveNext
           ' If rsemp.EOF Then
            'rsemp.MoveFirst
                        
                    End If
                    Loop
    End With

Cheers,
Vlad
 

Voyager

Registered User.
Local time
Today, 22:28
Joined
Sep 7, 2017
Messages
95
Hi Vlad,
Thanks for your constant support. Yes it is an variation of earlier post. I see your code meets my need. Just one more thing
1) Before going to the next_task I wanted to loop through all recordsets in the "rsemp" with the specific record in the rstask to identify if any recordset matches if none is matching then we can go to next_task

I mean in the ??? step am I supposed to use
Code:
rsemp.movenext
and again it has to check

Code:
If rsTask!taskpick >= rsemp!lfrm And rsTask!taskpick <= rsemp!lto Then

Code:
Else
		???
                        End If
                     rsemp.MoveNext
                 End If
                Loop
 

bastanu

AWF VIP
Local time
Today, 09:58
Joined
Apr 13, 2010
Messages
1,401
You don't need the Else; if there is a match you update it and go to the next task, if not you exit the If statement and your code already goes to rsemp.movenext.

Cheers,
Vlad
 

Users who are viewing this thread

Top Bottom