Cannot enable editing of some fields

The query contains fields from only 1 table.


Rick Brandt wrote:
BobC wrote:

The data for the query is from 'one' table of 4 joined tables. The
tables are joined in a 1 to many.
The query is a 'select' query.
I see nothing related to totals.

It does not matter how the tables are related in the relationship window.
Do you have more than one table in the query? If so the odds of the result-
set being editable decrease as you add more tables. A query with four
tables will almost certainly not produce editable output.

That is often the case with multi-table queries - they are not editable.

Since you are only looking at data from one table in your sub-form, are you
able to base it on just that one table? Or you could use a query with only
that table, and with criteria to select the records you want.

John



BobC wrote:
I never thought about doing that!? ...
I cannot edit any of the fields.

What happens if you run the query directly from the database window - can you
edit the fields from there? Can you edit ANY of the fields?
[quoted text clipped - 9 lines]
edited. If they are field names and those fields are autonumber fields or
calculated fields in a query, they cannot be edited.

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

BobC wrote:

The query contains fields from only 1 table.

That does not matter either. If you have four tables in the query then the
result will likely not be editable.

Try it out. Create a new query with just the table that you are using the
fields from. Switch to datasheet view and you will likely see that you can
edit data. Add one table at a time with its appropriate join to the first
table. After each table is added see if the result set is editable. At
some point you will see the results go to read-only.

I am assuming that I will 'never' get the original query to work and it
is not a function for property selections????

At this point, I guess I need to try to understand the basics of the
query window.
I have 4 related tables which are normally related 1 to many in other
queries.
If I create a new query for the purposes of allowing me to edit some fields:
Does it matter how many tables are in the query window?
Does it matter if I remove the relationships in my new query window
or is the problem inherent in this Access application?

THANKS FOR TAKING THE TIME TO HELP ME!
Bob




J_Goddard via AccessMonster.com wrote:
That is often the case with multi-table queries - they are not editable.

Since you are only looking at data from one table in your sub-form, are you
able to base it on just that one table? Or you could use a query with only
that table, and with criteria to select the records you want.

John



BobC wrote:
I never thought about doing that!? ...
I cannot edit any of the fields.

What happens if you run the query directly from the database window - can you
edit the fields from there? Can you edit ANY of the fields?
[quoted text clipped - 9 lines]
edited. If they are field names and those fields are autonumber fields or
calculated fields in a query, they cannot be edited.

I will do that ... I was not aware that it was not cut and dry.

Thank You for taking the time to explain!

Rick Brandt wrote:
BobC wrote:

The query contains fields from only 1 table.

That does not matter either. If you have four tables in the query then the
result will likely not be editable.

Try it out. Create a new query with just the table that you are using the
fields from. Switch to datasheet view and you will likely see that you can
edit data. Add one table at a time with its appropriate join to the first
table. After each table is added see if the result set is editable. At
some point you will see the results go to read-only.

Hi -

If I create a new query for the purposes of allowing me to edit some fields:
Does it matter how many tables are in the query window?
No, not really - but it does matter how they are related (see below)

