Re: Why has my query become read-only?

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

"John W. Vinson" wrote:

On Mon, 16 Mar 2009 08:47:04 -0700, Erin <Erin@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

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?
--

John W. Vinson [MVP]

.

Relevant Pages

  • Re: Indexes and referential integrity
    ... Thanks a bunch John and Jerry. ... I made a mainform and subforms and it mostly works great. ... If I bring the 3 tables into the query that have referential ... into my query that wouldn't let me set referential integrity then the query ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Nested Full Text Queries
    ... You're welcome, John, ... your query should be re-written as follows: ... FROM tblarticles WHERE CONTAINS (, ... FROM authors as FT_TBL INNER JOIN ...
    (microsoft.public.sqlserver.fulltext)
  • Re: The Many Flavors of SQL - Can a SQL Server query work in MS Ac
    ... John and Baz, ... Now that I understand the underlying differences in query syntax, ... inner join cmsopen.dbo.apm_vendor v ... in the Query Analyzer on a Windows 2000 Server box running SQL Server ...
    (microsoft.public.access.queries)
  • Is a special filter form a reasonable idea?
    ... On this main form, I can do filters/sorts/finds, and that is great! ... query which returns *all* of the necessary fields. ... I *am* right that I can't filter/sort the main form based on subforms, ... FROM Tbl_Status INNER JOIN (Tbl_PubPlace INNER JOIN (Tbl_Publisher INNER ...
    (microsoft.public.access.forms)
  • Re: Query returning negative number - whats wrong with this SQL?!
    ... Thanks for this John, this is exactly the calculation i want to do. ... the SQL you've kindly done below isn't working in my query - it's saying ... "John Spencer" wrote: ... Randomisation.StudyNumber) INNER JOIN Screened ON Recruitment.StudyNumber = ...
    (microsoft.public.access.queries)