Stop a loop when variable reached in loop 1

[santastefano]

I have a loop that performs three functions in each loop. I want it to stop when it reaches a variable integer but it continues to the end of the loop and so (in this example) adds two steps before completing. I have tried initiating the variable -1 and this results in one short of the required steps.
In this code I have left out the allocation of variables and a couple of subsidiary steps to reduce the post but all variables set correctly (at the end of the final loop the variable is -2), in the real code all If and End If match OK and posting to the table is OK.

Private Sub Time6_Exit(Cancel As Integer)
If varSessionsPerWeek = 3 Then
Do While varSessionsTotal > 0
   With rsTimetable
      .AddNew   
      !Code = varCode
      !StartDate = varDate1
      !From = varTime1
      !To = varTime2
      .Update   
   End With
    varSessionsTotal = varSessionsTotal - 1
    varDate1 = varDate1 + 7
        Dim rsHolidays As DAO.Recordset
        Set db = CurrentDb
        Set rsHolidays = db.OpenRecordset("tblHolidays", dbopendynaset)
        With rsHolidays
            .FindFirst "Close1 = #" & Format(varDate1, "yyyy-mm-dd") & "#"
            Do While Not .NoMatch
            varDate1 = varDate1 + 7
            .FindNext "Close1 = #" & Format(varDate1, "yyyy-mm-dd") & "#"
            Loop
        End With
    rsHolidays.Close 
    Set rsHolidays = Nothing  nothing.
 With rsTimetable
      .AddNew   
      !Code = varCode
      !StartDate = varDate2
      !From = varTime3
      !To = varTime4
      .Update   
   End With
   varSessionsTotal = varSessionsTotal - 1
   varDate2 = varDate2 + 7
        Set db = CurrentDb
        Set rsHolidays = db.OpenRecordset("tblHolidays", dbopendynaset)
        With rsHolidays
            .FindFirst "Close1 = #" & Format(varDate2, "yyyy-mm-dd") & "#"
            Do While Not .NoMatch
            varDate2 = varDate2 + 7
            .FindNext "Close1 = #" & Format(varDate2, "yyyy-mm-dd") & "#"
            Loop
        End With
    rsHolidays.Close 
    Set rsHolidays = Nothing 
 With rsTimetable
      .AddNew   
      !Code = varCode
      !StartDate = varDate3
      !From = varTime5
      !To = varTime6
      .Update   
   End With
   varSessionsTotal = varSessionsTotal - 1
   varDate3 = varDate3 + 7
        Set db = CurrentDb
        Set rsHolidays = db.OpenRecordset("tblHolidays", dbopendynaset)
        With rsHolidays
            .FindFirst "Close1 = #" & Format(varDate3, "yyyy-mm-dd") & "#"
            Do While Not .NoMatch
            varDate3 = varDate3 + 7
            .FindNext "Close1 = #" & Format(varDate3, "yyyy-mm-dd") & "#"
            Loop
        End With
    rsHolidays.Close 
    Set rsHolidays = Nothing 
Loop
rsTimetable.Close 
Set rsTimetable = Nothing 
Set db = Nothing
DoCmd.GoToControl "Continue"
Else
DoCmd.GoToControl "Date4"
End If
End Sub

Any solution/suggestions gratefully received.

 

[rjoubert]

If I understand your post correctly, you can test for your variable value, then call "Exit Do" to jump out of your loop.

Do while ...
If variable = desiredvalue Then
Exit Do
End If
Loop

 

[PHV]

Have a look at the Exit Loop instruction

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rjoubert]

Exit Loop throws up a compile error. You can only use "Exit Do", "Exit For", "Exit Sub", "Exit Function", or "Exit Property

 

[santastefano]

Thanks very much rjoubert and PHV.
Looks OK
(Extra thanks for very quick response. Not sure where you are (and so what time) but dredging through my bad code at any time and suggesting a response to help a stranger is very kind)

 

[PHV]

OOps, sorry for the typo :~/