Setting Queries Allow Edit

[edisonl via AccessMonster.com]

Hi,

I have a controls that generates queries.


But is there any idea of how to make the queries generated editable ?

Regards & God Bless, Edison

 

[John Spencer]

In Access Help type the following in the Answer Wizard tab
When can I update data from a query
Then in the results window select About Updating Data and select the
appropriate option


If you cannot edit the data in a query, this list may help you identify
why it is not updatable:
--Query based on three or more tables in which there is a
many-to-one-to-many relationship
--Query that includes a linked ODBC table with no unique index or a
Paradox table without a primary key
--Query that includes more than one table or query, and the tables or
queries aren't joined.
--It has a GROUP BY clause. A Totals query is always read-only.
--It has a TRANSFORM clause. A Crosstab query is always read-only.
--It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause.
Queries that aggregate records are read-only.
--It contains a DISTINCT predicate. Set Unique Values to No in the
query's Properties.
--It involves a UNION. Union queries are always read-only.
--It has a subquery in the SELECT clause. Uncheck the Show box under
your subquery, or use a domain aggregation function instead.
--It uses JOINs of different directions on multiple tables in the FROM
clause. Remove some tables.
--The fields in a JOIN are not indexed correctly: there is no primary
key or unique index on the JOINed fields.
--The query's Recordset Type property is Snapshot. Set Recordset Type to
"Dynaset" in the query's Properties.
--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.)
--The query calls a VBA function, but the database is not in a trusted
location so the code cannot run. (See the yellow box at the top of this
Access 2007 page.)

Also See
http://allenbrowne.com/ser-61.html

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[edisonl via AccessMonster.com]

Hi John,

Appreciate your response.. But not sure below mentioned rules applicable to
any specific version?
In my case, I am using Access 2000.

Regards & God Bless, Edison

In Access Help type the following in the Answer Wizard tab
When can I update data from a query
Then in the results window select About Updating Data and select the
appropriate option

If you cannot edit the data in a query, this list may help you identify
why it is not updatable:
--Query based on three or more tables in which there is a
many-to-one-to-many relationship
--Query that includes a linked ODBC table with no unique index or a
Paradox table without a primary key
--Query that includes more than one table or query, and the tables or
queries aren't joined.
--It has a GROUP BY clause. A Totals query is always read-only.
--It has a TRANSFORM clause. A Crosstab query is always read-only.
--It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause.
Queries that aggregate records are read-only.
--It contains a DISTINCT predicate. Set Unique Values to No in the
query's Properties.
--It involves a UNION. Union queries are always read-only.
--It has a subquery in the SELECT clause. Uncheck the Show box under
your subquery, or use a domain aggregation function instead.
--It uses JOINs of different directions on multiple tables in the FROM
clause. Remove some tables.
--The fields in a JOIN are not indexed correctly: there is no primary
key or unique index on the JOINed fields.
--The query's Recordset Type property is Snapshot. Set Recordset Type to
"Dynaset" in the query's Properties.
--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.)
--The query calls a VBA function, but the database is not in a trusted
location so the code cannot run. (See the yellow box at the top of this
Access 2007 page.)

Also See
http://allenbrowne.com/ser-61.html

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi,

[quoted text clipped - 3 lines]

Regards & God Bless, Edison

 

[John Spencer]

The guidelines (rules) are applicable to all versions of Access. The
last guideline applies only to Access 2007 since that is when MS
implemented the trusted location concept. In earlier versions, you may
run into restrictions based on security also, but the security is
implemented differently.

That said, even the guidelines don't explain all cases of why you can't
update values in a query, but they do explain almost all cases that I
have ever encountered in the last decade.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi John,

Appreciate your response.. But not sure below mentioned rules applicable to
any specific version?
In my case, I am using Access 2000.

Regards & God Bless, Edison

In Access Help type the following in the Answer Wizard tab
When can I update data from a query
Then in the results window select About Updating Data and select the
appropriate option

If you cannot edit the data in a query, this list may help you identify
why it is not updatable:
--Query based on three or more tables in which there is a
many-to-one-to-many relationship
--Query that includes a linked ODBC table with no unique index or a
Paradox table without a primary key
--Query that includes more than one table or query, and the tables or
queries aren't joined.
--It has a GROUP BY clause. A Totals query is always read-only.
--It has a TRANSFORM clause. A Crosstab query is always read-only.
--It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause.
Queries that aggregate records are read-only.
--It contains a DISTINCT predicate. Set Unique Values to No in the
query's Properties.
--It involves a UNION. Union queries are always read-only.
--It has a subquery in the SELECT clause. Uncheck the Show box under
your subquery, or use a domain aggregation function instead.
--It uses JOINs of different directions on multiple tables in the FROM
clause. Remove some tables.
--The fields in a JOIN are not indexed correctly: there is no primary
key or unique index on the JOINed fields.
--The query's Recordset Type property is Snapshot. Set Recordset Type to
"Dynaset" in the query's Properties.
--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.)
--The query calls a VBA function, but the database is not in a trusted
location so the code cannot run. (See the yellow box at the top of this
Access 2007 page.)

Also See
http://allenbrowne.com/ser-61.html

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi,

[quoted text clipped - 3 lines]

Regards & God Bless, Edison