Editing data in datasheet view from a query joining other tables

[Guest]

Gurus, I want to be able to type in new data or alter data in a table
(datasheet) view.

When i do this in datasheet view, MS Access shows an icon (little pen
writing) on the extreme left hand side of the datasheet view confirming i can
freely edit my data. likewise, when i run a query on this table to subset
some of its data, access allows me to edit the data from the datasheet mode
in the same way. it updates the source table okay.

So far so good. however....

Now when i run a query on the same table but this time join a field or
fields with the field of another table, the resulting query data sheet view
will not let me edit the data from the datasheet view. And the icon has
gone. Is there something i am missing here or is MS Access set up to disalow
this type of editing. (i have tried to do it by creating a form based on the
query results making sure the properties of each field i want to change will
allow editing but this still wont work.)

 

[John Spencer]

In Access Help type the following in the Answer Wizard tab
When can I update data from a query

Select about updaing data (or the above) that from the list for an
explanation of some of the causes and alternative solutions.

Access MVP Allen Browne has summarized some of the reasons:

Query results will be read-only if any of the following apply:
.. It has a GROUP BY clause (totals query).
.. It has a TRANSFORM clause (crosstab query).
.. It contains a DISTINCT predicate.
.. It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause
(performs aggregation).
.. It involves a UNION.
.. It has a subquery in the SELECT clause.
.. It uses JOINs of different directions on multiple tables in the FROM
clause.
.. The query's Recordset Type property is set to Snapshot.
.. The query is based on another query that is read-only (stacked query.)
.. Your permissions are read-only (Access security.)
.. The database is opened read-only, or the file attributes are read-only, or
the database is on read-only media (e.g. CD-ROM, network drive without write
privileges.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks very much John.

i will try your suggestion.