Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AddNew Do...Loop with captured Primary Key (A2k3)

    Edited by HansV - inserted <!t>[tab]<!/t> tags to preserve indentation

    Hi All,
    I know the design of this db is convoluted, but I don't have any recourse but to use it as is. Also I can do this in a "chain" of AddNew's to each individual table, but I tried it this way, failed, and now want to know how/if it can be done.
    I'm using a Do...Loop along with AddNew to enter previous records into a 3 table query. These previous records are treated as if
    it is new/different data, it just looks like the same data.
    All Primary keys are AutoNumber.
    One of the problems of this design is that the Primary Key of the "first" table (which is a foreign key in the "second" table)
    is, as far as I can tell, really one-to-one with the Primary Key of the "second" table rather that one-to-many that it looks
    like.

    DataDetails => "first" table -- DataDetailsPKID (pk) (DataDetails.DataDetailsPKID is not used in the query)
    LabData => "second" table--LabDataPKID (pk); DataDetailsPKID (fk) (LabData.DataDetailsPKID is used in the query)
    TestData => "third" table -- TestDataPKID (pk); LabDataPKID (fk) (TestData.LabDataPKID is used in the query)

    <<example of what is needed>>
    With rstList
    Do Until .EOF
    With rstSub
    .AddNew
    !DataDetailsPKID = rstClone!DataDetailsPKID 'this remains the same through each of the loops
    !LabDataPKID = LabDataPKID 'this should remain the same through each of the loops
    !AnonField1 = rstList![AnonField1]
    !AnonField2 = rst![AnonField2]
    !AnonField3 = rst![AnonField3]
    End With
    .MoveNext
    Loop
    End With

    I can get the just entered LabData record's primary key on the first loop, however because of the design, that pk
    must_stay_the_same through each loop. That is my problem, entering that pk into the LabDataPKID field after the first loop, when
    it wants to just auto increment.
    I get hit with the "Current field must match the join key '?' in the table that serves as the 'one' side ....etc...", but to me
    that join key should be there; i.e. the pk of the record that I just added. So...

    <<What I've tried>>
    i = 1
    With rstList
    Do Until .EOF
    With rstSub
    .AddNew
    !DataDetailsPKID = rstClone!DataDetailsPKID 'this remains the same through each of the loops
    If i > 1 Then
    !LabDataPKID = intLabDataPKID 'this is trying to enter that intial LabDataPKID
    Else
    'do nothing
    End If
    !AnonField1 = rstList![AnonField1]
    !AnonField2 = rst![AnonField2]
    !AnonField3 = rst![AnonField3]
    If i <= 1 Then
    intLabDataPKID = !LabDataPKID 'this gets the initial LabDataPKID
    Else
    'do nothing
    End If .Update
    End With
    i = i + 1
    .MoveNext
    Loop
    End With

    Could someone please show me how this could be done?
    Thank you in advance.

    gdr
    --
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: AddNew Do...Loop with captured Primary Key (A2k3)

    Instead of trying to add records to a recordset based on the three tables (through the query), add records to each of the three tables separately. Fist, add a record to DataDetails, and capture the DataDetailsPKID. Then add a record to LabData, set DataDetailsPKID and capture LabDataPKID. Finally add a record to TestData and set LabDataPKID.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AddNew Do...Loop with captured Primary Key (A2k3)

    Hi Hans,
    Although it is near impossilbe to tell by the way I wrote the initial post, <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15> I can and have set it up to addnew to each table separately.
    What I was trying to say is that I tried to do it with the query, that failed, but am interested in if it can be done that way.
    I've gotta stop posting so dang late in the evening.
    Thank you for your response.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: AddNew Do...Loop with captured Primary Key (A2k3)

    No, I don't think it would be possible to use the query based on the three tables for this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •