not rst.eof not working

[jmacleod]

Hi,
I've a piece of code that concatenates sickness records when the two records are continuous.

I get a "no current record" error on the line "EmployeeNumberNext = rst!EmpNum"

How do I get the code to "break out" of the loop after the "rst.MoveNext" directly above "EmployeeNumberNext = rst!EmpNum" ?

Thank you,
Jim MacLeod
Shetland Isles

Function concatenatedata()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim EmployeeNumber As String
Dim EmpNum As String
Dim EmployeeNumberNext As String
Dim WriteSQL As String
Dim StartSick As Date
Dim FromDate As Date
Dim ToDate As Date
Dim EndSick As Date
Dim nextStartSick As Date
Dim nextEndSick As Date
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT [Source Data].Employee AS EmpNum, [Source Data].From AS FromDate, [Source Data].To AS ToDate FROM [Source Data] ORDER BY [Source Data].Employee, [Source Data].From;", dbOpenForwardOnly)
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM [Concatenated Data]"

Do While Not rst.EOF
    EmployeeNumber = rst!EmpNum
    StartSick = rst!FromDate
    EndSick = rst!ToDate
    rst.MoveNext
    
    EmployeeNumberNext = rst!EmpNum
    nextStartSick = rst!FromDate
    nextEndSick = rst!ToDate
    
    Do While (EmployeeNumber = EmployeeNumberNext) And (nextStartSick = EndSick + 1) And Not rst.EOF
        EndSick = nextEndSick
        rst.MoveNext
        nextStartSick = rst![FromDate]
        nextEndSick = rst![ToDate]
    Loop
        
    WriteSQL = "INSERT into [Concatenated Data]  (Employee,[From],[To]) Values (" & "'" & EmployeeNumber & "'" & " ," & "'" & StartSick & "'" & " ," & "'" & EndSick & "'" & " );"
    DoCmd.RunSQL WriteSQL
    Debug.Print EmployeeNumber, StartSick, EndSick
    
    
Loop
DoCmd.SetWarnings True
End Function

[johnhutchison]

I think the error occurs at the start of the process rather than the end because you don't do a rst.MoveFirst before

EmployeeNumber = rst!EmpNum

But there will still be problems with the inner loop if the outer loop hits EOF.

I think you can do it with a single loop.

• Go to the first record read in the data into some variables
• If not EOF then MoveNext
• Start the loop
• Read the data into a different set of variables
• Compare the two sets of variables to decide if it is the same person etc.
• Take what action is needed
• Move Next
• End Loop
• Deal with the last remaining values

[MarkLiquorman]

How about:

If rst.EOF then EXIT DO

[jmacleod]

Thank you both, I'll try those suggestions.

Jim