none
Copy records from one recordset to another in the same table RRS feed

  • Question

  • Hi All,

    The code below is my failed attempt to select records according to "TempVars!ToolSetID" search criteria, and if existent, copy them into the same table with a new value of "TempVars!ToolSetIDNew" in the "ToolSetID" field. Please advise what is wrong with my code, and whether this approach is even viable.

    'COPY ToolPositions FROM LAST ToolSet IF ONE EXISTS 
        Dim rsTP As DAO.Recordset
        Dim sqlTP As String
        Dim rsTPnew As DAO.Recordset
            sqlTP = "SELECT * FROM [ToolPosition]" & _
            "Where [ToolPosition].[ToolSetID]=" & TempVars!ToolSetID & ""
        
            Set rsTPnew = CurrentDb.OpenRecordset("ToolPosition")
            Set rsTP = CurrentDb.OpenRecordset(sqlTP)
        
            With rsTP
                Do While Not .EOF
                With rsTPnew
                rsTPnew.AddNew
                rsTPnew!ToolSetID = TempVars!ToolSetIDNew
                rsTPnew!ToolHolderID = rsTP!ToolHolderID
                rsTPnew!ToolInsertID = rsTP!ToolInsertID
                rsTPnew!PosNumber = rsTP!PosNumber
                rsTPnew!Function = rsTP!Function
                rsTPnew!Notes = rsTP!Notes
                
                End With
                .MoveNext
                Loop
            End With
    


    Les M.

    Tuesday, July 24, 2018 1:02 PM

Answers

  • Hi Les,

    Rather than use a recordset and a loop, have you tried using an APPEND query? For example:

    INSERT INTO ToolPosition (ToolSetID, ToolHolderID, ToolInsertID, PosNumber, [Function], Notes)
    SELECT TempVars!ToolsetIDNew, T.ToolHolderID, T.ToolInsertID, T.PosNumber, T.[Function], T.Notes
    FROM ToolPosition T
    WHERE T.ToolSetID = TempVars!ToolSetID

    Hope it helps...

    • Marked as answer by les2worry Tuesday, July 24, 2018 8:09 PM
    Tuesday, July 24, 2018 3:20 PM
  • Do I literally insert what you wrote into my code instead of the loop? 

    Les M.

    Hi Les,

    I hope you were talking to me. If so, what I was saying is you may not need to use code at all. Copy and paste, modify for object names as necessary, the SQL statement I suggested into the SQL view of a new query and then execute it. (Make sure you have a backup copy of your database first). Also, make sure the TempVars are properly initialized before running/executing the APPEND query.

    Hope it makes sense...

    • Marked as answer by les2worry Tuesday, July 24, 2018 8:09 PM
    Tuesday, July 24, 2018 5:40 PM
  • PS. If you must use code, then yes, you can use the APPEND query to replace your recordset creation and the loop to update it. For example:

    Dim strSQL As String

    strSQL = "insert append query SQL here"

    CurrentDb.Execute strSQL, dbFailOnError

    Hope it helps...

    • Marked as answer by les2worry Tuesday, July 24, 2018 8:09 PM
    Tuesday, July 24, 2018 5:41 PM

All replies

  • I’ve never done what you’re  trying.

    I expect you will have to get the entire ‘old’ record into local variables and then AddNew from the local variables to the new record.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Tuesday, July 24, 2018 2:27 PM
  • Hi Les,

    Rather than use a recordset and a loop, have you tried using an APPEND query? For example:

    INSERT INTO ToolPosition (ToolSetID, ToolHolderID, ToolInsertID, PosNumber, [Function], Notes)
    SELECT TempVars!ToolsetIDNew, T.ToolHolderID, T.ToolInsertID, T.PosNumber, T.[Function], T.Notes
    FROM ToolPosition T
    WHERE T.ToolSetID = TempVars!ToolSetID

    Hope it helps...

    • Marked as answer by les2worry Tuesday, July 24, 2018 8:09 PM
    Tuesday, July 24, 2018 3:20 PM
  •         sqlTP = "SELECT * FROM [ToolPosition]" & _
            "Where [ToolPosition].[ToolSetID]=" & TempVars!ToolSetID & ""
    

    Hi Les,

    A space is missed in the above sqlTP. For better recognition, ALWAYS place the space in front of the continuation lines, like:

    sqlTP = "SELECT * FROM [ToolPosition]" _
          & " WHERE [ToolPosition].[ToolSetID]=" & TempVars!ToolSetID & ""

    Imb.

     

    Tuesday, July 24, 2018 4:43 PM
  • Thanks but I am not getting any syntax errors. The code runs. It just doesn't create any new records.

    Les M.

    Tuesday, July 24, 2018 5:31 PM
  • Do I literally insert what you wrote into my code instead of the loop? 

    Les M.

    Tuesday, July 24, 2018 5:35 PM
  • Do I literally insert what you wrote into my code instead of the loop? 

    Les M.

    Hi Les,

    I hope you were talking to me. If so, what I was saying is you may not need to use code at all. Copy and paste, modify for object names as necessary, the SQL statement I suggested into the SQL view of a new query and then execute it. (Make sure you have a backup copy of your database first). Also, make sure the TempVars are properly initialized before running/executing the APPEND query.

    Hope it makes sense...

    • Marked as answer by les2worry Tuesday, July 24, 2018 8:09 PM
    Tuesday, July 24, 2018 5:40 PM
  • PS. If you must use code, then yes, you can use the APPEND query to replace your recordset creation and the loop to update it. For example:

    Dim strSQL As String

    strSQL = "insert append query SQL here"

    CurrentDb.Execute strSQL, dbFailOnError

    Hope it helps...

    • Marked as answer by les2worry Tuesday, July 24, 2018 8:09 PM
    Tuesday, July 24, 2018 5:41 PM
  • Thank you .theDBguy

    You got me headed in the right direction.


    Les M.

    Tuesday, July 24, 2018 8:11 PM
  • Thank you .theDBguy

    You got me headed in the right direction.


    Les M.

    Hi Les,

    Congratulations! Glad to hear you got it sorted out. We're all happy to assist. Good luck with your project.

    Tuesday, July 24, 2018 8:15 PM
  • Thanks but I am not getting any syntax errors. The code runs. It just doesn't create any new records.

    Les M.

    Hi Les,

    Strange! I always get error 3131 when the WHERE component is not preceeded by a space.

    Imb.

    Tuesday, July 24, 2018 8:38 PM