Copy record and linked data

[Appollo14]

Hi,

I need to be able to copy a record in a table (which i can do) and also copy a related record in another table. This needs to be based on the current form and a form that is displayed on command. For ease of use this needs to be done from a button.

Any help appreciated.

Noel.

 

[Remou]

Hi
I am not sure I understand your question. Do you wish to duplicate all the data from a form, including the subform, to another, new record? Does the subform contain just one record, as you seem to imply?

 

[Appollo14]

Hi, yes i want to duplicate the whole record. However, some of the data is stored on another form that displays on command and so is not really a sub form. the data is linked via the primary key from the main form.

Hope this clarrifies it.

Regards,
Noel.

 

[Remou]

Hi
Here is one idea. It is quite rough:

Set db = CurrentDb

Set rsMain = db.OpenRecordset("Select Field2, Field3 " _ 
& "From Table1 Where Key = " & Me!Key)

Set rsSub = db.OpenRecordset("Select Field2, Field3 " _ 
& "From Table2 Where Key = " & Me![Table2 subform].Form!Key)

'Table1: Numeric Key, Field2 is Text, Field3 is Date/Time
Table1Key = DMax("Key", "Table1") + 1
strSQLMain = "INSERT INTO Table1 (Key, Field2, Field3 ) " _
     & "SELECT " & Table1Key & ", '" & rsMain!Field2 _
     & "',#" & rsMain!Field3 & "#"

'Table2: Numeric Key, Field2 is Text, Field3 is YesNo
strSQLSub = "INSERT INTO Table2 (Key, Field2, Field3 ) " _
     & "SELECT " & Table1Key & ",'" & rsSub!Field2 _
     & "'," & rsSub!Field3 & ""

DoCmd.RunSQL strSQLMain
DoCmd.RunSQL strSQLSub

Me.Requery

 

[Appollo14]

Hi Remou,

Thats probably what i'm after. It looks like i've now got to learn how to use recordsets. I'll have a mess about tonight and let you know how it goes.

Thanks for the help,
Regards,
Noel.

 

[Appollo14]

Hi Remou,

I sat for a couple of hours last night and came up with this based on your code:


Set db = CurrentDb


Set rsDetails = db.OpenRecordset("SELECT JobTitle, Size, Size2, Colours, Colours2, NoPages, NoPages2," & _
"Material, Material2, Finishing, Finishing2, PackagingIdFk, DeliveryIdFk, ReproSpec, ReproSpec2, ProofIdFk," & _
"Quantity, TotalCost, Comment" & " FROM tbQuoteDetails where quoteidfk = " & Me.QuoteID)


strDetailsSQL = "insert into tbquotedetails (JobTitle, Size, Size2, Colours, Colours2, NoPages, NoPages2," & _
"Material, Material2, Finishing, Finishing2, PackagingIdFk, DeliveryIdFk, ReproSpec, ReproSpec2, ProofIdFk," & _
"Quantity, TotalCost, Comment) select rsDetails!JobTitle, rsDetails!Size," & _
"rsDetails!Size2, rsDetails!Colours, rsDetails!Colours2, rsDetails!NoPages, rsDetails!NoPages2," & _
"rsDetails!Material, rsDetails!Material2, rsDetails!Finishing, rsDetails!Finishing2, rsDetails!PackagingIdFk," & _
"rsDetails!DeliveryIdFk, rsDetails!ReproSpec, rsDetails!ReproSpec2, rsDetails!ProofIdFk," & _
"rsDetails!Quantity, rsDetails!TotalCost, rsDetails!Comment"


DoCmd.RunSQL strDetailsSQL
Me.Requery

Which i think is nearly there (tho i could be wrong). When i run this code it asks for the rsDetails! fields as parameters which makes me think that i need to define my connection type and my recordset type. The only problem is i'm clueless as to how to do this apart from the fact that i may need to do some DIM statements.

I'd appreciate it if you could point me in the right direction.

Regards,
Noel.

 

[Appollo14]

Hi Remou,

Dont worry about the above i think i've managed to suss it out.

Thanks for the help thus far tho.

Regards,
Noel.