Is there a work-around for add to recordset from query that has fields from 2 or more tables

[ultrarunner2017]

Hi;


This is in reference to Access 2010:
I have been tearing my hair out trying to figure out how to get a query using fields from more than one table to produce a recordset that can be added to or updated.
I have a database that uses a couple of lookup tables. When I run queries that use fields from both the main table and one or more of the lookup tables, I am unable to add new records to the recordset. The error is:'Cannot add record(s): join key of table ‘tablename’ not in recordset'

I have found that if, instead of using the field from the lookup table, I use the lookup field in the main table, I don't have this problem. But that will prevent me from using the criteria to get input from the user. I don't get an error in that situation, but I get only a null recordset when I run the query - unless I enter the wildcard '*' as the search term.

Any thoughts?
Thanks
FW

[pbaldy]

The error implies that if you include that field, the query will be updatable. Have you tried that?

[ultrarunner2017]

The error implies that if you include that field, the query will be updatable. Have you tried that?

Thank you very much! That solved the problem. I had tried adding the field from the main table before, but hadn't tried having both the lookup and the main table fields. Just one more reason I really need to do some studying of the manuals.

[pbaldy]

Happy to help!