Unable to save data from a form created using blank form in access 2007. Getting error "cannot add records join key of table not in recordset access" RRS feed

  • Question

  • Hi All

    I have two tables customers and orders. custid is the primary key of first table which is using as a one to many relationship to orders table. I created a blank form to avoid creating a subform. But the form is not letting me fill any field belong to second table and prompt the error message "cannot add records join key of table not in recordset access". Could you please help me to resolve the issue?

    Thank you very much


    Friday, October 14, 2016 4:13 PM


  • If the form is to be used for inserting orders than it can be based on a query which joins the Customers and Orders tables, but should not be used for inserting data into the Customers table, only into the Orders table.  The foreign key CustID column in Orders should have as its bound control in the form a combo box set up along the following lines if the customers are individuals:

    ControlSource:   CustID

    RowSource:     SELECT CustID, FirstName & " " & LastName FROM Customers ORDER BY LastName, FirstName;

    BoundColumn:   1
    ColumnCount:    2
    ColumnWidths:  0cm

    If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

    Alternatively, you can concatenate the names so that the last name is first, which would be better with a large list of names as it allows the user to enter the initial characters of the last name and progressively go to the first match as each character is entered:

    RowSource:     SELECT CustID, LastName & ", " & FirstName FROM Customers ORDER BY LastName, FirstName;

    If the customers are corporate entities rather than individuals you will not have to concatenate the name of course, as there will presumably be a single CustName column or similar.

    The query can return whatever non-key columns from Customers you wish to show in the form when a customer is selected in the combo box, and controls in the form can be bound to these columns, setting the Locked property of each control to True  and its Enabled property to False to make them read-only.

    If the customers are corporate entities with distinct customer names then a new customer can be inserted in the Customers table where necessary by means of the CustID combo box's NotInList event procedure.  If they are individuals, however, then the names cannot be assumed to be distinct as personal names can legitimately be duplicated, so other means have to be adopted, e.g. a 'New Customer' button on the form, which opens a form bound to Customers in dialogue mode at an empty new record.  You'll find an example in NotInList.zip in my public databases folder at:


    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the opening form includes a button for adding a new contact by opening a form in dialogue mode.   When the contacts form is closed the new name is automatically added to the drop down list of the combo box in the form, and can be selected.  Note also how the combo box's list concatenates address data with each contact's names, to differentiate between two or more contacts of the same name.

    Also in this demo file you'll find examples of the use of the NotInList event in a variety of contexts.  In your case that to add a new city in the contacts form would be an appropriate model if your customers are corporate entities with distinct names.  This opens a form in dialogue mode to insert a new city record, passing the new city name to the form as its OpenArgs property.  In the dialogue form the state in which the city is located can then be selected.  Note BTW that this demo unrealistically assumes that city names are distinct.  In real life this is not the case of course.

    Ken Sheridan, Stafford, England

    • Proposed as answer by Chenchen Li Tuesday, October 18, 2016 9:31 AM
    • Marked as answer by Chenchen Li Monday, October 24, 2016 4:57 AM
    Friday, October 14, 2016 5:32 PM