AddNew Do...Loop with captured Primary Key

  • Thread starter Thread starter Gary D. Rezek
  • Start date Start date
G

Gary D. Rezek

Hi All,
I know the design of this db is convoluted, but I don't have any recourse but to use it as is.
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 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
!ProbeLocus = rstList![ProbeLocus]
!MotherBandsHigh = rst![HighBand]
!MotherBandsLow = rst![LowBand]
!Included = True
End With
..MoveNext
Loop

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

Could someone please show me how this could be done, short of a re-design?
Thank you in advance.

gdr
 
Since LabDataPKID is a Foreign Key in the new records, it
can not be an autonumber field in the many side (second)
table. It should be a Long number type field. It might
even be a good idea to name it LabDataFK.
--
Marsh
MVP [MS Access]



Hi All,
I know the design of this db is convoluted, but I don't have any recourse but to use it as is.
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 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
!ProbeLocus = rstList![ProbeLocus]
!MotherBandsHigh = rst![HighBand]
!MotherBandsLow = rst![LowBand]
!Included = True
End With
.MoveNext
Loop

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

Could someone please show me how this could be done, short of a re-design?
Thank you in advance.

gdr
Click to expand...
 
Hi Marshall,
Yeah, I know and it is a Number/Long Integer when it is in its' "foreign" table. Just bad verbiage on my part. I should have
stated that all Primary keys are Autonumbers.
Anyway, I've decided to AddNew table by table and forego the "shortcut" of just using the query. I know I can do that....just
takes more typing and bookkeeping.
Thank you.

gdr


Marshall Barton said:
Since LabDataPKID is a Foreign Key in the new records, it
can not be an autonumber field in the many side (second)
table. It should be a Long number type field. It might
even be a good idea to name it LabDataFK.
Click to expand...
<snip>
 
Back
Top