Check Boxes "not detectable"

[Bill]

I have a form with 5 check boxes that are bound
to Yes/No fields returned by a simple query. Four
of the 5 check boxes are new to the form. Since
the addition of those four controls I can no longer
change anything on the form, i.e., I get the usual
"blink" sound anytime I attempt to toggle a check
box or modify text in a text box. It behaves as
though I had disabled the form, though the form's
property sheet clearly shows the form to be
enabled.

What might be the problem?

Bill

 

[Klatuu]

How simple is your query? It sounds like you have created an uneditable
recordset.

 

[Bill]

The query was created in design grid using fields
from two tables that are linked via long-standing
relationships, Families and Registry. I've copied
the SQL view and have included it here:

SELECT Families.FamilyName, Families.FamilyDirectory,
Families.FamilyImageID,
Families.FamilyDirFmtID, Families.FamilyID, Registry.PubCell1,
Registry.PubCell2,
Registry.PubEMA1, Registry.PubEMA2 FROM Families INNER JOIN Registry
ON (Families.FamilyID = Registry.FamilyID) AND (Families.FamilyID =
Registry.FamilyID)
WHERE (((Families.FamilyDirectory)=True)) OR (((Families.FamilyImageID) Is
Not Null))
ORDER BY Families.FamilyName;

When I run the query directly, I've just discovered
that can't change any of the fields via the datasheet
view either. So, my problem seems to be based on
what you suspected, namely an uneditable query.

WOW, the query seems so simple. What might be
wrong with it?

Bill

 

[Klatuu]

I am not an expert in this area, but this is obviously the problem. The
simple solutioin would be to use a form/subform with the Families table in
the main form and the Registry table in the sub form.

 

[Bill]

NEVER NEVER NEVER EVER make
program changes to a large system when
you are ill. I know better than to create
an inner-join in a query when there's a
one-to-many relationship......SIGH!

Thanks Dave.

Bill

 

[George Nicholson]

INNER JOIN Registry

ON (Families.FamilyID = Registry.FamilyID)
AND (Families.FamilyID = Registry.FamilyID)
WHERE...

For whatever reason, the above clause looks pretty redundant (ANDing an
identical condition).

Change it to
INNER JOIN Registry
ON (Families.FamilyID = Registry.FamilyID)
WHERE...
and see if the query becomes editable again. Might not, but it won't hurt
anything to try.

Might also make sure that Registry.FamilyID is indexed w/Duplicates OK.

The "double join" makes me wonder if you have Name Auto-Correct on, and have
used a Lookup field within table design...

HTH,
George

 

[Bill]

The "Inner Join" query can't be updated
because the relationship is one-to-many.

Thanks for you time and thoughts though.

Bill

 

[Dirk Goldgar]

NEVER NEVER NEVER EVER make
program changes to a large system when
you are ill. I know better than to create
an inner-join in a query when there's a
one-to-many relationship......SIGH!

I don't follow you. There's nothing inherently nonupdatable about an inner
join of tables in a one-to-many relationship; at least not in Jet. I can't
recall whether SQL Server has any problem with it. Of course, it may depend
on the indexes, what fields include in the query, and what fields you join
on. But the simple fact of the inner join and 1-M relationship don't make
it nonupdatable.

 

[Bill]

Hi Dirk,
I'm not sure how to explain the situation, but
I'll try.

The query, I assume you looked at the SQL view
of the query, contains two tables. The first table
has in each of its records a key-value to records
in the second table. Theoretically, there could be
an infinite number of records in the second table
which contain that key-value. That being the case,
the question arises as to which record in the 2nd
table would be the expected recipient of any
updates.

The HELP text specifies that there is no solution
when such a query is created.

Bill

 

[Dirk Goldgar]

Hi Dirk,
I'm not sure how to explain the situation, but
I'll try.

The query, I assume you looked at the SQL view
of the query, contains two tables. The first table
has in each of its records a key-value to records
in the second table. Theoretically, there could be
an infinite number of records in the second table
which contain that key-value. That being the case,
the question arises as to which record in the 2nd
table would be the expected recipient of any
updates.

The HELP text specifies that there is no solution
when such a query is created.

Would you mind identifying and quoting the HELP text you're referring to?
Either it's flat wrong, or you've extrapolated from a special case to a
general one, or maybe you're not talking about a Jet database. But see the
help text entry from Access 2003 on "When can I update data from a query",
viewable at this link:

http://office.microsoft.com/assistance/hfws.aspx?AssetID=HP051880011033

It identifies among other things specific conditions under which you might
not be able to update data in a query that joins tables in a one-to-many
relationship. But this implies that these are exceptional conditions, and I
can attest from experiment that they are.

Does each of your tables have a primary key? Is the join field indexed in
both tables? These are things I could imagine affecting the updatability of
the query. Unless these aren't Jet tables, there's got to be something
about the tables' design or the query itself that is keeping the query from
being updatable, but it isn't just the fact that the query involves a
one-to-many relationship.

Hmm, I see that back on the 7th, George Nicholson pointed out an an oddity
in your SQL:

(quoting George Nicholson's post):

For whatever reason, the above clause looks pretty redundant (ANDing an
identical condition).

Change it to
INNER JOIN Registry
ON (Families.FamilyID = Registry.FamilyID)
WHERE...
and see if the query becomes editable again. Might not, but it won't hurt
anything to try.

I think he hit the nail on the head. I just took an updatable join of
tables in a one-to-many relationship, doubled up the join condition like
that, and it suddenly became nonupdatable. Did you ever try his suggested
fix?

 

[George Nicholson]

Did you ever try his suggested fix?

I've been wondering the same thing...

 

[Bill]

Dirk,

I got pulled off temporarily to attend to a more
pressing aspect of this project. I'll try the fix the
first chance I get and try to find the HELP grid
that indicated there wasn't any fix for what I
encountered.

And yes, it's likely that I generalized what might
very well be a special case.

All my tables have primary keys, but the field
that linked the two in a OTM relationship are
not indexed.

All the tables in the current application are JET.
As a matter of fact, I don't think I have any
applications that are not JET.

Bill

 

[J_Goddard via AccessMonster.com]

I'm not sure that is always the case; From what I have seen, if you have
indexes on the table field(s) used in the join on the 'many' side, then the
query is updatable. I have just written such a query, and it runs fine. If
the 'many' side of the join is itself another query, it cannot be updated.

John

The "Inner Join" query can't be updated
because the relationship is one-to-many.

Thanks for you time and thoughts though.

Bill

INNER JOIN Registry
ON (Families.FamilyID = Registry.FamilyID) [quoted text clipped - 62 lines]

Bill