Link to home
Start Free TrialLog in
Avatar of M A
M AFlag for United States of America

asked on

Copy all data from a access table paste to the same table and change PK in access

I have an access file.  I would like to copy a row from a table and paste to the same table except primary key.
How this can be achieved?

Thanks
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

You can use this function:
Public Function CopyRecord( _
  ByVal strTable As String, _
  ByVal strId As String, _
  ByVal lngId As Long) _
  As Boolean

  Dim dbs     As DAO.Database
  Dim rst     As DAO.Recordset
  Dim rstAdd  As DAO.Recordset
  Dim fld     As DAO.Field
  Dim strFld  As String
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("Select * From " & strTable & " Where " & strId & "=" & lngId & ";")
  Set rstAdd = dbs.OpenRecordset("Select Top 1 * From " & strTable & ";")
'  Set rstAdd = rst.Clone
  
  With rstAdd
    .AddNew
      For Each fld In rstAdd.Fields
        With fld
          strFld = .Name
          If Not strFld = strId Then
            .Value = rst.Fields(strFld).Value
          End If
        End With
      Next
    .Update
    .Close
  End With
  rst.Close
  
  Set fld = Nothing
  Set rstAdd = Nothing
  Set rst = Nothing
  Set dbs = Nothing
  
End Function

Open in new window

/gustav
try

insert into yourTable(f1,f2,f3)
select T.f1,T.f2,T.f3 from yourTable as T
where T.PK=<some value>
Avatar of M A

ASKER

Thanks to both for the prompt reply.

@Rey Obrero
I am working on Access.
will this work as VBA  or as query?

@Gustav
Can you explain this line?
Set rst = dbs.OpenRecordset("Select * From " & strTable & " Where " & strId & "=" & lngId & ";")
@MAS
that is an Append query
Avatar of M A

ASKER

Thanks to both.
That didnt help.
As I am copying entire data as a new record/file except the primary key.
Maybe I am not good enough in VBA/query
in the query I posted, you have to select all fields except the field for the PK
SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M A

ASKER

Thsi is what I  tried now
INSERT INTO task_item ( ID, quotation_no, SKU, Item_detail, unit_price, QTY, Line_total )
SELECT task_item.ID, task_item.quotation_no, task_item.SKU, task_item.Item_detail, task_item.unit_price, task_item.QTY, task_item.Line_total
FROM task_item
WHERE (((task_item.quotation_no)=[Forms]![Dataentry]![Quotation_no]));

Open in new window

But quotation_no will be increased by a functiion I have,
Avatar of M A

ASKER

Many thanks for your prompt reply.
My primary key is not autonumber.
How this can be changed?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M A

ASKER

Thanks it worked for the main form.
Now I have a subform for items in a different table which is linked to the quotation no (which has multiple records associated with it).
how to copy this records along with it?

Thanks
Avatar of M A

ASKER

Thanks
That was handled here:

Copy records of subform

Notice this line:

    Set rstSource = Me!frmEdit_Order_Subform.Form.RecordsetClone

Adjust that to hold the name of your subform control, not the name of the form:

    Set rstSource = Me!YourSubformControlName.Form.RecordsetClone

and these lines:

    ElseIf .Name = "FK" Then
        ' Insert default new foreign key from copy of new master record.
        rstInsert.Fields(.Name).Value = lngNewFK

so FK is name of the foreign key to the quotation (could be quotationID or similar) and lngNewFK is the key of the new quotation.

Read the thread to its end, as this - and how/when to refresh the forms - is discussed here.

/gustav
Avatar of M A

ASKER

How to add this in my form?
As  on clikc event or ?
I mean this

It is duplicating but not changing the quotation no.
If you follow the thread ... call it after the MoveLast before the Bookmark setting:

        .MoveLast
        ' Copy childrecords using the new quotation ID.
        CopyRecords !ID.Value
        Me.Bookmark = .Bookmark

/gustav
Avatar of M A

ASKER

Many thanks I got it done.
I mixed both the codes and called from the onclick event
You are welcome!

/gustav