Cannot add record(s); join key of table ERROR PLEASE Save me!

[julz77]

Hi all,
I am doing an accounting assignment on access which requires a form with subform for an order which can have many products. The products are in the subform and in the main form I have the order #, customer details date salesperson etc etc. After alot of trouble trying to do the subform and getting it to total, I have now run into the problem of Cannot add record(s); join key of table ' orders' not in recordset. When I try to enter new data into my form!!

I have tried reading up on this with the relationship thing but I just don't get it and its really driving me NUTS
I'm no expert on access and I'm really frustrated now, I have tried looking at the northwind template which absolutely BLEW me away! it is so amazing ! I could never do that! I just want to be able to enter data into my form!



I have attached a word document which shows the relationships and the form which I am using. If anyone has any suggestions PLEASE advise!!

[jzwp11]

Welcome to the forum!

The main form should be based on only the order table. Within the main form, you would use combo boxes to supply the employee, customer and shipper. The subform should be based only on the order detail table and you would use a combo box to populate the products ordered. I am assuming that you used queries that joined multiple tables and based your forms on the queries.

Additionally, you would not have the subtotal field in the order detail table as calculated values are generally not stored (you calculate it on the fly when you need the subtotal in forms, reports or queries).

[julz77]

Hi,

The form is based on tables only, not queries, I have tried removing anything out of the main form which is not in the table 'Orders' and still get the error. The subform I have added textboxes with calculations and they all work perfectly, I can't work out why it won't do it, its something to do with the 'Orders' table and I have tried things with the relationships but no luck (join type etc).

Any more help??

[jzwp11]

I have tried removing anything out of the main form which is not in the table 'Orders' and still get the error

Other than some possible calculated controls what other controls would there be?

Could you post the database with any sensitive data removed?

[julz77]

Hi
I have given up and started again, AND the form works! I don't know what happened to the DB but even though it has meant re-doing heaps of work it is better than trying to fix something that I may never be able to fix! thanks everyone for your help

[jzwp11]

Glad you were able to come to a resolution. Good luck with your database.

[cluelesscoder]

I have this same problem. When I try to input something into the ProductID part of my form, I get the 'join key not in the recordset' error.

I just have 3 tables:

Product
ProductID (PK)
ProductName
CompanyID(FK)
ProductType(FK)

Company
CompanyID(PK)
CompanyName

ProductType
ProductTypeID(PK)
ProductTypeName

Each Company can have many Products, and each ProductType can include many products. I want a form where I can input a new Product and at the same time assign it a Company and a ProductType. This doesn't seem like it should be technically difficult, but I keep getting the recordset error.

My form is based on a query with the three tables above. This is necessary because I want to show the name of the Company to pop up rather than just the CompanyID. Also, I'm using combo boxes to allow people to select the Company and ProductType.

Does anyone really understand what is going on when this error message gets spit out? I've been searching around the internet, and have also browsed through a couple access books, and nobody seems to explain the plumbing. I understand that the "join key" is the specific row used in the ON part of the INNER JOIN. If I'm joining my Company and ProductType tables, I obviously don't use ProductID to join them. I'm joining the Company with a nested join of Product and ProductType.

So, yeah... are all database systems this finicky about form type things?

[jzwp11]

Welcome to the forum!

You need to create a form based only on the product table. On that form have a combo box based on the company table with both the companyID field and the companyname. Set the column width of the companyID field to zero (so you don't see it when you click on the combo box); you can use the combo box wizard to walk you through making it. Make sure the companyID field is bound to the CompanyID (FK) field of the underlying product table. You will want to do the same for the product type. I've attached an example database.

[cluelesscoder]

Wow, thanks a bunch. If you have any free time, now I have an issue with trying to edit some subform data after filtering using a Combobox. See https://www.accessforums.net/forms/h...orm-13347.html