"Cannot add record(s); join key of table ___ not in record set" Error


  • Hello, I'm trying to develop a simple form for a food ordering database at a cafe (as a learning project). I am fairly new to this so if I am posting in the wrong section, my apologies.

    In a form I made to add/modify an order, I have the following fields:

    EmployeeID, Customer First Name, Customer Last Name, BNumber (optional student ID number), OrderDate (defaulted to today's date), and the Order Details, which is a subform that contains ProductID, Quantity, ProductPrice, TotalPrice, and Complete (as a checkbox).

    Now, when I try to select an employee to assign to the order, it gives me this error:

    "Cannot add record(s); join key of table Orders not in record set" 

    The employeeID field is a combobox that contains the ID (hidden with the column widths), and Last and First names.

    Also, entering anything into the Customer First, Last, or BNumber results in the following error: "Field cannot be updated."

    I have referred to the a thread that had a well-written solution for a similar problem but unfortunately I couldn't apply it here. I know there are many variables that may be causing this so please let me know if you would like the database to take a look (there is only sample data, if any at all). I don't know how I would post it here though.

    Thank you!

    Thursday, May 02, 2013 5:28 PM

All replies

  • Okay, here you go.

    The main form's record source should not have the table that the subform uses and the subform's record source should not have the same table that is in the main form's record source.  The subform should only have the table that the subform is storing data into. 

    Both forms should not have any tables in their record sources that are just lookup tables for a control  The value can be displayed using a combo box or list box and their row source would have those lookup tables, but not the form itself.

    Also, you need to make sure that the PK field of the applicable table is part of the applicable form's record source.

    Bob Larson, Access MVP 2008, 2009, 2011

    Thursday, May 02, 2013 6:06 PM
  • As Bob Mentioned,

    only the Orders Table should be in the RecordSource of the Orders Form. The ComboBox ControlSource can be a lookup field created on the Order Table to the Emplyee Table and the RowSource of the Combo box on the Form would be based on the Tables settings. To setup the Table if you haven't already do the following.

    In the Order Table, add a new field (EmployeeLookup) set the type as Lookup. The wizard will guide you to the Employees Table where you would select the ID, First and Last names and other fields you feel are appropriate. If you already have this setup then what you need to make sure of is when adding the fields, you are only adding from the Orders Table list and not the Employee Table.

    Chris Ward

    Thursday, May 02, 2013 7:17 PM
  • Ah, thank you very much to the both of you! I had a second table (customers) in the RecordSource and it seemed to have been causing all the issues.

    If you or anyone else doesn't mind helping me further, here is how my form is set up now:




    Order Details-





    Complete (checkbox):

    What I would like to make possible is, to add a customer as opposed to select one from the dropdown list. Would I have to link it to a new form that creates a customer?

    Also, I tried adding in a code I found from another thread that would take the ProductPrice from the Product table and update it automatically as I selected the Product in the order detail subform. Unfortunately it didn't work.  The directions I followed were:

    "The subform would also contain text box controls bound to the UnitPrice and Quantity columns.  To insert the current unit price of the selected product put the following in the AfterUpdate event procedure of the ProductID combo box:

    Dim strCriteria As String

    strCriteria = "ProductID = " & Me.ProductID

    If Not IsNull(Me.ProductID) Then
        Me.UnitPrice = DLookup("UnitPrice","Products",strCriteria)
        Me.UnitPrice = 0
    End If"

    Of course I made the necessary changes to adapt it to my tables but it gave me an error saying that the object "Dim strCriteria As String

    strCriteria = "ProductID = " & Me.ProductID" could not be found.

    Thanks, again.

    Thursday, May 02, 2013 8:13 PM
  • So it looks like before you can get really good results fro any Forms or programming you need to work on normalizing your data. This Table you mention should be Four Tables from the Looks of it.

    Customer Table

    Employee Table

    Product Table

    Order Table.

    Then you can have one or more Tables to create Many to Many relationships to relate all the data.

    To answer your other question, There should be a separate Form for Each Tables update. Having said that, you can have a Mainform and several subForms to enter data in one screen.

    Chris Ward

    Thursday, May 02, 2013 8:21 PM
  • I can't post images cause my account is not verified but, I have the following tables

    Products (1:M) Order Details

    Orders (1:M) Order Details

    Customers (1:M) Orders

    Employees (1:M) Orders

    Payment (1:M) Orders

    Hope that makes sense. Order Details is the subform and Orders is the main form. My initial problem was that the Customer fields were linked to the Customer table as opposed to the Customer ID table under Orders. I got rid of the three fields from Customers and replaced it with just CustomerID in the Orders table, and now I can input data just fine. Only problem is that now I have to select customers from a drop down list when I wanted the ability to add them as I added a new order record.

    Would I have to create another form, "Add Customer," for example, and then link to it in the main form?

    Thursday, May 02, 2013 8:35 PM
  • Would I have to create another form, "Add Customer," for example, and then link to it in the main form?

    Yes, but you can have several subforms on one MainForm and a subForm can have a subForm also.

    Chris Ward

    Thursday, May 02, 2013 8:58 PM
  • Thank you. Sorry to keep on bothering but on my second question again, I would like the "ProductPrice" in the subform "Order Details" to pull the product price from the Products table so that as soon as I pick a product, it will also display its price. Is this possible?
    Friday, May 03, 2013 1:57 AM
  • Actually, I just discovered the following code (adapted to my tables):

    =DLookUp("[ProductPrice]","Products","[ProductID]=[Forms]![Orders]![Order Line]![ProductID]")

    and it seems to work. 

    Thank you all again for the solutions!

    Friday, May 03, 2013 2:04 AM
  • Sorry to revive this thread again, but I"m running into another issue now.

    On the same Orders form with the Order Details subform, I'm trying to add a another subform, "Payment" which holds the fields of PaymentMethod, CardholderName, CardNumber, AmountPaid, and Paid (checkbox). However, when I try to select a PaymentMethod from the dropdown list, it gives me this error:

    "You tried to assign the Null value to a variable that is not a Variant type."

    Can I get some aid on this issue?

    Thanks again

    • Proposed as answer by KCDW Sunday, May 05, 2013 10:50 PM
    Sunday, May 05, 2013 6:01 PM
  • What is the Row Source of the PaymentMethod combo box?  What fields are included?  How did you build the combo box?  Is there any code in the subform that refers to that box?

    Bob Larson, Access MVP 2008, 2009, 2011

    Monday, May 06, 2013 6:00 PM