Using Query to Update Table - No Current record!!

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

Answer 1

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

Answer 2

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

Answer 3

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