Duplicate subforms content and key link to another master table key

[SirTKC]

Hi,

Here is the following scenario.

I do have a master table with related subforms from wich I need to
create a revision.
But I need to duplicate the content of the subforms and link them to
the new entry in the master table.

Each string possess its unique identifier number.

So in this case I have a master table with the following structure
(simplified)

DesiID = Design Unique Identifier
Pnum = Project number
PPro = Prototype Number
PRev = Revision Number
FieldN = other fields

And subforms attached to the master table by the Unique identifier

PartID = Part Unique Identifier
DesiID = Link to master table
FieldN = Other Fields

Both strings are attached ONLY by the DesiID
I have the code (see code snippet) that allow me to create the
duplicate I need but it keeps attaching it to the original DesiID
instead of the new freshly created one by the revision generator.
I am scr(..) with this because all new entries remains attached to the
original DesiID instead of the new one... Thats my problem !

This is where I need a hand !

Thanks

CODE
================================================== ==========================
With Me.RecordsetClone
.AddNew
'
!PRev = Nz(DMax("PRev", "Design", "[PNum]=" & Me.PNum
& " And [PPro]= " & Me.PPro), 0) + 1
!ProjID = Me.ProjID
!PNum = Me.PNum
!PPro = Me.PPro

.Update

Dim strSQL As String
strSQL = "insert into Design_Struct (DesiID, FieldN) Select DesiID,
FieldN FROM Design_Struct WHERE Design_Struct.DesiID = " & Me.DesiID &
""
CurrentDb.Execute strSQL

Me.Bookmark = .LastModified
Me.Requery: Me.Refresh

End With

[Bruce]

On May 12, 10:06 pm, SirTKC <[e-mail]:

Hi,

Here is the following scenario.

I do have a master table with related subforms from wich I need to
create a revision.
But I need to duplicate the content of the subforms and link them to
the new entry in the master table.

Each string possess its unique identifier number.

So in this case I have a master table with the following structure
(simplified)

DesiID = Design Unique Identifier
Pnum = Project number
PPro = Prototype Number
PRev = Revision Number
FieldN = other fields

And subforms attached to the master table by the Unique identifier

PartID = Part Unique Identifier
DesiID = Link to master table
FieldN = Other Fields

Both strings are attached ONLY by the DesiID

I have the code (see code snippet) that allow me to create the
duplicate I need but it keeps attaching it to the original DesiID
instead of the new freshly created one by the revision generator.
I am scr(..) with this because all new entries remains attached to the
original DesiID instead of the new one... Thats my problem !

This is where I need a hand !

Thanks

CODE
================================================== ==========================
With Me.RecordsetClone
.AddNew
'
!PRev = Nz(DMax("PRev", "Design", "[PNum]=" & Me.PNum
& " And [PPro]= " & Me.PPro), 0) + 1
!ProjID = Me.ProjID
!PNum = Me.PNum
!PPro = Me.PPro

.Update

Dim strSQL As String
strSQL = "insert into Design_Struct (DesiID, FieldN) Select DesiID,
FieldN FROM Design_Struct WHERE Design_Struct.DesiID = " & Me.DesiID &
""
CurrentDb.Execute strSQL

Me.Bookmark = .LastModified
Me.Requery: Me.Refresh

End With

If I am understanding you correctly, your .Addnew....Update code adds
a new record to the master table, and your SQL adds a new record to
the related table. To make this work you're either going to have to
set the form's current record to your newly created record between
the .Update and the strSQL or you're going to have to save the value
of your newly created DesiID to insert into your SQL, e.g.
Dim lngDesiID as Long

With Me.RecordsetClone
.AddNew
'
!PRev = Nz(DMax("PRev", "Design", "[PNum]=" & Me.PNum
& " And [PPro]= " & Me.PPro), 0) + 1
!ProjID = Me.ProjID
!PNum = Me.PNum
!PPro = Me.PPro
lngDesiID = !DesiID ' save value of new autogenerated
DesiID
.Update

Dim strSQL As String
strSQL = "insert into Design_Struct (DesiID, FieldN) Select DesiID,
FieldN FROM Design_Struct WHERE Design_Struct.DesiID = " & lngDesiID &
""