locked
Using Query to Update Table - No Current record!! RRS feed

  • Question

  • Dim myDb As Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    
    Dim MyQuery As QueryDef
    Dim PremsTable As TableDef
    
    Set myDb = CurrentDb
    Set MyQuery = myDb.QueryDefs("QueryForWCPremiums")
    MyQuery.Parameters("Forms![Finance Agreement Details]!Policies.Form!PolicyControl") = [INSURANCE POLICY DATABASE].[Form_Finance Agreement Details]!Policies.Form!PolicyControl
    Set rs = MyQuery.OpenRecordset()
    Set PremsTable = myDb.TableDefs("WCClassCodePremsPerFac")
    Set rs2 = PremsTable.OpenRecordset(, dbAppendOnly)
    
    rs.MoveFirst
    
    While Not rs.EOF
    rs2.addnew
    rs2.MoveLast
    rs2.Edit
    rs2("PolicyControl").Value = rs("WCClassCodesPerPolicy_PolicyControl")
    rs2("Finance Agreement").Value = rs("Finance Agreement")
    rs2("ID").Value = rs("ID")
    
    rs2.Update
    rs2.MoveNext
    rs.MoveNext
    
    Wend
    
    
    End Sub

    This code keeps giving me the "No Current Record" error and highlighting rs2.MoveLast ... when I re-arrange the order of rs2.MoveLast, .AddNew, so forth, it just ends up highlighting .Edit or the other.

    I don't know how much more specific I can be pointing to the record I want to edit! This is a situation where the table doesn't have any records yet, also.

    Thank you

    Friday, October 4, 2013 5:58 PM

Answers

  • You're trying to add a new record in rs2, right?  In that case, you don't do any recordset movement -- no .MoveNext, .MoveLast, or any of the .MoveXXXX methods -- and you use rs2.AddNew *instead of* rs2.Edit, not *as well as* rs.Edit.  Assuming everything else is right, that block of code should look like:

    While Not rs.EOF
    
        rs2.AddNew
        rs2("PolicyControl").Value = rs("WCClassCodesPerPolicy_PolicyControl")
        rs2("Finance Agreement").Value = rs("Finance Agreement")
        rs2("ID").Value = rs("ID")
        rs2.Update
    
        rs.MoveNext
    
    Wend
    
    

    I'm not sure about what you're doing above that, with your reference to [INSURANCE POLICY DATABASE].[Form_Finance Agreement Details]!Policies.Form!PolicyControl -- it looks odd, but if it's working I'm not going to worry about it now.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Proposed as answer by Van Dinh Friday, October 4, 2013 11:25 PM
    • Marked as answer by AngRuiz1 Monday, October 7, 2013 7:03 PM
    Friday, October 4, 2013 6:36 PM

All replies

  • It looks like you are trying to update a query.  You can only update tables.  I would use Jet Sql instead of DAO.  Here is a sample to update one table with data from another table -- using Jet Sql

    UPDATE Table1 t1  INNER JOIN Table2 t2 ON t1.numID=t2.numID SET t1.phone = t2.phone

    The catch is that you need some key fields to join the tables on.  If you just want to update a single table -- you can do this:

    Update Table1 Set fldx = 'something' Where fldy = 'xyz'"


    Rich P


    • Edited by Rich P123 Friday, October 4, 2013 6:35 PM .....
    Friday, October 4, 2013 6:34 PM
  • You're trying to add a new record in rs2, right?  In that case, you don't do any recordset movement -- no .MoveNext, .MoveLast, or any of the .MoveXXXX methods -- and you use rs2.AddNew *instead of* rs2.Edit, not *as well as* rs.Edit.  Assuming everything else is right, that block of code should look like:

    While Not rs.EOF
    
        rs2.AddNew
        rs2("PolicyControl").Value = rs("WCClassCodesPerPolicy_PolicyControl")
        rs2("Finance Agreement").Value = rs("Finance Agreement")
        rs2("ID").Value = rs("ID")
        rs2.Update
    
        rs.MoveNext
    
    Wend
    
    

    I'm not sure about what you're doing above that, with your reference to [INSURANCE POLICY DATABASE].[Form_Finance Agreement Details]!Policies.Form!PolicyControl -- it looks odd, but if it's working I'm not going to worry about it now.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Proposed as answer by Van Dinh Friday, October 4, 2013 11:25 PM
    • Marked as answer by AngRuiz1 Monday, October 7, 2013 7:03 PM
    Friday, October 4, 2013 6:36 PM
  • It looks like you are trying to update a query.  You can only update tables. 

    While it is true that ultimately data resides in tables, and so that is where any updates take place, I'd like to clarify that a recordset opened on a query can most certainly be updated, so long as the query itself is updatable.  And a query can also be the target of an update query.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, October 4, 2013 7:02 PM