Why has my query become read-only?

[Erin]

I am working with Microsoft Access 2003. I have a tabbed form which is based
on a simple query. The query is based on five tables. Previously, I could
edit data directly on the tabbed form, but now I can't (I can't add any new
records, either). It's important to note that I have recently recreated both
the query and the form, but as far as I can tell I did not do anything
differently this time around--properties are all the same as the old version.

The SQL is as follows:

SELECT tblGenPubDetails.*, tblAcquisitions.*, tblMarketing.*,
[tblEdit&Production].*, tblRights.*
FROM tblAcquisitions INNER JOIN (((tblGenPubDetails INNER JOIN tblMarketing
ON tblGenPubDetails.BookTitle = tblMarketing.BookTitle) INNER JOIN
[tblEdit&Production] ON (tblMarketing.BookTitle =
[tblEdit&Production].BookTitle) AND (tblGenPubDetails.BookTitle =
[tblEdit&Production].BookTitle)) INNER JOIN tblRights ON
([tblEdit&Production].BookTitle = tblRights.BookTitle) AND
(tblGenPubDetails.BookTitle = tblRights.BookTitle)) ON
(tblGenPubDetails.BookTitle = tblAcquisitions.BookTitle) AND
(tblAcquisitions.BookTitle = tblMarketing.BookTitle);

Any ideas? I can't for the life of me figure out why the query and form have
become read-only.

Many thanks in advance for any assistance you can provide,

Erin

 

[fredg]

I am working with Microsoft Access 2003. I have a tabbed form which is based
on a simple query. The query is based on five tables. Previously, I could
edit data directly on the tabbed form, but now I can't (I can't add any new
records, either). It's important to note that I have recently recreated both
the query and the form, but as far as I can tell I did not do anything
differently this time around--properties are all the same as the old version.

The SQL is as follows:

SELECT tblGenPubDetails.*, tblAcquisitions.*, tblMarketing.*,
[tblEdit&Production].*, tblRights.*
FROM tblAcquisitions INNER JOIN (((tblGenPubDetails INNER JOIN tblMarketing
ON tblGenPubDetails.BookTitle = tblMarketing.BookTitle) INNER JOIN
[tblEdit&Production] ON (tblMarketing.BookTitle =
[tblEdit&Production].BookTitle) AND (tblGenPubDetails.BookTitle =
[tblEdit&Production].BookTitle)) INNER JOIN tblRights ON
([tblEdit&Production].BookTitle = tblRights.BookTitle) AND
(tblGenPubDetails.BookTitle = tblRights.BookTitle)) ON
(tblGenPubDetails.BookTitle = tblAcquisitions.BookTitle) AND
(tblAcquisitions.BookTitle = tblMarketing.BookTitle);

Any ideas? I can't for the life of me figure out why the query and form have
become read-only.

Many thanks in advance for any assistance you can provide,

Erin

Your query is made from 5 tables?

Some queries are not updateable.
See Access help:
Query + Troubleshoot queries + Select Query + I can't update data
from a query + Data can't be updated

 

[Stefan Hoffmann]

hi Erin,

Any ideas? I can't for the life of me figure out why the query and form have
become read-only.

Ensure that all tables have primary keys defined.



mfG
--> stefan <--

 

[Jerry Whittle]

I 'm assuming by read-only you mean it is not updateable.

Check out the following article.
http://support.microsoft.com/?kbid=328828

 

[Erin]

The reason my query is built from five tables is because I wanted to bring
together all of the data from these tables to create a tabbed form. It was my
impression that tabbed forms had to be built from a query, because in
designing the form it won't allow you to pull from multiple tables. Is this
incorrect?

Reading Microsoft Access help, the only thing I can think of is perhaps the
tables aren't joined properly?

As far as I can tell the data should be updatable, because my old tabbed
form and query were updatable. This is why I'm confused.

I apologize if my question is not clear enough.

Thanks for any help anyone can provide!

Erin

I am working with Microsoft Access 2003. I have a tabbed form which is based
on a simple query. The query is based on five tables. Previously, I could
edit data directly on the tabbed form, but now I can't (I can't add any new
records, either). It's important to note that I have recently recreated both
the query and the form, but as far as I can tell I did not do anything
differently this time around--properties are all the same as the old version.

The SQL is as follows:

SELECT tblGenPubDetails.*, tblAcquisitions.*, tblMarketing.*,
[tblEdit&Production].*, tblRights.*
FROM tblAcquisitions INNER JOIN (((tblGenPubDetails INNER JOIN tblMarketing
ON tblGenPubDetails.BookTitle = tblMarketing.BookTitle) INNER JOIN
[tblEdit&Production] ON (tblMarketing.BookTitle =
[tblEdit&Production].BookTitle) AND (tblGenPubDetails.BookTitle =
[tblEdit&Production].BookTitle)) INNER JOIN tblRights ON
([tblEdit&Production].BookTitle = tblRights.BookTitle) AND
(tblGenPubDetails.BookTitle = tblRights.BookTitle)) ON
(tblGenPubDetails.BookTitle = tblAcquisitions.BookTitle) AND
(tblAcquisitions.BookTitle = tblMarketing.BookTitle);

Any ideas? I can't for the life of me figure out why the query and form have
become read-only.

Many thanks in advance for any assistance you can provide,

Erin

Your query is made from 5 tables?

Some queries are not updateable.
See Access help:
Query + Troubleshoot queries + Select Query + I can't update data
from a query + Data can't be updated

 

[John W. Vinson]

I am working with Microsoft Access 2003. I have a tabbed form which is based
on a simple query. The query is based on five tables. Previously, I could
edit data directly on the tabbed form, but now I can't (I can't add any new
records, either). It's important to note that I have recently recreated both
the query and the form, but as far as I can tell I did not do anything
differently this time around--properties are all the same as the old version.

The SQL is as follows:

SELECT tblGenPubDetails.*, tblAcquisitions.*, tblMarketing.*,
[tblEdit&Production].*, tblRights.*
FROM tblAcquisitions INNER JOIN (((tblGenPubDetails INNER JOIN tblMarketing
ON tblGenPubDetails.BookTitle = tblMarketing.BookTitle) INNER JOIN
[tblEdit&Production] ON (tblMarketing.BookTitle =
[tblEdit&Production].BookTitle) AND (tblGenPubDetails.BookTitle =
[tblEdit&Production].BookTitle)) INNER JOIN tblRights ON
([tblEdit&Production].BookTitle = tblRights.BookTitle) AND
(tblGenPubDetails.BookTitle = tblRights.BookTitle)) ON
(tblGenPubDetails.BookTitle = tblAcquisitions.BookTitle) AND
(tblAcquisitions.BookTitle = tblMarketing.BookTitle);

Any ideas? I can't for the life of me figure out why the query and form have
become read-only.

Many thanks in advance for any assistance you can provide,

Erin

