Copy record to new record

[mveijndh]

Hi Guys,

I'm trying to copy a record to a new record and copy ALL data from that record. What I've found so far is the Insert Into, append query solution, but I thought there should be an easier way to do this as the record has a large number of fields.
The SQL code does not run and I can't see the error to little experience I think!

Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
  
 strSQL1 = "INSERT INTO tblProducts ( ProductPartNumber, ProductShortDescription, ProductLongDescription, " & _
"ProductListPrice , ProductCurrencyID, ProductMultiplier, ProductMultiplierType, ProductMultiplierFixed, " & _
"ProductActive, ProductFunction, ProductVoltage1, ProductPower, ProductPowerUnit, ProductRange, " & _
"ProductPhoto, ProductRoom, ProductTag, ProductDesc, ProductFlowUnit, ProductMinFlow, " & _
"ProductMaxFlow, ProductModelCode, ProductDiscount, ProductDutyTax, ProductFreight, ProductStock, " & _
"ProductDeliveryTime, ProductDeliveryUnit, ProductDatasheet, ProductChangeDate, ProductComSoftTime, " & _
"ProductComTime, ProductWeight, ProductVolume ) "
  
 strSQL2 = "Select ProductPartNumber, ProductShortDescription, ProductLongDescription, " & _
"ProductListPrice , ProductCurrencyID, ProductMultiplier, ProductMultiplierType, ProductMultiplierFixed, " & _
"ProductActive, ProductFunction, ProductVoltage1, ProductPower, ProductPowerUnit, ProductRange, " & _
"ProductPhoto, ProductRoom, ProductTag, ProductDesc, ProductFlowUnit, ProductMinFlow, " & _
"ProductMaxFlow, ProductModelCode, ProductDiscount, ProductDutyTax, ProductFreight, ProductStock, " & _
"ProductDeliveryTime, ProductDeliveryUnit, ProductDatasheet, ProductChangeDate, ProductComSoftTime, " & _
"ProductComTime, ProductWeight, ProductVolume from tblProducts "
  
 strSQL3 = "WHERE (ProductID = " & Me.fldProductID & ");"
  
 strSQL4 = strSQL1 & strSQL2 & strSQL3
 DoCmd.RunSQL strSQL4

Any suggestions?

 

[jdraw]

Please tell us why you want to copy one record to another? I don't understand.

 

[Galaxiom]

Your SQL will fail if the ProductID field is the Primary Key.

If you are using an Autonumber ProductID then simply leave it out of the command. Otherwise assign it a value that is not already in the table.

BTW include the second parameter in the Execute.

DoCmd.RunSQL strSQL4, dbFailOnError

Otherwise you won't get an error in the VBA if the command fails.

 

[mveijndh]

I want to copy the record as there is just a minor difference between the first and the second. After copying the record I'll edit it and correct the difference.
I've removed the ProductID field , but the result is the same, the translated message reads like: in the Query INSERT INTO you can't have multiple search fields for multiple values. Problem is I don't have any search fields as far as I know!

 

[Galaxiom]

Does it work if you build the query in the Query Designer?

(Substitute Forms!formname.fldProductID for the reference in the Where clause.)

 

[mveijndh]

No, It provides me with the same error. I can see the Where clause is converted fine.

 

[missinglinq]

Assuming that the ProductID is an AutoNumber, all you need is three commands:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

You can leave ProductID on the Form...Access will recognize it as an AutoNumber and assign a new number, when the data is pasted into the New Record.

Linq ;0)>

 

[mveijndh]

Hi Linq,

worked like a chame!!!

 

[missinglinq]

Glad we could help!

Linq ;0)>

 

[ppetrol]

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

Hello, Is it posible to save the record in another table?

 

[The_Doc_Man]

There are two issues involved here.

1. Yes, that is a copy/paste. You could surely put something before the PasteAppend command to select the paste location.

2. Having another table and selecting a whole record for a copy/append implies that the other table has identical structure (or else the command will fail). But if you have two tables with identical structure, there is a possibility of a design error. If you have two tables that are that close in structure, you must ask your self WHY you have that - because it likely violates proper normalization.

I suggest you open a new thread here and explore the issue you are trying to solve.

 

[ppetrol]

There are two issues involved here.

1. Yes, that is a copy/paste. You could surely put something before the PasteAppend command to select the paste location.

2. Having another table and selecting a whole record for a copy/append implies that the other table has identical structure (or else the command will fail). But if you have two tables with identical structure, there is a possibility of a design error. If you have two tables that are that close in structure, you must ask your self WHY you have that - because it likely violates proper normalization.

I suggest you open a new thread here and explore the issue you are trying to solve.

