"Can't add record(s); join key of table "SUBFORMS UNDERLYING TABLE NAME" not in record set" Error me

[Dave the wave]

I have a subform linked to a main form and populated by a query that uses
the Link Master field on the main form as the criteria. The query is based
on a separate table than the main form. The query pulls fields from the
second table using a current value from the main form as the criteria.

Whenever I try to add a new record I get the error message shown in the
subject.

The tables relationship is a one-to-many where the Main form has the one
side and the Subform has the many.

There exists some records in the second table which pull up as expected, but
I can not add any records. I click on the >* (new record) button and the
subform won't allow me to enter data into any of the fields.

I searched the MS KB but could not find any help on this issue. I appreciate
any insight.

 

[Dirk Goldgar]

I have a subform linked to a main form and populated by a query that
uses the Link Master field on the main form as the criteria. The
query is based on a separate table than the main form. The query
pulls fields from the second table using a current value from the
main form as the criteria.

Whenever I try to add a new record I get the error message shown in
the subject.

The tables relationship is a one-to-many where the Main form has the
one side and the Subform has the many.

There exists some records in the second table which pull up as
expected, but I can not add any records. I click on the >* (new
record) button and the subform won't allow me to enter data into any
of the fields.

I searched the MS KB but could not find any help on this issue. I
appreciate any insight.

Please post ...

+ the SQL of the subform's RecordSource query

+ the Link Master and Link Child Fields of the
subform control on the main form.

 

[Dave the wave]

SQL of the subform's RecordSource query:

SELECT DISTINCTROW FiveWhyInvestiagtion.FiveWhyID, Repacks.RepackID,
FiveWhyInvestiagtion.FiveWhyPhenomenonLink,
FiveWhyInvestiagtion.FiveWhyPhenomenonText,
FiveWhyInvestiagtion.FiveWhyLink1, FiveWhyInvestiagtion.FiveWhyText1,
FiveWhyInvestiagtion.FiveWhyCause1, FiveWhyInvestiagtion.FiveWhyLink2,
FiveWhyInvestiagtion.FiveWhyText2, FiveWhyInvestiagtion.FiveWhyCause2,
FiveWhyInvestiagtion.FiveWhyLink3, FiveWhyInvestiagtion.FiveWhyText3,
FiveWhyInvestiagtion.FiveWhyCause3, FiveWhyInvestiagtion.FiveWhyLink4,
FiveWhyInvestiagtion.FiveWhyText4, FiveWhyInvestiagtion.FiveWhyCause4,
FiveWhyInvestiagtion.FiveWhyLink5, FiveWhyInvestiagtion.FiveWhyText5,
FiveWhyInvestiagtion.FiveWhyCause5, FiveWhyInvestiagtion.FiveWhyActionsLink,
FiveWhyInvestiagtion.FiveWhyActions
FROM Repacks INNER JOIN FiveWhyInvestiagtion ON Repacks.RepackID =
FiveWhyInvestiagtion.RepackID
WHERE (((Repacks.RepackID)=[forms]![frmRepacks]![RepackID]));

Link Master and Link Child Fields:

Both fields are "RepackID". One comes from the Repacks table
(Repacks.RepacksID) and the other from the FiveWhyInvestiagtion table
(FiveWhyInvestiagtion.RepacksID)

There is a one to many relationship established for these 2 tables.

Thanks for your help.

 

[Dirk Goldgar]

There are a variety of odd things there, so I'm not sure what you should
be doing. You're getting the error message, it seems to me, because you
don't have the field FiveWhyInvestiagtion.RepackID in the query's result
set. But why are you doing an inner join in your query at all when the
only field you're bringing in from table Repacks is the join key,
RepackID? Why don't you have just

SELECT
FiveWhyID, RepackID,
FiveWhyPhenomenonLink, FiveWhyPhenomenonText,
FiveWhyLink1, FiveWhyText1,
FiveWhyCause1, FiveWhyLink2,
FiveWhyText2, FiveWhyCause2,
FiveWhyLink3, FiveWhyText3,
FiveWhyCause3, FiveWhyLink4,
FiveWhyText4, FiveWhyCause4,
FiveWhyLink5, FiveWhyText5,
FiveWhyCause5, FiveWhyActionsLink,
FiveWhyActions
FROM FiveWhyInvestiagtion
WHERE RepackID=[forms]![frmRepacks]![RepackID];

? And further, if frmRepacks, based on table Repacks, is the main form
and you want to show all records from table FiveWhyInvestiagtion that
have a RepackID that matches the main form's RepackID, then why do you
have the query criterion "WHERE
RepackID=[forms]![frmRepacks]![RepackID]" at all? That kind of
filtering is what the Link Master and Child Fields is supposed to do.
Why don't you just specify RepackID (from table Repacks) as the Link
Master Field, and RepackID (from table FiveWhyInvestiagtion) as the Link
Child Field, and leave of the WHERE clause from the query?

Am I missing something?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

SQL of the subform's RecordSource query:

SELECT DISTINCTROW FiveWhyInvestiagtion.FiveWhyID, Repacks.RepackID,
FiveWhyInvestiagtion.FiveWhyPhenomenonLink,
FiveWhyInvestiagtion.FiveWhyPhenomenonText,
FiveWhyInvestiagtion.FiveWhyLink1, FiveWhyInvestiagtion.FiveWhyText1,
FiveWhyInvestiagtion.FiveWhyCause1, FiveWhyInvestiagtion.FiveWhyLink2,
FiveWhyInvestiagtion.FiveWhyText2, FiveWhyInvestiagtion.FiveWhyCause2,
FiveWhyInvestiagtion.FiveWhyLink3, FiveWhyInvestiagtion.FiveWhyText3,
FiveWhyInvestiagtion.FiveWhyCause3, FiveWhyInvestiagtion.FiveWhyLink4,
FiveWhyInvestiagtion.FiveWhyText4, FiveWhyInvestiagtion.FiveWhyCause4,
FiveWhyInvestiagtion.FiveWhyLink5, FiveWhyInvestiagtion.FiveWhyText5,
FiveWhyInvestiagtion.FiveWhyCause5,
FiveWhyInvestiagtion.FiveWhyActionsLink,
FiveWhyInvestiagtion.FiveWhyActions
FROM Repacks INNER JOIN FiveWhyInvestiagtion ON Repacks.RepackID =
FiveWhyInvestiagtion.RepackID
WHERE (((Repacks.RepackID)=[forms]![frmRepacks]![RepackID]));

Link Master and Link Child Fields:

Both fields are "RepackID". One comes from the Repacks table
(Repacks.RepacksID) and the other from the FiveWhyInvestiagtion table
(FiveWhyInvestiagtion.RepacksID)

There is a one to many relationship established for these 2 tables.

Thanks for your help.

Please post ...

+ the SQL of the subform's RecordSource query

+ the Link Master and Link Child Fields of the
subform control on the main form.