Cannot add record(s); join key of table not in recordset PLEASE HELP!

Question

_{Wednesday, April 20, 2011 3:21 AM}

Hi everyone,

After some trouble with the totals and subform of my Form for 'Orders' I have now noticed that I can't enter data into my Form! AAHHH!

I get this error: Cannot add record(s); join key of table ‘orders’ not in recordset

I have an Orders Form, which has a subform of products, there is one order number which can have many products. I have Orders table, Products table with a Order details table inbetween them to create a many to many relationship. But I can't enter data in the form!

I just tried pasting into here a copy of the relationships but I won't let me. I am so stuck! I have read up on relationships and I just don't know what more I can do.

Any help PLEASE also I could email someone a image of the relationships to see if I am doing it right.

Any suggestions???

Julz

All replies (5)

_{Wednesday, April 20, 2011 3:35 AM ✅Answered}

Hi Julz,

Welcome to the world of Access! :)

Below is a nice article about dealing with this situation, explaining things like;

• How to Add or Edit Data in a Datasheet (Table or Query) or in a Form
• How to Save a Record in a Datasheet or in a Form
• How to Delete a Record in a Datasheet or in a Form
• How to Undo Changes Adding or Editing in a Datasheet or in a Form
• Referential Integrity
• Cascade Updates and Cascade Deletes
• About Restricting or Validating Data
• Subdatasheets
• When You Can Update Data from a Query

http://support.microsoft.com/kb/304473

HTH,

Daniel

HTH,

Daniel

_{Thursday, April 21, 2011 6:17 PM ✅Answered}

Your tables should be along these lines:

Orders
....OrderID(PK)
....CustomerID(FK)
....OrderDate

Products
....ProductID(PK)
....Product
....UnitPrice

OrderDetails
....OrderID(FK)
....ProductID(PK)
....UnitPrice
....Quantity

PK = Primary key; FK = foreign key.  The primary key of OrderDetails is a composite one made up of the two columns OrderID and ProductID.  Thses should be straightforward long integer numbers, not autonumbers, which the primary keys of Orders and Products will probably be.  Orders is related to OrderDetails on OrderID, and Products is related to OrderDetails on ProductID, and referential integrity should be enforced in each case.

The parent form should be in based on the Orders table, or it might be on a query which sorts the orders by OrderDate.  The subform should be based on the OrderDetails table and linked to the parent form by setting its LinkMasterFields and LinkChildFields properties to Order ID.  It should be in continuous forms view and have a combo box to select a product, set up as follows:

ControlSource:    ProductID

RowSource:     SELECT ProductID, Product FROM Products ORDER BY Product;

BoundColumn:   1
ColumnCount:   2
ColumnWidths:  0cm;8cm

If your units of measurement are imperial rather than metric Access will automatically convert the last one.  The important thing is that the first dimension is zero to hide the first column and that the second is at least as wide as the combo box.

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)
Else
    Me.UnitPrice = 0
End If

To show the gross price per order line add an unbound text box to the subform, with a ControlSource property of:

=[UnitPrice] * [Quantity]

This is a simplified scenario as it doesn't include any provision for adding tax, discounting a price etc which you may need to do.

Having entered data in the parent form you can now enter as many order lines as necessary by inserting new rows in the subform, selecting a product and entering the quantity in each case.

One other situation you may want to handle is selecting a new product which is not yet represented in the Products table.  Firstly you need to create a form bound to the Products table, frmProducts say.  Then you can type new product name directly into the combo box in the order details subform by putting the following code in the combo box's NotInList event procedure:

    Dim ctrl As Control
    Dim strMessage As String
   
    Set ctrl = Me.ActiveControl
    strMessage = "Add " & NewData & " to products list?"
   
    If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
        DoCmd.OpenForm "frmProducts", _
            DataMode:=acFormAdd, _
            WindowMode:=acDialog, _
            OpenArgs:=NewData
        ' ensure frmProducts closed
        DoCmd.Close acForm, "frmProducts"
        ' ensure Product has been added
        If Not IsNull(DLookup("ProductID", "Products", "Product = """ & _
            NewData & """")) Then
        Response = acDataErrAdded
        Else
            strMessage = NewData & " was not added to Products table."
            MsgBox strMessage, vbInformation, "Warning"
            Response = acDataErrContinue
            ctrl.Undo
        End If
    Else
        Response = acDataErrContinue
        ctrl.Undo
    End If

You also need to put the following in the frmProducts form's Open event procedure:

    If Not IsNull(Me.OpenArgs) Then
        Me.Product.DefaultValue = """" & Me.OpenArgs & """"
    End If

The product name you typed into the combo box is passed to frmProducts as its OpenArgs property and used to set the DefaultValue property of the Product text box control in that form, ready for you to enter its unit price and any other data you may need to put in other columns in the Products table, e.g. a Product Category.  When you close the frmProducts form you'll be returned to the combo box in the subform, and the new product will have been added to its list.

Ken Sheridan, Stafford, England

_{Thursday, September 29, 2011 5:19 PM}

If your subform is based on a query where there is a a 1 to many relationship rather than being based on the actual table, it may prevent data entry. I just had the same problem and I changed the subform source to the table where the data should be updated and it works great.

_{Thursday, September 29, 2011 6:02 PM}

A query which joins related tables can be used as the RecordSource for a subform, e.g. in the current case it might be:

SELECT OrderDetails.OrderID, OrderDetails.ProductID,
OrderDetails.UnitPrice, OrderDetails.Quantity
FROM Products INNER JOIN OrderDetails
ON Products.ProductID = OrderDetails.ProductID
ORDER BY Products.Product;

This would be the RecordSource of a subform within a Orders form for entering products for the Order.  In the subform the ProductID control would be bound to a combo box set up as I described in my earlier post.  In this example the OrderDetails table is joined to the Products table solely for the purpose of ordering the rows alphabetically by product.  This is a rather artificial example, but serves as an illustration.

While in this example the inclusion of the OrderDetails table is solely for the purpose of ordering the rows, there can be cases where columns from the referenced table would be returned in the query, e.g. the Products table might include a ProductDescription column with a more extended description than the value in the product column.  By retuning this column in the query and including a text box control bound to it in the subform, when a product is selected in the combo box the ProductDescription would automatically show in the text box.  You would not want the user to be able to edit the description, however, so to prevent this the text box's Locked property would be set to True and its Enabled property to False.

The important thing to note here is that, although the subform is based on a query which joins the two tables, data is being entered via the subform into the OrderDetails table only, not into the Products table, whose inclusion is purely to order the rows and/or show values other than those of the OrderDetails table's columns in the subform.

Ken Sheridan, Stafford, England

_{Wednesday, March 28, 2012 8:53 AM}

Exact same thing here. I was freaking out because I'm making a transaction system for a client that's due in 4 days. Turned out my query for the form just sourced the field from the wrong table- as a foreign key.

Thanks a lot, sometimes it's the most simple things that can trip us up :P