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

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

Here’s the sql.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote