Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-13-2019, 10:34 PM   #1
Voyager
Newly Registered User
 
Join Date: Sep 2017
Posts: 76
Thanks: 67
Thanked 0 Times in 0 Posts
Voyager is on a distinguished road
Assistance needed in loop

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
		???
                       
        
                 End If
                Loop
    

            rsTask.MoveNext
            rsemp.MoveNext
            If rsemp.EOF Then
            rsemp.MoveFirst
                        
                    End If
                    Loop
    End With


Last edited by Voyager; 02-13-2019 at 10:47 PM.
Voyager is offline   Reply With Quote
Old 02-13-2019, 10:52 PM   #2
bastanu
Registered User
 
Join Date: Apr 2010
Location: Kelowna, Canada
Posts: 225
Thanks: 1
Thanked 62 Times in 62 Posts
bastanu is on a distinguished road
Re: Assistance needed in loop

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
bastanu is offline   Reply With Quote
The Following User Says Thank You to bastanu For This Useful Post:
Voyager (02-13-2019)
Old 02-13-2019, 11:00 PM   #3
Voyager
Newly Registered User
 
Join Date: Sep 2017
Posts: 76
Thanks: 67
Thanked 0 Times in 0 Posts
Voyager is on a distinguished road
Re: Assistance needed in loop

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

Voyager is offline   Reply With Quote
Old 02-13-2019, 11:15 PM   #4
bastanu
Registered User
 
Join Date: Apr 2010
Location: Kelowna, Canada
Posts: 225
Thanks: 1
Thanked 62 Times in 62 Posts
bastanu is on a distinguished road
Re: Assistance needed in loop

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

bastanu is offline   Reply With Quote
The Following User Says Thank You to bastanu For This Useful Post:
Voyager (02-13-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
assistance needed daryln1243 Forms 5 05-08-2017 07:00 AM
Form Assistance Needed jaxmark Forms 3 03-17-2013 02:34 PM
VBA Assistance Needed Please graviz Modules & VBA 4 12-21-2009 12:19 PM
a little assistance needed soundsfishy Forms 12 06-25-2003 10:57 PM




All times are GMT -8. The time now is 05:53 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World