I have 2 tables (let's call them FrmAudio1 and FrmAudio2)
FrmAudio1 is the main table with all neccessary files.
FrmAudio2 is a new table with new audio files.

If I want to add a file that exists in FrmAudio1 to FrmAudio2 (with the same structure ofcourse) how would I have to do that?

The code looks like this:

Private Sub cmdKopie_Click()
            DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
            With Me.RecordsetClone
           
           .FindFirst "ID = " & Forms!FrmAudio1!Id
        
                DoCmd.RunCommand acCmdSelectRecord
                DoCmd.RunCommand acCmdCopy
                
[COLOR="Red"]I think a line of code should come here to get the file from FrmAudio1 to FrmAudio2, but this is where I need your help[/COLOR]

                DoCmd.GoToRecord , , acNewRec
            DoCmd.RunCommand acCmdPasteAppend
        
        Me.SRT = "A"
        Me.NUMMER = ""
        Me.TRACKNO = ""
            
End With
    Me.Refresh

End Sub

Thank you for your help in advance.

 

[Aeristan]

Copy record to new record using Recordset.Clone

Namasté!

Last night I was struggling with this exact problem. I came up with an entirely different solution, after reading the following article: Duplicate the record in form and subform which pbaldy was kind enough to link to.

The function has two arguments: the first is a reference to the Recordset containing the record you wish to duplicate, and the second is the unique primary key value of the source record (which in this case is a Long). A local copy is created using the Recordset.Clone method. The function returns the primary key value of the new record that was just created. I'm trying to write this function to make it usable with any Recordset, regardless of the name of the table or the individual fields.

Public Function [B]duplicate_Record[/B](rst As Recordset, ID As Long) As Long  [COLOR="green"]' returns the ID of the new record[/COLOR]
    
    Dim clone As Recordset: Set clone = rst.clone
    Dim f As Variant   [COLOR="green"] ' iterate through Fields[/COLOR]
    
    clone.AddNew
    For Each f In rst.Fields
        If f.Name <> "ID" Then clone.Fields(f.Name) = f            [COLOR="Green"]' always skip the AutoNumber Primary Key[/COLOR]
    Next f
    clone.Update           [COLOR="green"]' update the main table with the new record[/COLOR]
    clone.Bookmark = clone.LastModified     [COLOR="Green"]' set the current record of the clone to the new record just created[/COLOR]
    duplicate_Record = clone!ID
    clone.Close

End Function

With this solution, instead of listing each Field that you want to copy, you list the Fields that you do not want to copy. In this case, the only Field I don't want to copy is the primary key index named ID. This is easier in my opinion, because if you have a large number of Fields, and the structure of the source table changes, you won't have to go back and change this code (unless you don't want the new fields copied).

Probably this could be used to copy records from one table to another with a few changes. It might also be useful if your table contains multi-value fields and cannot be used in an Append query, but I'm not sure.

 

[syuksuzy]

Re: Copy record to new record using Recordset.Clone

Thanks Aeristan for your valuable input.

May I know how to refer the rst from the main form.

When I made rst = CurrentDb.OpenRecordset "Table Name" it go to first record.

Thanks in advance for your assistance.

Regards,

 

[syuksuzy]

done. I amended the code by creating rst to find record which = id

Public Function duplicate_Record(rst As Recordset, ID As Long) As Long ' returns the ID of the new record

rst.FindFirst "ID = " & ID
Dim clone As Recordset: Set clone = rst.clone
Dim f As Variant ' iterate through Fields

clone.AddNew
For Each f In rst.Fields
If f.Name <> "ID" Then clone.Fields(f.Name) = f ' always skip the AutoNumber Primary Key
Next f
clone.Update ' update the main table with the new record
clone.Bookmark = clone.LastModified ' set the current record of the clone to the new record just created
duplicate_Record = clone!ID
clone.Close

End Function

thanks a lot!!!

 

[PlymouthSid]

Assuming that the ProductID is an AutoNumber, all you need is three commands:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

You can leave ProductID on the Form...Access will recognize it as an AutoNumber and assign a new number, when the data is pasted into the New Record.

Linq ;0)>

Thank you very much indeed ~ That has the great advantage of simplicity and got me out of a (self dug) hole.

What also seems to happen is that a new (blank) record is created (simple to find and to delete but a nuisance). Is there a way to avoid this just as easily?

Apparently (I have yet to try it) there is, on the Command Button Wizard, a "Duplicate Record" command.

I am using an up to date Office 365 version of Access on a Windows 10 machine (don't ask the answer is too embarrassing!)

Sid

 

[theDBguy]

Thank you very much indeed ~ That has the great advantage of simplicity and got me out of a (self dug) hole.

What also seems to happen is that a new (blank) record is created (simple to find and to delete but a nuisance). Is there a way to avoid this just as easily?

Apparently (I have yet to try it) there is, on the Command Button Wizard, a "Duplicate Record" command.

I am using an up to date Office 365 version of Access on a Windows 10 machine (don't ask the answer is too embarrassing!)

Sid

Hi Sid,

Welcome to AWF!

May I suggest you start a new thread to get a better chance of getting help? This is a very old thread that people might just skip it.

 

[PlymouthSid]

Hi Sid,

Welcome to AWF!

May I suggest you start a new thread to get a better chance of getting help? This is a very old thread that people might just skip it.

Thank you for that welcome.

OK. I will try to do start a new thread as suggested

I have sorted the problem (to my satisfaction) but my suggestion may not win the approval of those that actually know how to write "simple" code!

Sid

 

[PlymouthSid]

Assuming that the ProductID is an AutoNumber, all you need is three commands:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

You can leave ProductID on the Form...Access will recognize it as an AutoNumber and assign a new number, when the data is pasted into the New Record.

Linq ;0)>

I think that this works a little better (it avoids the generation of "blank" records) by selecting where the "copied" record is pasted (Not "PasteAppended"!):


DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste

I found it after some serious browsing but actually it follows the shape of what happens if you use the Command Button Wizard! which generates a Macro which has the above steps!

Once again thank you ~ Your response saved me hours.
Sid

Why did I need to do this?​

The requirement was generated by the need (in a “club membership” database) not to have to retype the details of the address for each individual in a family at the same address. The fields that change are very easily edited on the simple form by the use of various drop down boxes (qualification, membership Status, male/female etc.

Quite often the “Forename” is all that needs to change. Without the ability to use a copy of another record, the data input load is onerous ~ the address would have to be typed each time and setting up a separate table for each address for use in a one-to-many relationship seems to be quite an overkill). I suppose I could have used a variation on the “Clients / Orders” design.

 

[Pat Hartman]

When you have a set of data where addresses are frequently duplicated as with families and contacts at companies, the best solution is a separate Address table. That allows you to change the Address for a group of people at the same time. With your method, you need to change every family member's address.