queries that don't allow new input

[Guest]

Is there a general rule of thumb to tell you where to look for the problem
when a query (and therefore a form based on that query) doesn't allow you to
edit or enter new records (and the navigation button with the star is greyed
out).

I get this problem trying to create a select query drawing from three
related tables (one of them a many-to-many junction table). OK if I just want
a form for viewing records, but otherwise not helpful.

I get this even whan I create the query using the query wizard. And when I
try tinkering and adjusting I end up with an "ambiguous outer joins" error
message. I have only a very limited grasp of either VBA or SQL and INNER
JOIN, LEFT JOIN stuff leaves me pretty baffled.

 

[Guest]

Look in the top section of the query design and right click on the join lines
and change the join type.

 

[Rick Brandt]

Is there a general rule of thumb to tell you where to look for the
problem when a query (and therefore a form based on that query)
doesn't allow you to edit or enter new records (and the navigation
button with the star is greyed out).

I get this problem trying to create a select query drawing from three
related tables (one of them a many-to-many junction table). OK if I
just want a form for viewing records, but otherwise not helpful.

I get this even whan I create the query using the query wizard. And
when I try tinkering and adjusting I end up with an "ambiguous outer
joins" error message. I have only a very limited grasp of either VBA
or SQL and INNER JOIN, LEFT JOIN stuff leaves me pretty baffled.

There is a help topic named
"When can I update data from a query?"

That you need to read. There are many reasons why a query will not be
updateableand that doesn't mean you built the query incorrectly. In general
queries are for organizing data for reporting or other analysis operations,
not for doing edits to data although they can be used for that if the
structure of the query doesn't render a read-only record set.

 

[Guest]

Many thanks Rick

You solved my problem. The clincher was probably that I didn't grasp that
the keyword here was "update". This led me a MS knowledge base article called
"How to troubleshoot errors that may occur when you update data in Access
queries and in Access forms", which is perhaps the one you were thinking of.

Here I found exactly what I was looking for: "When the query is based on
three or more tables and there is a many-to-one-to-many relationship, you
cannot update the data directly in the query. You can update the data in a
form or in a data access page. You can do this based on the query when the
RecordsetType property of the form is set to Dynaset (Inconsistent Updates)."

I changed the RecordsetType property of my form accordingly and now it works
fine.