Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Location
    Sussex, England
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Links between primary and foreign keys (2000)

    Can anyone help with the following? I will illustrate the problem using Northwind rather than try and explain my database.

    Take the tables Customers, Orders and Order details and build a form such as Main (Customers), Sub1 (Orders), Sub2 (Order details). Using this form then add a new Customer, new Order and new detail. Access correctly adds the customer and order ensuring links between primary and foreign keys are built. However, the Primary key of the orders table (OrderID) is not written into its corresponding foreign key field in the details table. Thus causing the error Primary key cannot be Null. The OrderID is part of the Primary key in order details but also doubles as the foreign key linking back to Orders table.
    Why is Access happy to write the foreign key into Orders at the Main and Sub1 level but will not do the same thing between Sub1 and Sub2 levels?

    Any help would be appreciated.
    Chesney

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Links between primary and foreign keys (2000)

    Do you have Sub2 as a subform in Sub1, or as a subform in Main?

  3. #3
    New Lounger
    Join Date
    Feb 2002
    Location
    Sussex, England
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links between primary and foreign keys (2000)

    Sub2 is a Subform in Sub1

    Thanks for any advice in advance
    Chesney

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Links between primary and foreign keys (2000)

    I did a little test with the setup you describe:

    <table border=1><td>Form</td><td>Record Source</td><td>Link Field</td><td>Link Field</td><tr><td>Main</td><td>Customers</td><td>CustomerID</td><td>

  5. #5
    New Lounger
    Join Date
    Feb 2002
    Location
    Sussex, England
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links between primary and foreign keys (2000)

    I still cannot get this working. Trying to interpret your solution:-

    There cannot be a linked field on the Main form, so I assume you mean Sub1 has both Child/Master fields CustomerID
    and Sub2 has both Child/Master fields as OrderID

    Can you please clarify

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Links between primary and foreign keys (2000)

    In my little table, I tried to indicate where these fields "live", but your interpretation is correct: the master and child link fields are both properties of the subform control. So both Link Child Fields and Link Master Fields for Sub1 are set to CustomerID, and both Link Child Fields and Link Master Fields for Sub2 are set to OrderID.

    I have attached a simple demo, just as an illustration. I paid no attention to consistent naming of controls etc.
    Attached Files Attached Files

Posting Permissions

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