Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy record in table (Access 2000)

    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 ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy record in table (Access 2000)

    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...

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy record in table (Access 2000)

    Dear Hans,


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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •