Criteria for early exit of Do Until loop - or would For Next be better? (1 Viewer)

Rx_ · Apr 6, 2016

This question is really about exiting early if one Field receives an "Eliminated" status.
Having started with a Do Until - there doesn't seem to be a way to exit midway in the Field evaluation.

This example only has 2 fields while the real one has many more.
Each field has a different formula for EvaluateRecord. This example criteria is hard-coded for demo. If Field 1 is Eliminate, there is no need to continue with Field 2, 3, ...25. Is there an easy exit criteria for the fields?
Keep in mind, the next Record needs to be evaluated.

Basically, for a proof of concept, each Column is evaluated. If Column 1 receives "eliminated" in the EvaluatedRecord (11) then Exit the loop
but move to the next record and evaluate the columns for that record.
Attached is an example of a Local Table that is populated.
Record 1 is the Source - that value is Evaluated against the rest of the records.

Code:

       ' **************** Begin evaluating Each Record - Column By Column ***********************
       ' **************** Note - if one Column is assigned "Eliminate" then don't evaluate next Column
       ' ****************        Move to next record set and sart with first column
160    Do Until RSEvaluate2WellsFieldWorkDates.EOF  ' Note - once any column results in "eliminate" go to next record
          ' ******************** Start Test Date 1 ******** If Column 1 is Eliminated - no need to test Column 2,3,4,5,6,7 **************
170           If IsDate(Dt_SurveyEVAL) Then ' Date 1
180               If IsDate(RSEvaluate2WellsFieldWorkDates.Fields(4)) Then                        ' both are dates
190                   If Abs(Dt_SurveyEVAL - RSEvaluate2WellsFieldWorkDates.Fields(4)) < 4 Then   ' Dates match  ' CUSTOM Formula
200                       RSEvaluate2WellsFieldWorkDates.Edit
210                       RSEvaluate2WellsFieldWorkDates.Fields(11) = "Match"
220                       RSEvaluate2WellsFieldWorkDates.Update
230                   Else
240                       RSEvaluate2WellsFieldWorkDates.Edit
250                       RSEvaluate2WellsFieldWorkDates.Fields(11) = "Eliminate"                 ' Dont Match If Eliminated, move next record
260                       RSEvaluate2WellsFieldWorkDates.Update
270                   End If
280               Else                                                                            ' Current Target is Null
290                   RSEvaluate2WellsFieldWorkDates.Edit
300                   RSEvaluate2WellsFieldWorkDates.Fields(11) = "PossibleUpdate"                ' Orginal Date - Target Null
310                   RSEvaluate2WellsFieldWorkDates.Update
320               End If
330          Else                                                                                 ' Both source and Target are Null
340                       RSEvaluate2WellsFieldWorkDates.Edit
350                       RSEvaluate2WellsFieldWorkDates.Fields(11) = "Match"                     ' Still counts as a Match
360                       RSEvaluate2WellsFieldWorkDates.Update
370          End If
          ' ******************** END Test Date 1 **********
          ' ******************** Start Test Date 2 ********
380           If IsDate(Dt_Arch_ReqEVAL) Then ' Date 2
390               If IsDate(RSEvaluate2WellsFieldWorkDates.Fields(5)) Then                        ' both are dates
400                   If Abs(Dt_SurveyEVAL - RSEvaluate2WellsFieldWorkDates.Fields(5)) < 7 Then   ' Dates match ' CUSTOM Formula
410                       RSEvaluate2WellsFieldWorkDates.Edit
420                       RSEvaluate2WellsFieldWorkDates.Fields(11) = "Match"
430                       RSEvaluate2WellsFieldWorkDates.Update
440                   Else
450                       RSEvaluate2WellsFieldWorkDates.Edit
460                       RSEvaluate2WellsFieldWorkDates.Fields(11) = "Eliminate"                 ' Dont Match If Eliminated, move next record
470                       RSEvaluate2WellsFieldWorkDates.Update
480                   End If
490               Else                                                                            ' Current Target is Null
500                   RSEvaluate2WellsFieldWorkDates.Edit
510                   RSEvaluate2WellsFieldWorkDates.Fields(11) = "PossibleUpdate"                ' Orginal Date - Target Null
520                   RSEvaluate2WellsFieldWorkDates.Update
530               End If
540          Else                                                                                 ' Both source and Target are Null
550                       RSEvaluate2WellsFieldWorkDates.Edit
560                       RSEvaluate2WellsFieldWorkDates.Fields(11) = "Match"                     ' Still counts as a Match
570                       RSEvaluate2WellsFieldWorkDates.Update
580          End If
590       RSEvaluate2WellsFieldWorkDates.MoveNext
600   Loop

Hope this is simple, have been in since 4AM working on the bigger part of this project.

Ranman256 · Apr 6, 2016

either way, just put a goto,(if you have more to do)
goto ExitLoop

ExitLoop:

if nothing else to do here, just exit,
exit sub

Rx_ · Apr 6, 2016

Thanks, I had one of those in the current Demo and it worked fine.
Just wondered if there was something different than a GOTO statement.
Other than Error Traps, the trend is to try and avoid them.
But, it is simple and it works.

But, its a working prototype.

Well, let me GOTO your post and give a THANKS

For others reading this:

265 goto ExitLoop:

585 ExitLoop

gemma-the-husky · Apr 6, 2016

I tend to use goto, but there is an exit command to exit loops.

https://msdn.microsoft.com/en-us/library/t2at9t47.aspx

fwiw, I always use while...wend statement format, as I just never got used to do...loop

Rx_ · Apr 6, 2016

In this case, can't exit the Do (loop) because the Loop is at the Record Level.
The Conditional Exit is at the Field(s) level.
In the real code, there are 15 fields with different conditions.
If any conditions return False, then there is no need to check the remanding fields.

It is all so simple... until we get to the actual details.
My first BASIC was BASIC in ROM on the Ohio Scientific Single Board. It had the WEND too. It only had 8K of RAM (until I spent a fortune and upgraded it to 32K).
Probably the WEND saved us a byte or two?
My First PC: http://www.technology.niagarac.on.ca/people/mcsele/OhioScientific.html
I took my superboard II and made a wooden case. The front flipped up like an old piano to expose the keyboards. I bought a portable 12VDC Zenith B&W 10" Portable that mounted on top of the wooden case. I hardwired the PC into the Zenith video circuit board. The TV had a handle - perfect for picking up my portable and throwing it into the car. Behind the TV, I had a thin set of mil-std lead-acid batteries I bought scrap from a military surplus auction. This gave me about 1 hour charge for computer and monitor. Forgot, I bought a dozen mil-std dual directional audio cassette (4 heads). Before floppy, I created my own DOS where one side of the cassette had audio to mark blocks of code. The programs could fast-forward or rewind listening to a set of tracks, stop at the point, then load a block of 8K of data for that part of the program. I actually got government contracts and did them faster than companies with punch cards.
The Atari 400/800 came out about a year later followed by the first Apple. All had the 6502 microprocessor.
The Wend... it probably doesn't get any more geek than knowing what that statement means! LOL

Galaxiom · Apr 6, 2016

I have never found the need for GoTo (except with On Error) though careful construction of the If blocks.

In situations where multiple conditional tests feed into a processing path then a flag can be set by each of the them and tested at the end.

Code:

 Do 
     If whatever Then 
        do stuff
        boolFlag = True
    End If 
  
     If anothertest Then
        dootherstuff
        boolFlag = True
    Else
        dodifferentstuff
    End If

    If boolFlag Then Exit Do

Loop

BTW You could make your code much more readable and avoid a lot of typing by using a With Block on RSEvaluate2WellsFieldWorkDates.

Your tests also look like they might be better done with Select Case.

I would also change to holding the test result in a variable then having a single section of code to handle the recordset Edit.

gemma-the-husky · Apr 7, 2016

@galaxiom

do you think exit do compiles differently to a goto

Galaxiom · Apr 7, 2016