Does it matter if I remove the relationships in my new query window
Generally, yes it does - otherwise you end up with a large number of rows (#
Rows in Table A x # Rows in Table B x ....). Leave them in, and see below.


You should (based on my own experience) be able to make your query updateable
if:

- the fields you want to edit are all from the same table
- this table is not on the "One" side of any relationship in the query
- the table(s) on the "Many" side of each relationship have a unique index on
the field used in the relationship. This field is usually the PK of the
table.

You can use whatever criteria you need from any of the tables.

Give it a try - let us know how you make out.

Cheers!

John



BobC wrote:
I am assuming that I will 'never' get the original query to work and it
is not a function for property selections????

At this point, I guess I need to try to understand the basics of the
query window.
I have 4 related tables which are normally related 1 to many in other
queries.
If I create a new query for the purposes of allowing me to edit some fields:
Does it matter how many tables are in the query window?
Does it matter if I remove the relationships in my new query window
or is the problem inherent in this Access application?

THANKS FOR TAKING THE TIME TO HELP ME!
Bob

That is often the case with multi-table queries - they are not editable.

[quoted text clipped - 12 lines]
edited. If they are field names and those fields are autonumber fields or
calculated fields in a query, they cannot be edited.

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

I will do some experimenting and see what I can learn.

Thank You Very much for you time and efforts!!!!!!!!!
Cheers,
Bob

J_Goddard via AccessMonster.com wrote:
Hi -

If I create a new query for the purposes of allowing me to edit some fields:
Does it matter how many tables are in the query window?
No, not really - but it does matter how they are related (see below)

Does it matter if I remove the relationships in my new query window
Generally, yes it does - otherwise you end up with a large number of rows (#
Rows in Table A x # Rows in Table B x ....). Leave them in, and see below.


You should (based on my own experience) be able to make your query updateable
if:

- the fields you want to edit are all from the same table
- this table is not on the "One" side of any relationship in the query
- the table(s) on the "Many" side of each relationship have a unique index on
the field used in the relationship. This field is usually the PK of the
table.

You can use whatever criteria you need from any of the tables.

Give it a try - let us know how you make out.

Cheers!

John



BobC wrote:
I am assuming that I will 'never' get the original query to work and it
is not a function for property selections????

At this point, I guess I need to try to understand the basics of the
query window.
I have 4 related tables which are normally related 1 to many in other
queries.
If I create a new query for the purposes of allowing me to edit some fields:
Does it matter how many tables are in the query window?
Does it matter if I remove the relationships in my new query window
or is the problem inherent in this Access application?

THANKS FOR TAKING THE TIME TO HELP ME!
Bob

That is often the case with multi-table queries - they are not editable.

[quoted text clipped - 12 lines]
edited. If they are field names and those fields are autonumber fields or
calculated fields in a query, they cannot be edited.

I will do some experimenting and see what I can learn.

Thank You Very much for you time and efforts!!!!!!!!!

Cheers,
Bob

J_Goddard via AccessMonster.com wrote:
Hi -

If I create a new query for the purposes of allowing me to edit some fields:
Does it matter how many tables are in the query window?
No, not really - but it does matter how they are related (see below)

Does it matter if I remove the relationships in my new query window
Generally, yes it does - otherwise you end up with a large number of rows (#
Rows in Table A x # Rows in Table B x ....). Leave them in, and see below.


You should (based on my own experience) be able to make your query updateable
if:

- the fields you want to edit are all from the same table
- this table is not on the "One" side of any relationship in the query
- the table(s) on the "Many" side of each relationship have a unique index on
the field used in the relationship. This field is usually the PK of the
table.

You can use whatever criteria you need from any of the tables.

Give it a try - let us know how you make out.

Cheers!

John



BobC wrote:
I am assuming that I will 'never' get the original query to work and it
is not a function for property selections????

At this point, I guess I need to try to understand the basics of the
query window.
I have 4 related tables which are normally related 1 to many in other
queries.
If I create a new query for the purposes of allowing me to edit some fields:
Does it matter how many tables are in the query window?
Does it matter if I remove the relationships in my new query window
or is the problem inherent in this Access application?

THANKS FOR TAKING THE TIME TO HELP ME!
Bob

That is often the case with multi-table queries - they are not editable.

[quoted text clipped - 12 lines]
edited. If they are field names and those fields are autonumber fields or
calculated fields in a query, they cannot be edited.

I will do some experimenting and see what I can learn.

Thank You Very much for you time and efforts!!!!!!!!!

Cheers,
Bob

J_Goddard via AccessMonster.com wrote:
Hi -

If I create a new query for the purposes of allowing me to edit some fields:
Does it matter how many tables are in the query window?
No, not really - but it does matter how they are related (see below)

Does it matter if I remove the relationships in my new query window
Generally, yes it does - otherwise you end up with a large number of rows (#
Rows in Table A x # Rows in Table B x ....). Leave them in, and see below.


You should (based on my own experience) be able to make your query updateable
if:

- the fields you want to edit are all from the same table
- this table is not on the "One" side of any relationship in the query
- the table(s) on the "Many" side of each relationship have a unique index on
the field used in the relationship. This field is usually the PK of the
table.

You can use whatever criteria you need from any of the tables.

Give it a try - let us know how you make out.

Cheers!

John



BobC wrote:
I am assuming that I will 'never' get the original query to work and it
is not a function for property selections????

At this point, I guess I need to try to understand the basics of the
query window.
I have 4 related tables which are normally related 1 to many in other
queries.
If I create a new query for the purposes of allowing me to edit some fields:
Does it matter how many tables are in the query window?
Does it matter if I remove the relationships in my new query window
or is the problem inherent in this Access application?

THANKS FOR TAKING THE TIME TO HELP ME!
Bob

That is often the case with multi-table queries - they are not editable.

[quoted text clipped - 12 lines]
edited. If they are field names and those fields are autonumber fields or
calculated fields in a query, they cannot be edited.

BobC wrote in
:

I am working with a application that I did not create (Access
2007) I cannot seem to be able to allow editing of some fields on
a subform. I changed the properties to Enabled (yes) and Locked
(No); but I am still unable to edit the desired fields?

I've read all the other replies in the query. There is only one
possibility to make it editable that has not been mentioned, and
that's the Jet/ACE-only predicate DISTINCTROW. If you don't want to
dip into the SQL view and add DISTINCTROW after SELECT, go to the
query's properties sheet and select YES for the DISTINCT RECORDS
property. This predicate has the value of figuring out which rows
are unique and thus making (in some cases) both sides of a join
editable.

If it doesn't work, you're out of luck with using joins, and may
need to change your joins to subqueries using IN in the WHERE
clause, i.e.,:

WHERE ID IN (SELECT ID FROM Othertable WHERE ...)

This will leave the main table editable, but sometimes with
subqueries, Jet/ACE will not properly optimize the query and ignore
indexes on one or both sides of the criterion. However, I've found
that to be more like with NOT IN than with IN by itself.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/