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

    Question

  • 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
    Wednesday, April 20, 2011 3:21 AM

Answers

  • 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

    • Marked as answer by Bruce Song Wednesday, May 04, 2011 10:15 AM
    Wednesday, April 20, 2011 3:35 AM
  • 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
    • Marked as answer by Bruce Song Wednesday, May 04, 2011 10:15 AM
    Thursday, April 21, 2011 6:17 PM

All replies

  • 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

    • Marked as answer by Bruce Song Wednesday, May 04, 2011 10:15 AM
    Wednesday, April 20, 2011 3:35 AM
  • 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
    • Marked as answer by Bruce Song Wednesday, May 04, 2011 10:15 AM
    Thursday, April 21, 2011 6:17 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.
    • Proposed as answer by Vee2000 Wednesday, March 28, 2012 8:53 AM
    Thursday, September 29, 2011 5:19 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
    Thursday, September 29, 2011 6:02 PM
  • 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

    Wednesday, March 28, 2012 8:53 AM