Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    154
    Thanks
    3
    Thanked 1 Time in 1 Post

    not rst.eof not working

    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

    Code:
    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

  2. The Following User Says Thank You to jmacleod For This Useful Post:

    Lastcall (2012-04-19)

  3. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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
    Last edited by johnhutchison; 2012-03-13 at 17:10.
    Regards
    John



  4. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    How about:

    If rst.EOF then EXIT DO
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    154
    Thanks
    3
    Thanked 1 Time in 1 Post
    Thank you both, I'll try those suggestions.

    Jim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •