Copy record in table (Access 2000)

[freelance]

I need a code that deletes a record from one table and copies the content of this record to a new record in another table.
In my case the tables are TblClients and customers. In the form frmClients I have a control with which I may copy the content of a certain client to a new record in the form Fcustomers , after that delete the record and open the form Fcustomers to the already created customer.
As could be seen from the attachement, I use INSERT INTO statement, there is some
Mistake in it, and also I do not know how to open the second form exactly to the newly created customer.
Can you help me ?

[HansV]

Try this version of the On click event procedure:

Private Sub CmdConvert_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngID As Long

If IsNull(Me!CompanyName) Then
MsgBox "Please enter Company Name!", vbExclamation
Me!CompanyName.SetFocus
Exit Sub
End If

If MsgBox("A new customer will be created and the contact will be deleted. Are you sure?", _
vbQuestion + vbYesNo) = vbYes Then
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Customers", dbOpenDynaset)
rst.AddNew
rst!CompanyName = Me.CompanyName
rst!LastName = Me.LastName
lngID = rst!CustomerID
rst.Update
rst.Close
Set rst = Nothing
Set dbs = Nothing

DoCmd.SetWarnings False
On Error Resume Next
RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
DoCmd.OpenForm "FCustomers", , , "CustomerID = " & lngID
DoCmd.Close acForm, Me.Name
End If
End Sub

You must set a reference to the Microsoft DAO 3.6 Object Library in Tools | References...

[freelance]

Dear Hans,


Thank you so much for your excellent suggestion.I have tried it sucessfully
and i am very happy about that