Copy record to new record (1 Viewer)

[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!!!