Solved Duplicate Record VBA (1 Viewer)

[MattBaldry]

Hi All,

I have been trying to find a solution for duplicating multiple rows but have been unsuccessful so far. I have a table, tblProductLabour, with 4 fields as below.

ProductID (FPK - Number)
ProductLabourID (PK - AutoNumber)
LabourProcessID (FPK - Number)
ProductLabourQuantity (Number)

I just want a simple VBA code to copy the LabourProcessID and ProductLabourQuantity from one ProductID to another.

I have a feeling this is going to be very simple for the Professor Hulk brains you guys have, but I am just stumped. Normally I find everything I want on here, but I think my brain is broken at the moment.

I know I am going to kick myself with how simple this is going to turn out to be

~Matt

 

[theDBguy]

Is the record you want to copy those fields already existing, or are you trying to create a new record?

 

[plog]

Presumably you know the ProductLabourID of the record you want to copy and the ProductID that should be used for the new record.

From there it's a simple INSERT query:

--(Psuedo code, not functioning)--


Sub copy_Record(in_PLID, in_PID)
' in_PLID is the ProductLabourID of record to copy, in_PID is ProductID to use in new record

  lpID= DLookup(LabourProcessID, Table, in_PLID)
' get LabourProcessID of record to copy

  plQ= DLookup(ProductLabourQuantity, Table, in_PLID)
' get ProductLabourQuantity of record to copy

  sql="INSERT INTO Table (ProductID, LabourProcessID, ProductLabourQuantity) VALUES (in_PID, lpID, plQ)"
  DoCmd.RunSQL sql
' compiles and runs INSERT statement

end sub

 

[MattBaldry]

Is the record you want to copy those fields already existing, or are you trying to create a new record?

As an example, ProductID 123 has 5 LabourProcessID rows assigned to it, each with the relevant ProductLabourQuantity values.

Each row would have it's own ProductLabourID.

I would want to be able to copy these from ProductID 123 to ProductID 456.

Copying 1 record is easy. I am struggling with the copying of all rows assigned to a ProductID.

~Matt

 

[arnelgp]

create an Insert query (qryDup):

Insert Into tblProductLabour ( ProductID, LabourProcessID, ProductLabourQuantity )
Select [p1], LabourProcessID, ProductLabourQuantity
From tblProductLabour Where ProductID = [p2];

save the query.
create a function in a module to call this query.

Public Function fncCopyProcess(Byval srcProductID As Variant, Byval trgProductID As Variant)
    Const INSERT_QUERY As String = "qryDup"
    If IsEmpty(srcProductID) Or IsEmpty(trgProductID) Then
        Exit Function
    End if
    With DoCmd
        .SetParameters "p1", trgProductID
        .SetParameters "p2", srcProductID
        .SetWarnings False
        .OpenQuery INSERT_QUERY
        .SetWarnings True
    End With
End Function

 

[arnelgp]

you can use a Recordset and Loop through each record and do the copyig, eg:

dim db as dao.database
dim rs as dao.recordset
dim srcID as long
srcID = 123
set db=currentdb
set rs = db.openrecordset("mainProductTable", dbOpenSnapshot, dbReadOnly)
with rs
    if not (.bof and .eof) then
        .movefirst
    end if
    do until .eof
        if !ProductID <> srcID then
            call fnCopyProcess(srcID, !ProductID)
        end if
        .movenext
    loop
    .close
end with
set rs=nothing
set db=nothing

 

[MattBaldry]

create an Insert query (qryDup):

Insert Into tblProductLabour ( ProductID, LabourProcessID, ProductLabourQuantity )
Select [p1], LabourProcessID, ProductLabourQuantity
From tblProductLabour Where ProductID = [p2];

save the query.
create a function in a module to call this query.

Public Function fncCopyProcess(Byval srcProductID As Variant, Byval trgProductID As Variant)
    Const INSERT_QUERY As String = "qryDup"
    If IsEmpty(srcProductID) Or IsEmpty(trgProductID) Then
        Exit Function
    End if
    With DoCmd
        .SetParameters "p1", trgProductID
        .SetParameters "p2", srcProductID
        .SetWarnings False
        .OpenQuery INSERT_QUERY
        .SetWarnings True
    End With
End Function

Thanks Arnelpg,

That was almost what I had but was getting wrong. I shall give it a go.

~Matt

 

[MattBaldry]

Tried, tested, works.

Thanks Arnelpg. My append query that kept failing was so close to yours, I just had the [Copy To] parameter in the wrong place.

I shall now make a little form and function to create this. It will save me lots of time.

~Matt