Cannot enable editing of some fields

[BobC]

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?

 

[Ken Snell]

What are the values in the ControlSource property for those fields? If they
begin with an = character, they are calculated expressions that cannot be
edited. If they are field names and those fields are autonumber fields or
calculated fields in a query, they cannot be edited.

 

[BobC]

Based on your comments, I created a test form using the original query
as a source. I was 'UNABLE'to edit the fields!
The fields are not outonumber nor calculated.
Any other suggestions?
Bob

 

[Rick Brandt]

Based on your comments, I created a test form using the original query
as a source. I was 'UNABLE'to edit the fields!
The fields are not outonumber nor calculated.
Any other suggestions?
Bob

Many queries are produce non-editable recordsets. Sounds like yours falls
into that category.

Does it use DISTINCT?
Does it have "View - Totals" enabled?
Does it use multiple input tables with joins?

The first two are hard rules that will produce read-only output 100% of the
time. The last is a "it depends" rule. How many tables, what kind of
joins, etc..

 

[BobC]

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.

 

[J_Goddard via AccessMonster.com]

Hi -

Go and take a look at the source query itself. If some of the fields in that
query are expressions, then they can't be edited either.

John

 

[BobC]

None of the fields are expressions ... the are directly from the table.
Thanks

 

[J_Goddard via AccessMonster.com]

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?

John

None of the fields are expressions ... the are directly from the table.
Thanks

Hi -

[quoted text clipped - 13 lines]

 

[BobC]

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?

John

None of the fields are expressions ... the are directly from the table.
Thanks

Hi -

[quoted text clipped - 13 lines]

edited. If they are field names and those fields are autonumber fields or
calculated fields in a query, they cannot be edited.

 

[Rick Brandt]

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.

 

[BobC]

The query contains fields from only 1 table.

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.

 

[J_Goddard via AccessMonster.com]

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

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]

 

[Rick Brandt]

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.

 

[BobC]

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.

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

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.

 

[BobC]

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

Thank You for taking the time to explain!

 

[J_Goddard via AccessMonster.com]

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

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]

 

[BobC]

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

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

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

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]

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

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

Cheers,
Bob

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

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]

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

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

Cheers,
Bob

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

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.

 

[David W. Fenton]

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.