duplicating a record AND it's related table data

[toon10]

Hi

I have an access database which has various related tables. I have parts which have many operations, each operation could have many sub operations and each sub operation has a timing record attached to it.

What the user wants my vb app to do is to select a part on my parts maintenance screen and choose a "copy" menu option. This facility needs to copy the part selected, prompt the user for a new part number but have all of the original parts operation, sub ops, etc copied to new records.

At present, my routine copies the selected part and propts for a new part number and saves it to the database. It doesn't however, copy any related table data from the original part record. Is there an easy way to do this without having a load of recordsets opening, looping and writing to the database? I know there is a recordset clone option but this doesn't seem to clone any related table data.

Here's my code so far...

Private Sub UpdatePart()
On Error GoTo Err_Log
Dim Message, Title
Set db = New Connection
db.CursorLocation = adUseClient
db.Open DataBaseCON 'open the database connection
Dim OpRS As Recordset

Message = "Enter New Part Number"
Title = "Duplicate Part: " & adoPrimaryRS.Fields(2) 'part number
varPartNo = InputBox(Message, Title) 'new part number is now stored in varPartNo

If vbCancel = True Then 'cancel the copy record operation
Exit Sub 'drop out of routine
End If

varDesc = txtFields(0) 'description is stored in varDesc
varPID = txtFields(2) 'product id is now stored in varPID

db.Execute " INSERT INTO Parts ([PartID],[Description],[ProdID]) VALUES ('" & varPartNo & "', '" & varDesc & "', '" & varPID & "');" 'update part table with duplicate record

Err_Log:
'MsgBox Err.Number & " " & Err.Description, vbCritical, "Error"
Resume Next
End Sub

Thanks
Andrew

 

[gmmastros]

I have a couple suggestions for you.

1) Don't open the database connection until you are ready for it. If your user cancel the operation, you are not closing the database connection. Instead, you should get information from the user, connect to the database, do what you need to do, and then close the connection.

2) It's a little difficult for me to understand what you are trying to accomplish here. You can insert values in to a table based on other values. The syntax is something like this...

Insert Into Table(Field1, Field2, field3)
Select Field1, Field2, Field2
From Table Where IdField = <some Id>

In fact, using this method, you can insert multiple records at once, it all depends on your where clause.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[toon10]

I've taken your advice on the database connection. Unfortunately, it's not as easy as inserting into multiple tables, I need to copy from multiple tables too. For example I have a part record which may have 25 operation records linked to it and each operation record could have 100 sub operation records, each with a timing record attacthed to it. There is masses of data here and it's getting complex so I wanted to know if there was an easy way of being able to select a part record, copy it and paste it with a new number but also have all it's sub records copied at the same time and pasted under the new number too.