What happens in the compile is a mystery to all but the backroom staff at Microsoft.

However Exit Do is a clear and complete instruction that does not require looking for the GoTo target which could be anywhere. Simply look down to the next Loop command.

I realise this is not much different if the GoTo target is straight after that Loop but I have seen some hideous examples of the use of GoTo, generally known to its detractors as "spaghetti code".

stopher · Apr 7, 2016

Galaxiom said:
I have never found the need for GoTo (except with On Error) though careful construction of the If blocks.

In situations where multiple conditional tests feed into a processing path then a flag can be set by each of the them and tested at the end.

Code:

Do If whatever Then do stuff boolFlag = True End If If anothertest Then dootherstuff boolFlag = True Else dodifferentstuff End If If boolFlag Then Exit Do Loop

BTW You could make your code much more readable and avoid a lot of typing by using a With Block on RSEvaluate2WellsFieldWorkDates.

Your tests also look like they might be better done with Select Case.

I would also change to holding the test result in a variable then having a single section of code to handle the recordset Edit.

Surely this should be:

Code:

 Do 
     If whatever Then 
        do stuff
        boolFlag = True
    End If 
  
     If anothertest Then
        dootherstuff
        boolFlag = True
    Else
        dodifferentstuff
    End If

Loop Until boolFlag

I'm a bit of a purist. Last time I used Goto was mid 1950s programming Fortran 77.

I would only use Exit Do for a complex do loop. I would never use Goto. But there does seem to be mixed views on this. Certainly Goto is deemed poor code but Exit Do seems widely acceptable.

One problem with Goto is that the location of the label is not guaranteed i.e. code could inadvertently be placed between the end of the loop and the label and would be skipped by goto. Then exit do at least ensures that the next line after the loop is always executed.

Some interesting commentary here albeit on C.

Rx_ · Apr 7, 2016

The GoTo argument - that is what was expected.
Yes, spaghetti code, what programmers use to keep a maintenance job to put starchy food on the table.
In my past years of instructing VB and VBA, there were those who objected to using On Error GoTo

The use of a Flag (boolFlag) is something I normally use. Thanks for that confirmation.
For a prototype under a time constraint the GoTo with a verbose comment is probably going to stay for now.

Once the proof of concept prototype is put into production and approved, the code will actually be moved over to T-SQL.

My agreement is to avoid the use of GoTo outside OnError

At the moment, the massive size of the backlog for the manual regulatory process is growing exponentially. The use of a GoTo with an individual explicit comment will help reach the goal of closing on an acceptable coding specification and offer relief for the backlog. The example above only had 2 fields, the actual code has many, many more.

Thanks everyone for actually looking at the code example and offering realistic comments.
All comments are very, very appreciated.

The_Doc_Man · Apr 7, 2016

As long as artificial intelligence isn't THAT intelligent, there will ALWAYS be room for a GOTO. Having said that, I usually prefer to use a "permission to continue" variable in a loop and then use a "Do .... While Permission=True" type of construct, then just set the permission to false when I need to abort the process.

Galaxiom · Apr 8, 2016

stopher said:
Surely this should be:

Code:

Do If whatever Then do stuff boolFlag = True End If If anothertest Then dootherstuff boolFlag = True Else dodifferentstuff End If Loop Until boolFlag

Yes that would be true in my hasty simplistic example. However there are other situations where one would test the flag to get out of the Loop other than at the end and avoid processing subsequent instructions.

Criteria for early exit of Do Until loop - or would For Next be better? (1 Viewer)

Rx_

Nothing In Moderation

Attachments

Ranman256

Well-known member

Rx_

Nothing In Moderation

gemma-the-husky

Super Moderator

Rx_

Nothing In Moderation

Galaxiom

Super Moderator

gemma-the-husky

Super Moderator

Galaxiom

Super Moderator

stopher

AWF VIP

Rx_

Nothing In Moderation

The_Doc_Man

Immoderate Moderator

Galaxiom

Super Moderator

Similar threads

Users who are viewing this thread