Copy contents of related table to new record??

I have a database which stores information about certain products.

The products (tblProducts) are made up of product contents
(tblProductContents). The product contents may be used in one or more
products. So tblProducts is on the one side of a one to many with
tblProductContents

tblProducts has two fields: ProductCode(primary key), ProductDescription.

tblProductContents has: ProductContentID (primary key), ProductCode
(foreign key), Description, Size, etc.

I want to be able to copy all of the records in tblProductContents
associated with product A to a new product, product B. Ideally selecting
product A from a dropdown and entering a new product code for B.

I'm really not sure where to start. Any pointers much appreciated.

Thanks

jim

Re: Copy contents of related table to new record??

On Dec 4, 12:15 pm, jim wrote:
> I have a database which stores information about certain products.
>
> The products (tblProducts) are made up of product contents
> (tblProductContents). The product contents may be used in one or more
> products. So tblProducts is on the one side of a one to many with
> tblProductContents
>
> tblProducts has two fields: ProductCode(primary key), ProductDescription.
>
> tblProductContents has: ProductContentID (primary key), ProductCode
> (foreign key), Description, Size, etc.
>
> I want to be able to copy all of the records in tblProductContents
> associated with product A to a new product, product B. Ideally selecting
> product A from a dropdown and entering a new product code for B.
>
> I'm really not sure where to start. Any pointers much appreciated.
>
> Thanks
>
> jim

Hi Jim,
You are going to need to run two queries the first adds the product
and the second will add the contents but will have to get an ID from
the first (assuming that ProductCode is an autonumber. I think you
can only do it in code - I don't think macros will suffice.
This is done on the fly so I hope I dont make any errors, but you will
get the general idea.

sub duplicate(lProduct as long)
Dim sSql as string
Dim lNewID as long
sSql= "INSERT INTO tblProducts(ProductDescription) SELECT '(copy of)
' & ProductDescription FROM tblProducts WHERE ProductCode=" &
lProduct'copy the product
currentproject.connection.execute sSql'execute

lNewID=GetIdentity("tblProducts") 'get the newly created field
sSql= "INSERT INTO tblProductContents(ProductCode, Description, Size,
etc.) " & _
"SELECT " & lNewID & " As ProductCode, Description, Size, etc.
FROM tblProductContents WHERE ProductCode=" & lProduct'copy the
product contents
currentproject.connection.execute sSql'execute
End Sub


Private Function GetIdentity(ByVal sTableName As String) As Variant
'********************************************************
' Author Daniel Tweddell
' Revision Date 3/23/07
'
' Get and return a newly inserted identity from a table
'********************************************************
On Error GoTo Err_Function
Dim rsGetID As ADODB.Recordset
Set rsGetID = New ADODB.Recordset
Set rsGetID = CurrentProject.Connection.Execute("SELECT @@Identity
FROM " & sTableName, 1)
GetIdentity = rsGetID(0) 'get the newly inserted id
rsGetID.Close
Set rsGetID = Nothing
Exit Function
Err_Function:
'ErrHandler Err.Number, Err.Description, Me.Name & "
GetIdentity()", bSilent
End Function

HTH
Dan