Can someone help me with this simple code. The code in bold doesn't work (just an update on a table). Any idea?

 Many thanks!

 Kris

Private Sub Command4_Click()
On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord

Dim Result As Integer

Dim Dbs As DAO.Database
Dim rst1, rst2, rst3 As DAO.Recordset
Set Dbs = CurrentDb
Dim Record_ID As Integer
'Main Record
Set rst1 = Dbs.OpenRecordset("SELECT * FROM qry_Import;")
Set rst2 = Dbs.OpenRecordset("SELECT * FROM tbl_Case ORDER BY tbl_Case.Record_ID;")

Result = MsgBox("Transfer?", vbOKCancel, "Confirmation")
Select Case Result
  Case vbOK

  rst1.MoveFirst

  While Not rst1.EOF

  rst2.AddNew
  rst2!Nom = rst1!id_nome
  rst2!Prénom = rst1!id_apelido
  rst2.Update
  rst2.MoveLast
  Record_ID = rst2!Record_ID


  'add autre nom dans la table tbl_Autre_Nom

  Set rst3 = Dbs.OpenRecordset("tbl_Autre_Nom")
  rst3.AddNew
  rst3!Record_ID = Record_ID
  rst3!Autre_Nom = rst1!id_outronome
  rst3.Update
  rst3.Close

  'Update database quandidat pour record transfere

rst1.Edit
  rst1!id_Transfered = True
  rst1!id_To_be_Transfered = False
  rst1.Update
  rst1.MoveNext

  Wend

  rst2.Close
  rst1.Close
Case vbCancel
  Me.Undo
  DoCmd.CancelEvent
End Select

Me.Requery

End Sub

Hi Kris

By default the cursor will be forwar-only. You can read the records but not update using this cursor.

Use either dynamic or keyset for updation

rst1.CursorType = adOpenDynamic

should do that

Cheers
Shasur

http://www.vbadud.blogspot.com

You might note that this is DAO and not ADO, and things like CursorType are not part of the DAO Recordset object.

I suggest removing the On Error Resume Next, and step through the code to determine where as error might be occuring.

Also, it is probably not possible - but it would be a good idea to upgrade everything to ADO if you can. I assume this us VB6? DAO went out of general use at least 5 years ago, and even then it was used only for legacy applications that didn't have much life left in them.

Woody Z http://www.learntoprogramnow.com