Solved

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

Posted on 2016-11-13
17
81 Views
Last Modified: 2016-11-15
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
0
Comment
Question by:-MAS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 4
17 Comments
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41885374
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41885377
try

insert into yourTable(f1,f2,f3)
select T.f1,T.f2,T.f3 from yourTable as T
where T.PK=<some value>
0
 
LVL 25

Author Comment

by:-MAS
ID: 41886095
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 & ";")
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41886293
@MAS
that is an Append query
0
 
LVL 25

Author Comment

by:-MAS
ID: 41887306
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41887311
in the query I posted, you have to select all fields except the field for the PK
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 41887320
here check this sample db

run the query Qry_Copy_record.

the query will copy the record with ID primary key value 1
db_28982748_Copy_record.accdb
0
 
LVL 25

Author Comment

by:-MAS
ID: 41887325
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,
0
 
LVL 25

Author Comment

by:-MAS
ID: 41887404
Many thanks for your prompt reply.
My primary key is not autonumber.
How this can be changed?
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 41887412
First, select all my code and paste it into a new code module.
Then compile and save. Give the module a name like basCopyRecord.

Go to your form and create or select the button that shall copy a record.
Pick from its property sheet, Events and OnClick, and select EventProcedure to open the code-behind-form module.

In the click event, enter this code line:

    CopyRecord    "task_item", "ID", [Forms]![Dataentry]![ID].Value    

Compile and save.

Open form, select a record, and click button.

Now, if the form is bound to the table "task_item", and you wish to see the new record, you must requery the form.
But, if so, you are making it too difficult for yourself as it is much faster and easier to copy the record directly.
To do so, use this code in your button click event:

Private Sub btnCopyQuotation_Click()

  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field
  
  If Me.NewRecord = True Then Exit Sub
    
  Set rstInsert = Me.RecordsetClone
  Set rstSource = rstInsert.Clone
  With rstSource
    If .RecordCount > 0 Then
      ' Go to the current record.
      .Bookmark = Me.Bookmark
      With rstInsert
        .AddNew
          For Each fld In rstSource.Fields
            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
              ElseIf .Name = "quotation_no" Then
                ' Insert custom quotation no.
                rstInsert.Fields(.Name).Value = YourCustomQuotationNoCreatorFunction
              Else
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With
          Next
        .Update
        ' Go to the new record and sync form.
        .MoveLast
        Me.Bookmark = .Bookmark
        .Close
      End With
    End If
    .Close
  End With
  
  Set rstInsert = Nothing
  Set rstSource = Nothing

End Sub

Open in new window

That's it. No requery, and the form at once will go to the new quotation copy.
0
 
LVL 25

Author Comment

by:-MAS
ID: 41887458
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
0
 
LVL 25

Author Closing Comment

by:-MAS
ID: 41887485
Thanks
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41887486
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
0
 
LVL 25

Author Comment

by:-MAS
ID: 41887556
How to add this in my form?
As  on clikc event or ?
I mean this

It is duplicating but not changing the quotation no.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41887678
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
0
 
LVL 25

Author Comment

by:-MAS
ID: 41887704
Many thanks I got it done.
I mixed both the codes and called from the onclick event
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41887760
You are welcome!

/gustav
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question