Multitable queries are often non-updateable, and the more multi- the less
updateable, usually! It's VERY rarely a good idea to base a form on one Great
Master Query that joins everything, for this reason and others (for instance
you won't see any data at all unless all five tables have matching records,
and you'll see each record repeated as many times as in the "manyest" table).

Instead, consider using Combo Boxes for lookup tables, and Subforms for "many"
side tables. I don't know anything about the logic of your tables, but you may
have better luck basing a Form on the "one" side table, and Subforms (each on
a tab page) for the "many".

Since it once worked but now doesn't... what has changed? Did you change any
of the indexes or relationships in the tables, for instance? Or go to a new
version of Access?

 

[Dale Fye]

See John's note. There is also a more detailed list of why queries may not
be updateable on Allen Browne's website
(http://www.allenbrowne.com/ser-61.html).

As far as tabbed forms are concerned, you can certainly put data on each of
the tabs from the main forms RecordSource (the main query), but you can also
put subforms on tabs, which is the way I usually go when I want to present my
user with a lot of data, from multiple related tables, on a single form.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

The reason my query is built from five tables is because I wanted to bring
together all of the data from these tables to create a tabbed form. It was my
impression that tabbed forms had to be built from a query, because in
designing the form it won't allow you to pull from multiple tables. Is this
incorrect?

Reading Microsoft Access help, the only thing I can think of is perhaps the
tables aren't joined properly?

As far as I can tell the data should be updatable, because my old tabbed
form and query were updatable. This is why I'm confused.

I apologize if my question is not clear enough.

Thanks for any help anyone can provide!

Erin

I am working with Microsoft Access 2003. I have a tabbed form which is based
on a simple query. The query is based on five tables. Previously, I could
edit data directly on the tabbed form, but now I can't (I can't add any new
records, either). It's important to note that I have recently recreated both
the query and the form, but as far as I can tell I did not do anything
differently this time around--properties are all the same as the old version.

The SQL is as follows:

SELECT tblGenPubDetails.*, tblAcquisitions.*, tblMarketing.*,
[tblEdit&Production].*, tblRights.*
FROM tblAcquisitions INNER JOIN (((tblGenPubDetails INNER JOIN tblMarketing
ON tblGenPubDetails.BookTitle = tblMarketing.BookTitle) INNER JOIN
[tblEdit&Production] ON (tblMarketing.BookTitle =
[tblEdit&Production].BookTitle) AND (tblGenPubDetails.BookTitle =
[tblEdit&Production].BookTitle)) INNER JOIN tblRights ON
([tblEdit&Production].BookTitle = tblRights.BookTitle) AND
(tblGenPubDetails.BookTitle = tblRights.BookTitle)) ON
(tblGenPubDetails.BookTitle = tblAcquisitions.BookTitle) AND
(tblAcquisitions.BookTitle = tblMarketing.BookTitle);

Any ideas? I can't for the life of me figure out why the query and form have
become read-only.

Many thanks in advance for any assistance you can provide,

Erin

Your query is made from 5 tables?

Some queries are not updateable.
See Access help:
Query + Troubleshoot queries + Select Query + I can't update data
from a query + Data can't be updated

 

[Michel Walsh]

The same query? have you checked the mdb file itself? is its Read Only
attribute turned on (as it may happen when you copy it on a WriteOnce CD and
back, from the CD, to the hard disk) ?


Vanderghast, Access MVP

 

[Erin]

Thanks, John. This is useful advice. I don't have a vast wealth of Access
skills but have built and am trying to maintain a publishing database that
organizes information about our books into five broad categories (general pub
details, acquisitions info, marketing info, editorial & production info, and
rights & permissions info). The tabbed form seems like the best format in
which to present all the data together, but I wasn't aware of the potential
pitfalls of basing it on one great master query. I haven't heard of subforms
before, but will look into this as a potentially better option.

In terms of my old query and form (which did allow data updating), I do now
remember that in the midst of editing some of my tables, I removed a field
that we determined was unnecessary, and a message box popped up saying I was
going to delete an affiliated index or something like that. I didn't know
what this meant but seeing as all the tables had a primary key defined, I
didn't think it was of grave concern, so went ahead and deleted the field.
Perhaps this is what's causing me the problem now?

I appreciate everyone's efforts in helping me!

Erin

I am working with Microsoft Access 2003. I have a tabbed form which is based
on a simple query. The query is based on five tables. Previously, I could
edit data directly on the tabbed form, but now I can't (I can't add any new
records, either). It's important to note that I have recently recreated both
the query and the form, but as far as I can tell I did not do anything
differently this time around--properties are all the same as the old version.

The SQL is as follows:

SELECT tblGenPubDetails.*, tblAcquisitions.*, tblMarketing.*,
[tblEdit&Production].*, tblRights.*
FROM tblAcquisitions INNER JOIN (((tblGenPubDetails INNER JOIN tblMarketing
ON tblGenPubDetails.BookTitle = tblMarketing.BookTitle) INNER JOIN
[tblEdit&Production] ON (tblMarketing.BookTitle =
[tblEdit&Production].BookTitle) AND (tblGenPubDetails.BookTitle =
[tblEdit&Production].BookTitle)) INNER JOIN tblRights ON
([tblEdit&Production].BookTitle = tblRights.BookTitle) AND
(tblGenPubDetails.BookTitle = tblRights.BookTitle)) ON
(tblGenPubDetails.BookTitle = tblAcquisitions.BookTitle) AND
(tblAcquisitions.BookTitle = tblMarketing.BookTitle);

Any ideas? I can't for the life of me figure out why the query and form have
become read-only.

Many thanks in advance for any assistance you can provide,

Erin

Multitable queries are often non-updateable, and the more multi- the less
updateable, usually! It's VERY rarely a good idea to base a form on one Great
Master Query that joins everything, for this reason and others (for instance
you won't see any data at all unless all five tables have matching records,
and you'll see each record repeated as many times as in the "manyest" table).

Instead, consider using Combo Boxes for lookup tables, and Subforms for "many"
side tables. I don't know anything about the logic of your tables, but you may
have better luck basing a Form on the "one" side table, and Subforms (each on
a tab page) for the "many".

Since it once worked but now doesn't... what has changed? Did you change any
of the indexes or relationships in the tables, for instance? Or go to a new
version of Access?

 

[David W. Fenton]

I am working with Microsoft Access 2003. I have a tabbed form
which is based on a simple query. The query is based on five
tables. Previously, I could edit data directly on the tabbed form,
but now I can't (I can't add any new records, either).

You might try the Jet-specific DISTINCTROW predicate:

SELECT DISTINCTROW ...

If you're not comfortable typing that in SQL view you can change it
with the query properties, changing the property called "Unique
Rows" to True.