Loop without Do

[MADDPM]

I'm trying to use the following code with a set of nested If statements ... but I'm getting "Loop without Do" error at the fifth line from the bottom: "Loop While Not rsA.EOF"
I think I'm missing an End If? Can anyone help? Thanks-Colleen
------------------------------------
Option Compare Database
Option Explicit

Public Function CalculateServiceLX()

Dim rsA As DAO.Recordset
Dim rsS As DAO.Recordset
Dim lngEmp As Double
Dim dtBeginDate As Date
Dim dtExpireDate As Date
Dim strBeg As String
Dim strType As String
Dim strStatus As String
Dim strAY As String
Dim intCount As Integer

Set rsA = CurrentDb.OpenRecordset("SELECT * FROM [APPOINTMENT] ORDER BY [Employee_Number]")
Set rsS = CurrentDb.OpenRecordset("SELECT * FROM [YEARSOFSERVICE_LX]")

rsA.MoveFirst
lngEmp = rsA!Employee_Number
strBeg = rsA!Begin_Date_this_appt
strType = rsA!FT_PT
dtBeginDate = rsA!Begin_Date_this_appt
dtExpireDate = rsA!Expire_Date_this_appt
intCount = 0

Do
'If we are continuing with the same employee
If lngEmp = rsA!Employee_Number Then
'If we are continuing with the same employee and same service type,
If strType = rsA!FT_PT Then
'If we are continuing with the same employee, same service type, check if semester or year
If Year(dtBeginDate) = Year(dtExpireDate) Then
'semester, keep counting
intCount = intCount + 0.5
If Year(dtBeginDate) <> Year(dtExpireDate) Then
'year, keep counting
intCount = intCount + 1
rsA.MoveNext

'If we are continuing with the same employee but different service type, add to new table
' and reset counters and string variables
Else:
rsA.MovePrevious
rsS.AddNew
rsS!YS_Employee_Number = lngEmp
rsS!YS_Date_Range = strBeg & " - " & rsA!Expire_Date_this_appt
rsS!YS_FT_PT = strType
rsS!YS_Count = intCount
rsS.Update
rsA.MoveNext
If Not rsA.EOF Then
strBeg = rsA!Begin_Date_this_appt
strType = rsA!FT_PT
dtBeginDate = rsA!Begin_Date_this_appt
dtExpireDate = rsA!Expire_Date_this_appt
intCount = 0
End If
End If

'If we have encountered a new employee add to new table and reset counters and string variables

Else:
rsA.MovePrevious
rsS.AddNew
rsS!YS_Employee_Number = lngEmp
rsS!AY_Date_Range = strBeg & " - " & rsA!Expire_Date_this_appt
rsS!YS_FT_PT = strType
rsS!YS_Count = intCount
rsS.Update
rsA.MoveNext
If Not rsA.EOF Then
strBeg = rsA!Begin_Date_this_appt
strType = rsA!FT_PT
dtBeginDate = rsA!Begin_Date_this_appt
dtExpireDate = rsA!Expire_Date_this_appt
intCount = 0
End If
lngEmp = rsA!Employee_Number
strBeg = rsA!Begin_Date_this_appt
strType = rsA!FT_PT
dtBeginDate = rsA!Begin_Date_this_appt
dtExpireDate = rsA!Expire_Date_this_appt
intCount = 0
End If

If rsA.EOF Then
rsA.MovePrevious
rsS.AddNew
rsS!YS_Employee_Number = lngEmp
rsS!YS_Date_Range = strBeg & " - " & rsA!Expire_Date_this_appt
rsS!YS_FT_PT = strType
rsS!YS_Count = intCount
rsS.Update
rsA.MoveNext
End If

Loop While Not rsA.EOF

Set rsA = Nothing
Set rsS = Nothing

MsgBox ("Process Complete!")

End Function

 

[MajP]

I do not think any of these close
If strType = rsA!FT_PT Then
'If we are continuing with the same employee, same service type, check if semester or year
If Year(dtBeginDate) = Year(dtExpireDate) Then
'semester, keep counting
intCount = intCount + 0.5
If Year(dtBeginDate) <> Year(dtExpireDate) Then
'year, keep counting
intCount = intCount + 1

 

[dhookom]

Try count the number of If... lines and compare with number of End If lines. I don't think they match.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

I'd use smart indenter to discover such mismatch:

http://www.oaltd.co.uk/Indenter/Default.htm

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

You can also post your code into a text editor and delete everything to see your structure

If lngEmp = rsA!Employee_Number Then
If strType = rsA!FT_PT Then
If Year(dtBeginDate) = Year(dtExpireDate) Then
If Year(dtBeginDate) <> Year(dtExpireDate) Then
If Not rsA.EOF Then
End If
End If
If Not rsA.EOF Then
End If
End If

If rsA.EOF Then
End If

As Duane said, 7 ifs and 5 end ifs

 

[SkipVought]

FYI,

When I code a loop, With..End With, If...Else...End If etc., I 1) code the entire structure before I fill in the code within the structure and 2) indent the code within the structure. That way I ALWAYS have a Do with a Loop, a Select Case with and End Select, an IF with Else and End If, a For with a Next etc.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MADDPM]

Thanks everyone!
Fixed it - code works perfectly.

'If we are continuing with the same employee, same service type, check if semester or year
If Year(dtBeginDate) <> Year(dtExpireDate) Then
'year, keep counting
intCount = intCount + 1
ElseIf Year(dtBeginDate) = Year(dtExpireDate) Then
'semester, keep counting
intCount = intCount + 0.5
Else
intCount = intCount + 0
End If

 

[dhookom]

I'm with Skip on this. I rarely type a If without automatically:
- pressing enter,
- typing End If,
- pressing the up arrow,
- pressing the end key
- the enter key
- the tab key

This is soon like riding a bike.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya MADDPM . . .

I'm glad you got it fixed (mainly filling in proper End If). However ... just because it executes with no errors doesn't mean it follows the logic flow you intended. My point involves the code you posted [blue]12 Feb 10 15:47[/blue] ... where I hope you see that ...

[blue]            Else
                intCount = intCount + 0[/blue]

... will never execute! Making the execution of your code the same as:

[blue]   If Year(dtBeginDate) = Year(dtExpireDate) Then [green]'semester[/green]
      intCount = intCount + 0.5
   Else                                           [green]'year[/green]
      intCount = intCount + 1
   End If[/blue]

I'm only saying ... [purple]just make sure its what you want![/purple]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]