Unable to edit on a form

[Robin Chapple]

I have a form based on a single query. qryThree

The query is based on another query, qryTwo which links data from two
sources. One table, one query. tblOne and qryOne

I am prevented from changing the data in qryThree. I know that this is
possible. What have I missed?

Thanks,

Robin Chapple

 

[Eric Schittlipz]

I have a form based on a single query. qryThree

The query is based on another query, qryTwo which links data from two
sources. One table, one query. tblOne and qryOne

I am prevented from changing the data in qryThree. I know that this is
possible. What have I missed?

Thanks,

Robin Chapple

How do you know it is possible? Are you saying you can edit the data in the
query, but not the form? It sounds to me like it could be the design of the
query which is preventing the edits. It can sometimes be remedied by
looking at the design of the query and selecting View>Properties and
changing the 'Unique Records' property to true. However, you some queries
cannot be edited (think of totals queries) and some might need a re-write
but you could post the SQL statements for the 3 queries.
If you can edit qryThree directly then check things like the 'AllowEdits'
property of the form.

 

[Guest]

I have a form based on a single query. qryThree

The query is based on another query, qryTwo which links data from two
sources. One table, one query. tblOne and qryOne

I am prevented from changing the data in qryThree. I know that this is
possible. What have I missed?

Thanks,

Robin Chapple

Little more information, please.
What is the data source for qryOne?
What fields is qryTwo pulling from tblOne, from qryOne?
What fields is qryThree pulling from qryTwo?
Any parameters?

Roxie Aho
roxiea at usinternet.com

 

[Van T. Dinh]

Since you use tblOne and qryOne, I assume that qryOne involves more than one
Table. Let's say 2.

Whether qryOne is updateable or not depends on the Fields you use to join
the Tables in qryOne. Generally, the Query is updateable only if one of the
linking Field is a PrimaryKey.

Assuming qryOne is updateable, then you will have the same thing when you
join qryOne to tblOne in qryTwo.

If qryTwo is updateable and qryThree is not a Totals Query, then qryThree
should be updateable.

It goes without saying that if qryOne is not updateable, then qryTwo and in
turn, qryThree won't be updateable. If qryTwo is not updateable, then qry
won't be updateable, either.

 

[Robin Chapple]

How do you know it is possible?

Because this one used to work and other are still working.

Are you saying you can edit the data in the
query, but not the form?

I can edit neither.

It sounds to me like it could be the design of the
query which is preventing the edits. It can sometimes be remedied by
looking at the design of the query and selecting View>Properties and
changing the 'Unique Records' property to true.

I tried that. No change.

Thanks,

Robin Chapple

 

[Robin Chapple]

Little more information, please.
What is the data source for qryOne?

Query One is a membership database with a club identified by ClubID

What fields is qryTwo pulling from tblOne, from qryOne?

Query Two uses a look up table to add the ClubName field.

What fields is qryThree pulling from qryTwo?

Query three is all fields from query Two

Any parameters?

No parameters are used.

Thanks,

Robin

 

[Robin Chapple]

Since you use tblOne and qryOne, I assume that qryOne involves more than one
Table. Let's say 2.

Yes query One is used to add the club name from the Club ID in table
one. Club ID is a primaryKey field. The query cannot be edited.

Whether qryOne is updateable or not depends on the Fields you use to join
the Tables in qryOne. Generally, the Query is updateable only if one of the
linking Field is a PrimaryKey.

Thanks,

Robin

 

[Rick Brandt]

Yes query One is used to add the club name from the Club ID in table
one. Club ID is a primaryKey field. The query cannot be edited.

It is completely normal in most database engines (not just Access) for queries
or views based on more than one table to result in Read-only result sets. As
more tables are added and/or the complexity of the query increases it becomes
more and more likely that the db engine will not be able to determine which of
the source tables edits should be applied to. When the db engine "senses" any
ambiguity about this it will no longer allow edits in the results. This is a
safety mechanism to protect the integrity of the data. Access/Jet actually
leans much more to the side of allowing edits in these cases than most other
databases.

As a general rule one should avoid using multi-table queries as the RecordSource
for a form where you want editing to be allowed. It _does_ work some of the
time, but one should not be surprised on those occasions where it does not. On
those occasions where editing is disallowed you can often tweak the design of
the query to again make edits possible. All you can do is read up on the
relevant Help topics on this and see if that can be done in your case.