none
Do While statement unexpectedly results in an infinite loop RRS feed

  • Question

  • Hi All,

    I'm trying to add records to two tables ("TPTHJoint" and "TPTIJoint") and copy values to them from a third table ("ToolPosition"). Two "...Joint" tables are in one-to-many relationship with "ToolPosition" table. Although there are only four records in the "ToolPosition" table which meet my "Where" criteria, the code gets in an infinite loop, creating infinite records in both "...Joint" tables with data from only first of the selected records in the "ToolPosition" table.

    Please help.

    Below you will find the code and snapshots of involved tables.

    Dim rsTP As DAO.Recordset
        Dim sqTP As String
        Dim rsTPTHJ As DAO.Recordset
        Dim rsTPTIJ As DAO.Recordset
        sqTP = "SELECT ToolPosition.ID, ToolPosition.ToolSetID, ToolPosition.THID, ToolPosition.TIID, ToolPosition.PosNumber, ToolPosition.Function, ToolPosition.Notes " & vbCrLf & _
                "FROM ToolPosition " & vbCrLf & _
                "WHERE (((ToolPosition.ToolSetID)=" & [TempVars]![ToolSetID] & "));"
            Set rsTP = CurrentDb.OpenRecordset(sqTP)
            Set rsTPTHJ = CurrentDb.OpenRecordset("TPTHJoint")
            Set rsTPTIJ = CurrentDb.OpenRecordset("TPTIJoint")
           
            With rsTP
            Do While Not .EOF
                rsTPTHJ.AddNew
                rsTPTHJ!THID = rsTP!THID
                rsTPTHJ!tpid = rsTP!ID
                rsTPTHJ.Update
                rsTPTIJ.AddNew
                rsTPTIJ!TIID = rsTP!TIID
                rsTPTIJ!tpid = rsTP!ID
                rsTPTIJ.Update
            Loop
            Set rsTPTHJ = Nothing
            Set rsTPTIJ = Nothing
            End With
            Set rsTP = Nothing



    Les M.


    • Edited by les2worry Monday, August 6, 2018 6:40 PM
    Monday, August 6, 2018 6:39 PM

Answers

  • Hi Les,

    I can't tell if your table structure is correct because there should be no need to store the same information into multiple table, but the reason why you're getting an infinite loop is because you're not advancing the record pointer in your Do While loop.

    It should be something like this:

    Do While Not .EOF

    'do stuff

    .MoveNext

    Loop

    Hope it helps...

    • Marked as answer by les2worry Monday, August 6, 2018 7:54 PM
    Monday, August 6, 2018 6:52 PM

All replies

  • Hi Les,

    I can't tell if your table structure is correct because there should be no need to store the same information into multiple table, but the reason why you're getting an infinite loop is because you're not advancing the record pointer in your Do While loop.

    It should be something like this:

    Do While Not .EOF

    'do stuff

    .MoveNext

    Loop

    Hope it helps...

    • Marked as answer by les2worry Monday, August 6, 2018 7:54 PM
    Monday, August 6, 2018 6:52 PM
  • Thank you DBguy,

    You've come through as an excellent help again.

    As far as the table structure goes, I completely agree with you. However, this is kind of an extraordinary situation, as I need to copy information from records behind several relationships. I don't even know how to explain it in simple words, and i may seek your counsel in this matter at a later time.

    Thank you again.


    Les M.

    Monday, August 6, 2018 8:11 PM
  • Hi Les,

    Please don't get me wrong. I am not saying you're doing anything wrong. I just wanted to raise awareness in case there's something there to think about.

    In any case, glad we could help. Good luck with your project.

    Monday, August 6, 2018 8:36 PM
  • Hello DBguy,

    No worries, as a matter of fact, you made me revisit my data structure in the subject area and it turns out that I didn't need many-to-many relationship in two instances. Once I got rid of them everything worked out fine. Anyway, it was a great exercise and a learning experience. 

    Thank you for your support! 


    Les M.

    Wednesday, August 8, 2018 1:26 PM
  • Hi Les,

    You're welcome. Glad to hear you're making good progress. Cheers!

    Wednesday, August 8, 2018 3:33 PM