Join key on table not in recordset

[Guest]

I am attempting to write an Access 2000 app. There is one master table and
several tables that primarily support normalization issues. The master table
(Tickets) has the following fields: ticketID (PK), remedyID (INT),
caveatID(I), primPersonID(I), altPersonID(I), coreAreaID(I), projectID(I).
Each of the ID fields refer to other tables that contain the strings (unique
in there respective tables) that are actually displayed on the form, except
the remedyID field. That is simply a number that 'links' to another system
(conceptually only; I am not allowed to access the database system that this
would really link to).

The RecordSource for the form is a query which looks like the following:
select T.ticketID, T.remedyID, C.caveatStr, P.personLast, P1.personLast,
CA.coreName, Proj.projectName
from Caveats as C inner join (
Persons as P inner join (
Persons_1 as P1 inner join (
CoreArea as CA inner join (
Projects as Proj inner join
Tickets as T
on Proj.projectID = T.projectID)
on CA.coreID = T.coreID)
on P1.personID = T.altPersonID)
on P.personID = T.primePersonID)
on C.caveatID = T.caveatID;

(I hope I've copied that correctly. If not, apologies in advance.) The above
displays the data correctly, both in datasheet view (when run by executing
the SQL from SQL View) and form view. However, when I try adding a record, as
soon as I attempt to type anything in the one field that comes directly from
this master table (remedyID), I get the following errror on the status bar:

Cannot add record(s); join key on table 'Tickets' not in recordset

What does this mean and how to fix it? Does it mean that I have to include
the join keys with each table in my recordset, even if I am not displaying
them? And why does this only appear for the only field that is not retrieved
as a result of a join?

Any help will be greatly appreciated. TIA.

 

[John Vinson]

The RecordSource for the form is a query which looks like the following:
select T.ticketID, T.remedyID, C.caveatStr, P.personLast, P1.personLast,
CA.coreName, Proj.projectName

I would strongly suggest a different approach. Rather than basing your
Form on a "grand master query" linking all these tables, consider
basing it just on your main table, and using Combo Boxes based on the
related tables. These can easily be set to *store* the ID number but
to *display* the text value.


John W. Vinson[MVP]

 

[Guest]

Thanks for the reply, John. I had headed off in that direction but then come
back to the 'grand master query' approach for no other reason (not a good
one, I admit)than I didn't like the look and I was writing too many NotInList
event procedures.

But my original question still is unanswered: Why does that error appear
when I try to enter anything in the one field that is not the result of a
join? Is that message being generated as a result of the Form reacting to a
change vs. the field? In which case, why doesn't the error occur as soon as
the form is displayed in a mode that would allow entry of a new record?
Certainly, the sequence of events would not lead one (well, at least not me)
to add all of the join fields from the master table to the query (which does,
by the way, resolve the problem).

TIA.

 

[John Vinson]

But my original question still is unanswered: Why does that error appear
when I try to enter anything in the one field that is not the result of a
join? Is that message being generated as a result of the Form reacting to a
change vs. the field? In which case, why doesn't the error occur as soon as
the form is displayed in a mode that would allow entry of a new record?
Certainly, the sequence of events would not lead one (well, at least not me)
to add all of the join fields from the master table to the query (which does,
by the way, resolve the problem).

In order to add a record to the master table with a new value in its
foreign key field to one of the linked tables, you must be able to
assign a value to BOTH the foreign key field in the master table and
the primary key field in the linked table; so you need both fields
included in the query.

John W. Vinson[MVP]