Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Join Key of table not in Recordset (Access 97 SR2)

    Ok. I'm going to show my ignorance here. I have a form based on a query that has several tables in it. When I try to update some of the fields in the form, I get an error message that says, "Can't add record(s); join key of table 'tbl_fish' not in recordset. There is a table called Fish in the query. Tell me what to start looking for to fix this problem. I have the table in the query called Fish. This contains the primary keys of other tables, and they are all related by these primary keys.
    I have attached a screen print of the query.
    Attached Images Attached Images

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Join Key of table not in Recordset (Access 97 SR2)

    I think the problem is that table Locations has a field called Fish ID, but it doesn't have a join to the Fish table. As an aside, you don't typically want to name fields (or tables) with a name that contains the # sign - your primary key for table Fish is Fish ID#. The # sign is reserved for some things that can cause trouble down the road. Repost if you aren't able to fix it, and this time give us the SQL string (switch to the SQL view) in the post - I presume there are some columns to the right that we can't see.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Join Key of table not in Recordset (Access 97 SR2)

    Here's the sql.
    Attached Images Attached Images

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Join Key of table not in Recordset (Access 97 SR2)

    I missed a key point in my previous response - your original post indicated that you were trying to add a new record - that almost never works with a query that contains three or more tables. What the add record process thinks is that you are trying to add records to all of the tables involved in a query. I'm pretty sure that isn't what you want to do. I also suspect that your tables may not be correctly normalized, as you have Fish ID in table Locations, tbl_Tag Type, and tbl_GBT Eval. (That presumes that Fish ID and Fish ID# are the same thing.)

    What I think you want to do is to use a combo box on your form to populate the various ID fields in tbl_Fish. That way those tables don't need to be involved in the query, and you can add records. This area is related to what is known as updateability - search help for "updatable" and you'll find a fair bit of background on when you can add or edit records and when you can't. Hope this helps.
    Wendell

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Join Key of table not in Recordset (Access 97 SR2)

    Yep. That helps a lot. I need to do some work and then I will post a reply letting you know how it works out. Thanks for the help. I have the hardest time with table relationships and normalization. I understand the concept, but I confuse myself beyond the ability to work sometimes.

    Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Join Key of table not in Recordset (Access 97 SR2)

    Melanie,

    Please post SQL as text in the future. You can just copy it from the SQL view of your query and paste it direclty into the textbox when posting. Then it will wrap automatically and not cause horizontal scrolling in the thread.
    Charlotte

Posting Permissions

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