On May 12, 10:06 pm, SirTKC <andre.artsyst...@[EMAIL PROTECTED]
> wrote:
> 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 &
""
|
