M A
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
How this can be achieved?
Thanks
try
insert into yourTable(f1,f2,f3)
select T.f1,T.f2,T.f3 from yourTable as T
where T.PK=<some value>
insert into yourTable(f1,f2,f3)
select T.f1,T.f2,T.f3 from yourTable as T
where T.PK=<some value>
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 & ";")
@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
that is an Append query
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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]));
But quotation_no will be increased by a functiion I have,
ASKER
Many thanks for your prompt reply.
My primary key is not autonumber.
How this can be changed?
My primary key is not autonumber.
How this can be changed?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
Thanks
That was handled here:
Copy records of subform
Notice this line:
Set rstSource = Me!frmEdit_Order_Subform.F orm.Record setClone
Adjust that to hold the name of your subform control, not the name of the form:
Set rstSource = Me!YourSubformControlName. Form.Recor dsetClone
and these lines:
ElseIf .Name = "FK" Then
' Insert default new foreign key from copy of new master record.
rstInsert.Fields(.Name).Va lue = 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
Copy records of subform
Notice this line:
Set rstSource = Me!frmEdit_Order_Subform.F
Adjust that to hold the name of your subform control, not the name of the form:
Set rstSource = Me!YourSubformControlName.
and these lines:
ElseIf .Name = "FK" Then
' Insert default new foreign key from copy of new master record.
rstInsert.Fields(.Name).Va
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
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.
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
.MoveLast
' Copy childrecords using the new quotation ID.
CopyRecords !ID.Value
Me.Bookmark = .Bookmark
/gustav
ASKER
Many thanks I got it done.
I mixed both the codes and called from the onclick event
I mixed both the codes and called from the onclick event
You are welcome!
/gustav
/gustav
Open in new window
/gustav