Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2016
    Posts
    6

    Unanswered: Copy record from one recordset to another

    Hi, I'm trying to copy the record from one table to another using the following VBA codes. I'm having run-time error 3021 -- No Current Record.
    I think the problem happen in the red color font. Can someone please figure out the problem in my VBA codes for me.

    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]')")

    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.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So do you check if there is a result in the first recordset.
    What data type is oroject code
    Code:
    Set rs = db.OpenRecordset("SELECT ProjectCode, Wall_ID, ProductID, Timesing,Length, Height, CorIntEnd FROM tbl_MEA_Wall WHERE(ProjectCode = '" & Me.[Parent]![cboProjectCode] & "')")
    If you want to use a value from a variable / control then you need to 'drop out' of a string literal and use vba to append that variable or control to the string

    So if your first sql statement fails, and as setup currenty it will (unless you have a project code = to "Me.[Parent]![cboProjectCode]')" 'Me.[Parent]![cboProjectCode]"
    Then you have a null value in rs!ProjectCode

    On the face of it i dont see why you open the rs if the only valye you have is limited by 'Me.[Parent]![cboProjectCode]')". So you could replace rs!ProjectCode with 'Me.[Parent]![cboProjectCode]'
    Last edited by healdem; 03-27-16 at 05:04.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2016
    Posts
    6
    Hi Healdem,

    It works fine when I replace rs!ProjectCode with 'Me.[Parent]![cboProjectCode]'


    Thank you.

Posting Permissions

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