Changing form data

[Pwyd]

I've done something stupid. The underlying table allows data changes.
However, the query i'm using to power my form no longer allows me to edit old
records. And once a new record is created, i can't change it from its default
values. What setting have i messed with? i went through the database
options and the query settings and the form's settings.

 

[Pwyd]

Okay. i've discovered the problem but i do not understand its cause. The
problem is the query driving the form is including a new table, called
Namelist. The Namelist table has a few text fields, and one of them simply
returns a full name for a given shortname. I can edit the original query's
data fine. But if i even put the namelist table on the "show table" for that
same query, it prevents me from editing any of the data. Whats going on?

 

[John Spencer]

Hard to say without knowing more about your data. Does NameList have a
primary key? Are you including that in your 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.)

http://allenbrowne.com/ser-61.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

In addition, too many tables involved can cause a problem especially if
you don't have primary keys on all the tables or have not included the
primary key fields in the query.

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

 

[Pwyd]

Apart, each of the queries allows data entry. Combined they do not. I'm not
sure how that would manifest -- but i checked down through the summarized
list. none of them are the case.


Perhaps i'm misunderstanding how something works. When an afterupdate on
the form calls an event procedure for "me.refresh" and refreshes the queried
results, shouldn't the entire form (not the subform necessarily) refresh?

 

[John Spencer]

As I said apart from Allen Browne's list, if you have multiple tables
involved a query will lose the ability to be updated. However, this may
not be the case in your situation. I think you said you can add
records, you just can't update existing records.

Obviously something is happening, but I don't have an idea why this
would be the case.

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