Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    setting up relationship (access07)

    Hi,
    ran into some confusion with my table relationship so attached is a picture and heres an explanation of what is intended to happen.

    currently I have 4 tables

    Proposal table is what starts the data. OrderID is primary

    for every OrderID there is 1 design, one well

    for 1 well there can be many reservoirs

    When I start to enter in data in the proposal table all tables to the rightof tblProposal get data but tblDesign to the left does not get a OrderID from tblProposal? How can I make the data flow through to all the tables

    Thanks
    Attached Files Attached Files

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

    Re: setting up relationship (access07)

    How do you enter data? If you enter data in a table directly there is no reason why something would happen automatically in related tables.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: setting up relationship (access07)

    Will in tblProposal after I enter in a OrderID a plus + appears and when I click that tblWell appears an if I enter a WellID tblResevoir appears. I may have some setting activated

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

    Re: setting up relationship (access07)

    Ah yes - those are subdatasheets. Each table can have only one subdatasheet, so there is no way to add the other table to the chain this way.

    Subdatasheets look nice, but they have a negative impact on performance. The recommended way to treat data entry in related tables is through a form with subforms.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: setting up relationship (access07)

    thanks,
    off topic a bit but when making the form, I would like to have a subform for tblReservoir data in a table format so that I can add multiple entrys

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

    Re: setting up relationship (access07)

    You can use the Form Wizard to create a form based on tblReservoir in tabular or datasheet format. You can use this form as a subform on the main form.

  7. #7
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: setting up relationship (access07)

    ok, did that now when entering data in my form as soon as I enter data that relates to a different table I get an error at the bottom of the screen saying "Cannot add record(s); join key of table 'tblWell' not in recordset. Any idea what I need to do to fix this?

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

    Re: setting up relationship (access07)

    In fact, you need a main form based on tblProposal with a subform based on tblWell (linked on OrderID), and a sub-subform based on tblReservoir (linked to the subform on WellID).

Posting Permissions

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