468,780 Members | 2,200 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,780 developers. It's quick & easy.

copy records mainform, subform1 & subform2

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

Mar 27 '06 #1
4 1500
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.RecordsetClone

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,Amount,Period,Art,Anl, OrderID) " & _
"SELECT ItemID,Price,Amount,Period,Art,Anl," & lngNewOrderID & _
'Please notice: NO SPACE AFTER LAST FIELD but
'SPACE BEFORE NEXT FIELD
" FROM tblOrderDetails " & _
"WHERE OrderID = " & Me!OrderID
CurrentDb.Execute 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


Mar 28 '06 #2
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!MaterialId"
so if you click somewhere in subform1, subform2 displays the
corresponding processing machines

thanks in advance.....

Wout

Mar 30 '06 #3
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....

Apr 3 '06 #4
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.frmGrondstoffen.Form.RecordsetClone
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.RecordsetClone
.AddNew
!PrNaam = Me.PrNaam
.Update
'bewaar de nieuwe primaire sleutel waarde
.Bookmark = .LastModified
lngPrId = !PrId

'kopieer gerelateerde records uit sfrmGrondstoffen
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!frmProducten!frmGrondstoffen!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!frmGrondstoffen!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.frmGrondstoffen.Requery
Me.frmBewerkingen.Requery
End With
End If

exit_handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdKopiŽren_Click"
Resume exit_handler

End Sub

Apr 3 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

19 posts views Thread by davidgordon | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.