Results 1 to 3 of 3
  1. #1
    Join Date
    May 2010
    Posts
    23

    Unanswered: How to Add Record to Multi-Key Table

    Hey all,

    (Access 2007)
    I have an Order table and an Items table linked as a many-to-many via an Order_Line table.
    The primary key on the Order_Line table is a combination of OrderID and ItemID.

    I am trying to find the best way to build a an order form for users. I want to be able to use a combo box to select an Item (with ItemID bound), but every time I try this I get an error. I think the error is a product of me trying to write an ItemID to the Order_Line table before an OrderID is there to complete the two components of the primary key.

    Can anyone suggest a way to build this form? Hope this makes sense.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Typically you would do this with a form/subform, where the form was bound to the order and the subform to the detail table. Master/child links on OrderID would automatically populate the subform records with the OrderID.
    Paul

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Or you might set the default values of ItemID or OrderID on the subform to something like: =Forms!MyMainFormName!ItemID and/or =Forms!MyMainFormName!OrderID. How you set it up though can be done differently but when adding new records, you need to tell it where to get the default values from for ItemID and OrderID.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •