467,895 Members | 1,373 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Duplicate subforms content and key link to another master table key

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
Jun 27 '08 #1
  • viewed: 1492
Share:
1 Reply
On May 12, 10:06 pm, SirTKC <andre.artsyst...@gmail.comwrote:
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 &
""
Jun 27 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Jeremiah J. Burton | last post: by
3 posts views Thread by Diana Gard | last post: by
9 posts views Thread by Zeeshan Iqbal via AccessMonster.com | last post: by
7 posts views Thread by jaad | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.