Cannot add record(s); join key of table . . . .

[JimJones]

Hello,

I am making some good progress with the help I've gotten here over several months of trying to put this project together.

I now have a form based on a query which draws from 4 tables.
I'm told that if I try this that I may not be able to update my tables.
Sure enough, there is one table which I cannot update.

But, what I just realized is that when the form wizard used a query to base your form on, it basically still only uses the table themselves and knows how to manipulate the links and so on, when creating the form/suborm. If I'm wrong about that concept, please let me know.

OK the Problem:

A table I call ServiceNotes depends on a unique Id in a table that depends on another unique id in another table, and so on, and so on.
That's 4 tables all together.

I get the following error not right when I click the new record button, when I actually begin to type in data, which is also on the same line as "autonumber":

Cannot add record(s); join key of table 'ServiceNotes' not in Recordset

AND, here is the Record Source for the second subform on the form,
which draws the unique Id from the first of 2 subforms:

SELECT [Service].[ServiceTicket], [ServiceNotes].[TypeofService], [ServiceNotes].[Qty], [ServiceNotes].[Description], [ServiceNotes].[Price], [ServiceNotes].[Ext], [ServiceNotes].[Labor], [ServiceNotes].[Notes], [Service].[UniqueCarID] FROM ([Service] INNER JOIN [ServiceNotes] ON [Service].[ServiceTicket] =[ServiceNotes].[ServiceTicket])


Can you help me make that second subform updateable? Which would ultimately update the 4th table?

Thanks,
Jim

 

[sharon attwood]

Just a quick check is that you have no duplicates on the 'one' side. I don't know enough about the scripts to help you there, but when mine would not update, this was the problem.

 

[JimJones]

Just a quick check is that you have no duplicates on the 'one' side. I don't know enough about the scripts to help you there, but when mine would not update, this was the problem.

Sharon,

Are you able to decipher that from the Select statement I provided?

Because there are not duplicates allowed on any of the tables designated with a one-side primary key.

Jim

 

[suepowell]

Hi

Looking at the error message I wonder if you have missed a field that should be in the recordset of the form, even though it will not be used.

Try adding all of the fields from all of the tables in your query grid, and if this solves the problem, then remove the excess fields untill you find the one you really need to have there. It doesn't matter if you leave the extra fields in the query grid, too many is better than too few.

If this doesn't solve the problem post a sample of your database and I wil have a look for you.

Sue Powell

 

[Pat Hartman]

When you create a query that is based on a 1-many join, you need the join key field to be selected from the many-side table rather than the 1-side table. Therefore, replace [Service].[ServiceTicket] with [ServiceNotes].[ServiceTicket]