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

  • Thread starter Thread starter Dave the wave
  • Start date Start date
D

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.
 
Dave the wave said:
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.
Click to expand...

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.
 
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.
 
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)


Dave the wave said:
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 said:
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.
Click to expand...
Click to expand...
 
I built the subform "sfrm5why" based on a querry "qryAssigned5Y" which
selected records from "FiveWhyInvestiagtion" where the RepackID
=Repacks.RepackID. (That's where the inner join came from.)

When I built the mainform "frmRepackwith5Y" and inset the "sfrm5why" I setup
the child/master fields as you described without modifying the subforms
querry. If I understand your message, this is overkill? The child/master
links would replace the querry's criteria? Or, make it redundant?

I first modified the querry so there was no criteria, nor a Repacks.RepackID
field, but I left the Repacks Table showing in the top portion of the querry
builder which also showed the relationship between the two tables
"FiveWhyInvestiagtion" and "Repacks". I tried to run the form and it still
did not work. When I checked the SQL I noticed the inner join was still
listed even though the criteria and Repacks.RepackID field had been deleted.

I then removed the table "Repacks" from the querry builder window which gave
the SQL statement listed below.

UPDATED SQL:
SELECT DISTINCTROW FiveWhyInvestiagtion.FiveWhyID,
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 FiveWhyInvestiagtion;

I was then able to add records using the subform.

Since I only removed fields from the underlying querry, I don't understand
why Access couldn't see the join key before? Both the master and child
fields (or the join keys) were both present in the querry.

So, I can rely on the child/master link for filtering records as well as
linking. If the subform was shown as continuous forms, or datasheet, the
subform records would be limited to those records with child values matching
the master value?

Thanks very much fro your help and your time.


Dirk Goldgar said:
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)


Dave the wave said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Dave the wave said:
I built the subform "sfrm5why" based on a querry "qryAssigned5Y" which
selected records from "FiveWhyInvestiagtion" where the RepackID
=Repacks.RepackID. (That's where the inner join came from.)

When I built the mainform "frmRepackwith5Y" and inset the "sfrm5why"
I setup the child/master fields as you described without modifying
the subforms querry. If I understand your message, this is overkill?
The child/master links would replace the querry's criteria? Or, make
it redundant?
Click to expand...

The WHERE clause in the query is redundant if the same filtering is
being done by the Link Master/Child Fields. And if you leave that WHERE
clause in, you have to explicitly requery the subform every time you go
to a new record on the main form. You don't need to do that if you
leave the WHERE clause out and use the master/child links to do the
querying.
I first modified the querry so there was no criteria, nor a
Repacks.RepackID field, but I left the Repacks Table showing in the
top portion of the querry builder which also showed the relationship
between the two tables "FiveWhyInvestiagtion" and "Repacks". I tried
to run the form and it still did not work. When I checked the SQL I
noticed the inner join was still listed even though the criteria and
Repacks.RepackID field had been deleted.
Click to expand...

The join was still there and defined as part of the query even though
you deleted the field from the query's results grid. Under other
circumstances, there could be a reason for that, but you didn't really
want that join here.
I then removed the table "Repacks" from the querry builder window
which gave the SQL statement listed below.

UPDATED SQL:
SELECT DISTINCTROW FiveWhyInvestiagtion.FiveWhyID,
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 FiveWhyInvestiagtion;

I was then able to add records using the subform.
Click to expand...

But where is the field FiveWhyInvestiagtion.RepackID? I'd think that
would still need to be included in the query for the master/child fields
to work. You should add this field -- *not* the one from table Repacks,
which isn't even included any more -- to your query.
Since I only removed fields from the underlying querry, I don't
understand why Access couldn't see the join key before? Both the
master and child fields (or the join keys) were both present in the
querry.
Click to expand...

The problem was that your query as originally defined didn't include the
field FiveWhyInvestiagtion.RepackID in the result set; it only included
the corresponding field from Repacks. But you're trying to add records
to FiveWhyInvestiagtion, so you need the jopin field from that table to
be included.
So, I can rely on the child/master link for filtering records as well
as linking. If the subform was shown as continuous forms, or
datasheet, the subform records would be limited to those records with
child values matching the master value?
Click to expand...

Yes, that's right.
Thanks very much fro your help and your time.
Click to expand...

You're welcome.
 
Back
Top