copy records mainform, subform1 & subform2

[Wout]

Hello there,

i have a main form to define products in.
i have a subform1 to fill in the related materials
i have a subform2 to fill in the machines to process the materials

subform1 is related to mainform through "ProductId" , subform2 is
related to subform1 through "MaterialId "
both subforms are displayed on the mainform.
i need to find out how i can copy a product-record including all
related records so i can make small adjustments in the "new" product
and save it as a new product.

i hope i made a bit clear what i mean ( my english/ jargon is not so
well)

any suggestions are welcome!!

thanks a lot in advance

Wout

[gsb58]

Hi!

Here's a procedure that works fine between two forms, a master and a
detail.
Why not just try and add a second sql string and see if u can update
the second subform. Haven't tried it my self

(Code worked out by Al Kallal)

Me.Name

Private Sub cmdCopy_Click()
On Error GoTo Err_Copy

Dim rs As DAO.Recordset
Dim sqlNew As String
Dim lngNewOrderID as Long

'Force a diskwrite of current record
Me.Refresh

Set rs = Me.RecordsetClo ne

With rs
..AddNew
!CustID = Me!CustID
!EmployeeID = Me!EmployeeID
!CarID = Me!CarID
..Update
End With

'Now get ID of this new order just added.
'Simply move to last record changed
rs.Bookmark = rs.LastModified
lngNewOrderID = rs!OrderID

'Now copy details to NEW OrderID
sqlNew = "INSERT INTO
tblOrderDetail( ItemID,Price,Am ount,Period,Art ,Anl,OrderID) " & _
"SELECT ItemID,Price,Am ount,Period,Art ,Anl," & lngNewOrderID & _
'Please notice: NO SPACE AFTER LAST FIELD but
'SPACE BEFORE NEXT FIELD
" FROM tblOrderDetails " & _
"WHERE OrderID = " & Me!OrderID
CurrentDb.Execu te sqlNew, dbFailOnError
Wout skrev:

Hello there,

i have a main form to define products in.
i have a subform1 to fill in the related materials
i have a subform2 to fill in the machines to process the materials

subform1 is related to mainform through "ProductId" , subform2 is
related to subform1 through "MaterialId "
both subforms are displayed on the mainform.
i need to find out how i can copy a product-record including all
related records so i can make small adjustments in the "new" product
and save it as a new product.

i hope i made a bit clear what i mean ( my english/ jargon is not so
well)

any suggestions are welcome!!

thanks a lot in advance

Wout

[Wout]

Hi there,

many thanks for your reply! i tried it, but unfortunately it won't
work.
maybe it helps if i tell you that the records in subform2 are the
details of the active record in subform1.
i made this work by creating an unbound textbox (called "MaterialId ")
which recordsource is set to "=Subform1!Mate rialId"
so if you click somewhere in subform1, subform2 displays the
corresponding processing machines

thanks in advance.....

Wout

[Wout]

Hi,

I have been searching and trying to created the following code. the
idea is to loop through subform1 and execute for every record an append
query to copy the records from subform2. the code creates a new record
in the main form, copies the related 1st record from subform1 and
copies the related records from subform2. if subform1 has more then one
record these records are not copied (the related records in subform2
are not copied either)
I think it must be something in the loop but i haven't found out what
it is yet.....

Any help is highly appreciated!!!
Thanks in advance....

[Wout]

Euhhh..., sorry here's the code:

Private Sub cmdCopy_Click()
On Error GoTo Err_Handler
Dim db As DAO.database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim lngPrId, lngVAL, lngGrId As Long

Set db = CurrentDb
Set rs = Me.frmGrondstof fen.Form.Record setClone
If Me.Dirty Then
Me.Dirty = False
End If

If Me.NewRecord Then
MsgBox "Selecteer een bestaand product."
Else
'kopieer hoofdrecord en voeg toe aan kloon
With Me.RecordsetClo ne
.AddNew
!PrNaam = Me.PrNaam
.Update
'bewaar de nieuwe primaire sleutel waarde
.Bookmark = .LastModified
lngPrId = !PrId

'kopieer gerelateerde records uit sfrmGrondstoffe n
If rs.RecordCount > 0 Then
lngVAL = rs.RecordCount
MsgBox "Er worden " & lngVAL & " grondstoffen
gekopieerd"

With rs
rs.MoveFirst
Do Until rs.EOF
With rs
.AddNew
!GrNaam =
Forms!frmProduc ten!frmGrondsto ffen!GrNaam
!PrId = lngPrId
.Update
' bewaar de nieuwe primaire sleutel waarde
.Bookmark = .LastModified
lngGrId = !GrId

strSQL = "INSERT INTO tblBewerkingen ( GrId, BewNaam )" &
"SELECT " & lngGrId & " As GrId, tblBewerkingen. BewNaam " & " FROM
tblBewerkingen WHERE ((([GrId])= " & Me!frmGrondstof fen!GrId & "));"
db.Execute strSQL
End With
rs.MoveNext
Loop
End With
Set rs = Nothing
Set db = Nothing
Else
MsgBox "Alleen productgegevens gekopieerd, er waren geen
gerelateerde grondstoffen en bewerkingen."
End If

'geef de gekopieerde record weer
Me.Bookmark = .LastModified
Me.frmGrondstof fen.Requery
Me.frmBewerking en.Requery
End With
End If

exit_handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description , ,
"cmdKopiëren_Cl ick"
Resume exit_handler

End Sub