Copying recordsets (kinda)...

[opopanax666]

Hi everybody,

can someone spot why only the fields from the first record in "qryBestellingen_nieuw" are copied when I click the button, although it contains about a dozen records with "Status" = 1

Private Sub Knop21_Click()

Dim db As Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM qryBestellingen_nieuw WHERE [Status] = 1")
Set rs2 = db.OpenRecordset("tblBestellingen_magazijn")

With rs2
.AddNew
.Fields("Bestelling_id") = rs!Bestelling_id
.Fields("Product") = rs!Product
.Update
.Close
End With

rs.Close

End Sub

 

[boblarson]

You never change the record that is in rs.

You would need:

Private Sub Knop21_Click()

Dim db As Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM qryBestellingen_nieuw WHERE [Status] = 1")
Set rs2 = db.OpenRecordset("tblBestellingen_magazijn")

[B][COLOR=red]Do Until rs.EOF[/COLOR][/B]
   With rs2
      .AddNew
      .Fields("Bestelling_id") = rs!Bestelling_id
      .Fields("Product") = rs!Product
      .Update
      .Close
   End With
[COLOR=red][B]rs.MoveNext[/B][/COLOR]
[B][COLOR=#ff0000]Loop[/COLOR][/B]
[B][COLOR=#ff0000][/COLOR][/B]
[COLOR=red][COLOR=black]rs.Close
[/COLOR][B]rs2.Close[/B][/COLOR]
[B][COLOR=red][/COLOR][/B] 
[B][COLOR=red]Set rs = Nothing[/COLOR][/B]
[B][COLOR=red]Set rs2 = Nothing[/COLOR][/B]
End Sub

 

[opopanax666]

Thank you very much. It's weird, because this is an example given somewhere in a forum, and it supposedly worked (to copy all records)...

 

[opopanax666]

Cried victory too soon...

When I press the button, the code copies the fields from the first record, and then gives an "error 3420: Object Invalid or no longer set", and stops at ".AddNew".
I did a search on the net, but couldn't find an example for this specific case, and my knowledge of DAO is, well, nada (or I would at least have spotted there was no loop in the original code ).

Anybody?

 

[JANR]

....
[B][COLOR=#ff0000]Do Until rs.EOF[/COLOR][/B]
   With rs2
      .AddNew
      .Fields("Bestelling_id") = rs!Bestelling_id
      .Fields("Product") = rs!Product
      .Update
      [COLOR=red].Close  <-----  Take out this
[/COLOR]   End With
[COLOR=red][B]rs.MoveNext[/B][/COLOR]
[B][COLOR=#ff0000]Loop[/COLOR][/B]
....

You close rs after the first loop round so it is no longer set, hence the error.

JR

 

[opopanax666]

Of course. While looking for examples, I came across one that said that trying to close an already closed rs would give an "error 3420". I just didn't notice the ".Close"...

Thank you for the quick response!

 

[JANR]

No worries, sometimes a fresh pair of eyes helps. You are not alone

JR

 

[boblarson]

No worries, sometimes a fresh pair of eyes helps. You are not alone

JR

Yep, even I missed it.