Duplicate subforms content and key link to another master table key

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • SirTKC
    #1

    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.RecordsetClo ne
    .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.D esiID = " & Me.DesiID &
    ""
    CurrentDb.Execu te strSQL

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

    End With
    Tags: None
    • Bruce
      #2
      Re: Duplicate subforms content and key link to another master tablekey

      On May 12, 10:06 pm, SirTKC <andre.artsyst. ..@gmail.comwro te:
      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.RecordsetClo ne
      .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.D esiID = " & Me.DesiID &
      ""
      CurrentDb.Execu te strSQL
      >
      Me.Bookmark = .LastModified
      Me.Requery: Me.Refresh
      >
      End With
      If I am understanding you correctly, your .Addnew....Upda te 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.RecordsetClo ne
      .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.D esiID = " & lngDesiID &
      ""

        Comment

        Previous template Next
        Working...