compile error do without loop

[AccessHelp12]

Hey Guys i was just wandering if you could help me solve the error I come up with
compile error do without loop

Private Sub cboTime_Enter()
Dim i As Date, n As Integer, oRS As DAO.Recordset
Dim sSQL As String, dDuration As Date
Dim dLowerbreak As Date, dUpperBreak As Date

cboTime.RowSourceType = "Value List"
cboTime.RowSource = ""

If IsNull(Start) Then Exit Sub Else i = Start

If Me.NewRecord = True Then
   DoCmd.RunCommand acCmdSaveRecord
End If

sSQL = "SELECT DoctorsID,AppointDate,AppointTime"
sSQL = sSQL & " FROM qrySubformAppoints"
sSQL = sSQL & " WHERE DoctorsID=" & Me.ID & _
" AND AppointDate=#" & Me.txtAppointDate & "#"
Set oRS = CurrentDb.OpenRecordset(sSQL)
dDuration = TimeValue("00:30")
dLowerbreak = Break - TimeValue("00:25") 'Break is a field
dUpperBreak = Break + TimeValue("00:25")

If oRS.RecordCount = 0 Then
   Do
      If i <= dLowerbreak Or i >= dUpperBreak Then
         cboTime.AddItem i
      End If
      i = i + dDuration
   Loop Until i >= txtEnd
Else
   Do
      If i <= dLowerbreak Or i >= dUpperBreak Then
         oRS.FindFirst "[AppointTime] Between #" & i - TimeValue("00:00:05") & _
         "# And #" & i + TimeValue("00:00:05") & "#"
         If oRS.NoMatch Then cboTime.AddItem i
      End If
oRS.Close
End Sub

[June7]

If you post code between CODE tags, indentation will be retained, assuming you use indentation in your code. It makes code much easier to read.

I edited your post to show the indentation. Can you see what is missing? Second Do does not have matching Loop and the outermost If Then does not have an End If.

[JoeM]

Its telling you pretty much exactly what the issue is.
You have two "Do" loops, but your second (last) one does not have a "Loop" command.

This line also looks a little odd to me:

	If IsNull(Start) Then Exit Sub Else i = Start

What is Start? Is it a variables? I do not see it defined or set anywhere.
If it is a literal text value, it needs to be enclosed in quotes, i.e.

	If IsNull(Start) Then Exit Sub Else i = "Start"

[AccessHelp12]

Sorry i am new to VBA coding
i copied the code from a video on youtube
Start is a field name
www.youtube.com/watch?v=hQuXVxGFm7s

[June7]

So Start is a field on form?

Try:

If IsNull(Me.Start) Then Exit Sub Else i = Me.Start

Did you review my previous post?

[AccessHelp12]

That does not make a difference.
Like i said i am new to VBA so i did not understand what you said in your previous post.
The coding is from the guy who uploaded it on youtube

[June7]

Then you need to learn programming basics. Look at the first Do in the code. It has a matching Loop line. The second Do does not have a Loop line.

Look at each If Then structure. The first one is an all-on-one-line approach. All the others are split to separate lines. These must have an End If line. They must be in pairs.

I can't view the YouTube now so can't say if the errors started there but they are difinitely present in your code.

[ItsMe]

This will get rid of the errors but it probably will not give the desired results. Like June said, you need to learn how to debug. Study this and see if it makes sense. Study the results if you can get it to run.

Dim i As Date, n As Integer, oRS As DAO.Recordset
Dim sSQL As String, dDuration As Date
Dim dLowerbreak As Date, dUpperBreak As Date
cboTime.RowSourceType = "Value List"
cboTime.RowSource = ""
'If IsNull(Start) Then Exit Sub Else i = Start
If IsNull(Me.Start) Then Exit Sub Else i = Me.Start
 
        If Me.NewRecord = True Then
           DoCmd.RunCommand acCmdSaveRecord
        End If
    
    sSQL = "SELECT DoctorsID,AppointDate,AppointTime"
    sSQL = sSQL & " FROM qrySubformAppoints"
    sSQL = sSQL & " WHERE DoctorsID=" & Me.id & _
    " AND AppointDate=#" & Me.txtAppointDate & "#"
    Set oRS = CurrentDb.OpenRecordset(sSQL)
    dDuration = TimeValue("00:30")
    dLowerbreak = Break - TimeValue("00:25") 'Break is a field
    dUpperBreak = Break + TimeValue("00:25")
    
    If oRS.RecordCount = 0 Then
       Do
          If i <= dLowerbreak Or i >= dUpperBreak Then
             cboTime.AddItem i
          End If
          i = i + dDuration
       Loop Until i >= txtEnd
    Else
    '   Do
          If i <= dLowerbreak Or i >= dUpperBreak Then
             oRS.FindFirst "[AppointTime] Between #" & i - TimeValue("00:00:05") & _
             "# And #" & i + TimeValue("00:00:05") & "#"
             If oRS.NoMatch Then cboTime.AddItem i
          End If
    oRS.Close
    End If

[AccessHelp12]

I am now getting a error with the following code
Private Sub cboTime_AfterUpdate()
subform.SetFocus
DoCmd.GoToControl "AppointTime"
DoCmd.GoToRecord , , acNewRec
subform.Form.Controls("AppointTime") = Me.cboTime
subform.Form.Controls("AppointDate") = Me.txtAppointDate
subform.Form.Controls("cboClient").SetFocus
subform.Form.Controls("cboClient").Dropdown
End Sub

the subform.setfocus gets highlighted in yellow

[June7]

Except probably should put the oRS.Close below the End If.

What is the exact error message? Is your subform actually named 'subform'?

[AccessHelp12]

but then would it not be better of to delete the last end if
rather then having it as
End If
End If
oRS.close

How do you know what the subform is called.
under property sheet it is tblAppointments subform

[AccessHelp12]

There is no error message

[June7]

If and End If must be in PAIRS if using multi-line structure. Doesn't matter if one follows another - that is nested If Then. But what last End If are you talking about? ItsMe's correction provides the missing End If.

Use the actual form name.

You need to understand that a subform is created by placing a subform/subreport container control on another form/report. The container control holds an object (table, query, form, or report). Should give the container control a name different from the object it holds, like ctrAppointment. So your code would be like:

Me.ctrAppointment.SetFocus

[AccessHelp12]

sorry how do you find out the name
dont forget i am completely new to this.
i am doing a course

[ItsMe]

Click on the subform container and look in the Other tab of the property sheet. I usualy left click in an open area and swoop of to ensure the selection of the container and not an object inside the container.

And june is correct. The oRS.Close should go after the last End if. Better yet
End If
End If
oRS.close
Set oRS = Nothing