Copy Detail Record Only - Microsoft Access Check Register Seminar


	Back to Access Check Register Seminar Comments List

Upload Images @Reply Bookmark Link Email

Copy Detail Record Only

Werner Mildt

3 years ago

Hi Richard, I am trying to copy a record in my Detail File as a NEW Record and I can't get it to work. I do not want to copy
The Parent File. Since I have so many repeating entries it would be so much easier with a Copy Button.
Also, I would like to be able to after I have selected Filtered Data To only tag the Filtered Data, Either by way of a Button or
Code while the filtered data is selected.
Thanks, any recommendations would be greatly appreciated.

	Adam Schwanz @Reply 3 years ago
Duplicate Record

	Werner Mildt OP @Reply 3 years ago
Thanks for your reply. I have viewed this video, but it shows only the copy of the main file. It does not work with the detail file. I only want to duplicate Records in the Detail File? Thanks

	Kevin Robertson @Reply 3 years ago
You could put the button on the Subform and it will copy the currently selected record.

Adam Schwanz @Reply

3 years ago

I would probably just use a recordset then. I'm just going to use OrderT and OrderDetailT.

Dim rs as recordset

dim rs2 as recordset

set rs = CurrentDb.OpenRecordset("Select * From OrderDetailT Where OrderID=" & OrderID)

set rs2 = currentdb.openrecordset("Select * From OrderDetailT")



While not rs.eof

rs2.AddNew

rs2!OrderID=rs!OrderID

rs2!OtherField=rs!OtherField

'etc...

rs2.Update

rs.movenext

wend

rs2.close

rs.close

set rs2=nothing

set rs = notihng

	Adam Schwanz @Reply 3 years ago
That would copy an item of each item in the details, you would just need to make sure it got assigned to a good ID number, I'm not sure how your tables are setup so I can't advise the fields

Kevin Robertson @Reply

3 years ago

Adam I ran your code and it works great but got an extra item. The first item in the recordset is also added to the end. So I added a counter which seems to work.

    Dim rs As Recordset, rs2 As Recordset

    Dim itemCount As Long, counter As Long



    Set rs = CurrentDb.OpenRecordset("Select * From OrderDetailT Where OrderID=" & OrderID)

    Set rs2 = CurrentDb.OpenRecordset("OrderDetailT")



    itemCount = DCount("*", "OrderDetailT", "OrderID=" & OrderID)

    counter = 0

    

    While Not rs.EOF And counter <= itemCount - 1

        rs2.AddNew

            rs2!OrderID = rs!OrderID

            rs2!ProductName = rs!ProductName

            rs2!Quantity = rs!Quantity

            rs2!UnitPrice = rs!UnitPrice

            rs2!Notes = rs!Notes

            rs2.Update

            counter = counter + 1

        rs.MoveNext

    Wend



    rs2.Close

    rs.Close



    Set rs2 = Nothing

    Set rs = Nothing



    OrderDetailF.Form.Requery

	Adam Schwanz @Reply 3 years ago
Good eye Kevin, that's what I get for not testing :P

	Alex Hedley @Reply 3 years ago
Where's your Unit Tests?

	Werner Mildt OP @Reply 3 years ago
To Adam & Kevin, I have tried both of your codes and they both worked, however they both copied all the records in the Detail File for the current ID. What I need to do is select a record on the Detail Form and then copy only that one entry as a new Record. I would appreciate if you could help me with this. Thanks, Werner

Adam Schwanz @Reply

3 years ago

OH, you only want to copy one. That's way simpler.

CurrentDb.Execute "Insert Into OrderDetailT (OrderID,ProductName,Quantity,UnitPrice) VALUES (" & OrderID & ",'" & ProductName & "'," & Quantity & "," & UnitPrice & ")"

Me.Requery

Note if the button is on the main form instead of the subform, you will need to change the field names in the VALUES section to the full subform field name

CurrentDb.Execute "Insert Into OrderDetailT (OrderID,ProductName,Quantity,UnitPrice) VALUES (" & Forms!OrderF!OrderDetailF.Form!OrderID & ",'" & Forms!OrderF!OrderDetailF.Form!ProductName & "'," & Forms!OrderF!OrderDetailF.Form!Quantity & "," & Forms!OrderF!OrderDetailF.Form!UnitPrice & ")"

Forms!OrderF!OrderDetailF.Requery

That would make a copy of whatever one is selected.

	Richard Rost @Reply 2 years ago
I was gonna say, copying a record with all its children is in Access Developer 24.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Check Register Seminar.