Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2002
    Posts
    168

    Unanswered: Copy record from one recordset to another

    I have two recordsets and I want to copy one record from rst2 to rst1. How do I do this ? Thanks

  2. #2
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    Hey Milan,

    I can show you two different methods. If you are copying records from one table to another table that is identical you can use the INSERT INTO command with SQL.

    Like this

    Dim db As Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT * FROM Table1 WHERE(SomeField1 = 'Something')")
    DoCmd.RunSQL "INSERT INTO Table2 SELECT * FROM Table1 WHERE(SomeField1 = """ & rs!SomeField1 & """)"
    rs.Close

    Or if the Tables are different and you want to copy the data from field to field you can try this:

    Dim db As Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT * FROM Table1 WHERE(SomeField1 = 'Something')")
    Set rs2 = db.OpenRecordset("Table2")
    With rs2
    .AddNew
    .Fields("SomeField1") = rs!SomeField1
    .Fields("SomeField2") = rs!SomeField2
    .Update
    .Close
    End With
    rs.Close

    Is this what you needed? Let me know

    Kal

  3. #3
    Join Date
    Apr 2002
    Posts
    168
    That's exactly what I want, thanks a lot.

  4. #4
    Join Date
    Apr 2002
    Posts
    168
    What if I want to copy all records from Table2 to Table1 using your first method (DoCmd.RunSQL) ? So, let's say, I just scan through from first record in Table 2 until last record, and for each of the scan I copy to Table1.

    Thanks

  5. #5
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    If I understand you correctly you can try this:

    Dim db As Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Table1")
    rs.MoveFirst
    Do Until rs.EOF
    DoCmd.RunSQL "INSERT INTO Table2 SELECT * FROM Table1 WHERE(SomeField = """ & rs!SomeField & """)"
    rs.MoveNext
    Loop
    rs.Close
    MsgBox "Copy complete!"

    You can manipulate this code with all kinds of variables to get exactly what you need. Let me know if this is what you meant.

    Kal

  6. #6
    Join Date
    Apr 2002
    Posts
    168
    Exactly, thanks a lot, you've been very helpful and clear

  7. #7
    Join Date
    Mar 2016
    Posts
    3

    Copy record from one recordset to another (run-time error '3021)

    [QUOTE=kpalmer;1375745]Hey Milan,

    I can show you two different methods. If you are copying records from one table to another table that is identical you can use the INSERT INTO command with SQL.

    Like this

    Dim db As Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT * FROM Table1 WHERE(SomeField1 = 'Something')")
    DoCmd.RunSQL "INSERT INTO Table2 SELECT * FROM Table1 WHERE(SomeField1 = """ & rs!SomeField1 & """)"
    rs.Close


    Hi kpalmer,

    I'm new in vba and I'm working with your first method to copy the recordset to another but I having run-time error '3021' -- No Current Record in VBA

    Here is my VBA codes for your advices


    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT ProjectCode, Wall_ID, ProductID, Timesing,Length, Height, CorIntEnd FROM tbl_MEA_Wall WHERE(ProjectCode = 'Me.[Parent]![cboProjectCode]')")
    'Me.[Parent]![cboProjectCode]
    rs.Requery
    DoCmd.RunSQL "INSERT INTO tbl_MEA_WallFinishes SELECT ProjectCode, Wall_ID, ProductID, Timesing, Length, Height, CorIntEnd FROM tbl_MEA_Wall WHERE(ProjectCode = """ & rs!ProjectCode & """) "

    rs.Close
    MsgBox "Copy complete!"


    Thanks in advance.

Posting Permissions

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