Assistance needed in loop

[Voyager]

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

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

 

[bastanu]

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:

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]

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

rsemp.movenext

and again it has to check

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

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

 

[bastanu]

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