Not possible to update fields and add new records.

[Guest]

I have a form - based on a select query. Until now it has been possible to
update the fields and to add new records in the table, which most of the data
belong to, simply by writing in the fields of the form. Now this is suddenly
not possible any longer. I am posting this question in the Queries group,
because it is not possible neither to update nor append records in the query,
which the form is based on. The only possibility seems to be to update
fields and add records directly in the table. I tried (temporarily) to
change the form, so it is based not on the select query but on a single
table, and with this solution it is actually possible to do what I want -
except that I am missing some information from the other tables of the select
query. The properties of the form and of each field in the form are
identical to the properties of other forms and fields, where I can update
fields and add records. Does anyone have an idea, what can be wrong?

 

[John Spencer]

Can you post the query? Hint: (Menu: View: SQL)

The problem seems to be in the query and not in your form. So I would look
at the query.

In Access Help type the following in the Answer Wizard tab
When can I update data from a query
Select that from the list for an explanation of some of the causes and
alternative solutions.

Access MVP Allen Browne has summarized 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.)

 

[Guest]

I think I have checked everything now, and it is still not working. I guess
I'll have to continue checking, and if I don't find my mistake, if must find
an alternative solution to the problem.


"John Spencer" skrev:

 

[Guest]

Now I got it!

It is actually a very simple query. I have a table with names and adresses.
This is the main table. In this table I don't have the names of the cities,
only the post office codes. I have another table with post office codes and
the related names of the cities. In the select query I have a left join from
the main table to the post office code table, so that each record of the main
table gives a record in the query, even if the post office code doesn't
appear in the post office code table. The described reasons for not having
any possibility to update fields are (as far as I can see) not really
applicable for this simple query.

In the post office code table I didn't have any key field, and the table
wasn't indexed either. I can't tell why it didn't have any key field. The
post office code is unique, and there was actually no reason, why it
shouldn't be a key field. Now I've defined it as a key field, and now the
query functions exactly, as I want, and it is possible to update fields and
add new records to the main customer table, using this select query (or a
form based on the query).

LarsM


"LarsM" skrev: