Re: Query vs form with child & parent: Deleting rows

First, I have not set up any relationships at all in this database. I
understand that it's recommended, and why, and I expect that I will someday.
For now, though, I can be sure cascading deletes are not an issue because I
deliberately omitted all automatic relationships.

Second, I forgot to say at the outset last time that I'm sort of a newbie at
this. I've been writing in various computer languages professionally for
decades, and I've been tweaking this database in VBA for a year or so -- but
I never took a class in VBA, I had to learn it from manuals, and mostly when
I wanted to do something that I couldn't figure out, I just kept whacking
away until I found something that worked. Therefore I'm quite sure I've
taught myself a few techniques that are either stupidly inefficient or likely
to cause problems. Don't assume I'm writing my code sanely, in other words.

Now, you seem to be saying (correct me if I'm mistaken) that it makes a
difference which table I name first in my join. That doesn't accord with my
experience, which is why I think I may be misreading you. I'll show you the
actual query so you can see what ~I~ think is happening.

The parent Table is called "Who"; each record represent either a company, or
an individual who works for one of those companies. In my application I
display the Who records in a continuous main form called [Company list]. In
that form's detail section each record has a button I can push that opens a
second form (not a sub-form) displaying any Who records that might be
subordinate to that record, that is, all individuals (and sometimes child
companies) whose Owner field contains the record key of the parent company.
That second form (named clCoSub) is fed by this query:

SELECT *
FROM Who AS parent INNER JOIN Who AS child ON child.Owner=parent.ID
WHERE child.Owner=Forms![Company List]!ID

No problem so far. But I have a child table called "Channel"; each record
there is an email address, a phone number, a URL or some other way of
contacting the company or individual represented by its parent Who record.
In clCoSub's detail section is a button labeled "Channels", which displays a
third form (named clCoSubChan) that is fed by this query:

SELECT *
FROM Channel AS c LEFT JOIN Who AS w ON w.ID=c.Owner
WHERE c.Owner In (Forms!clCoSub!CoID,Forms!clCoSub!ID)

This query displays every channel that is owned either by the individual
whose button I clicked (clCoSub!ID), or the company (clCoSub!CoID) to which
that individual is subordinate. Let's pretend this query displays five
records:

Company X : Fax number
Company X : URL
Bob Barcy : Office phone
Bob Barcy : Cell number
Bob Barcy : Email address

The form displays the correct five channels. But if in that form I delete,
say, Bob Barcy's cell number, Access deletes not just that Channel record but
also the parent -- Bob Barcy's Who record -- leaving Barcy's office phone and
email address orphaned. (I know, setting relationships for referential
integrity would delete the two orphaned records, but let's not lose sight of
the ball; what I want to know is why Access is deleting Barcy's Who record).

I'd expect that since the form acts that way, the query would do the same --
that is, if I display the query in Data*** mode and delete the record for
Barcy's cell phone, it'll act the same way as in the form. But no, when I
delete that record in the query's data*** display, it deletes just the
Channel record. That's what I want the form to do, too.

Furthermore, it all acts correctly in clCoSub. Remember that form? It
displays Who records that are subordinate to another Who record; if in that
query I delete a record, Access deletes just the child Who record, not the
parent Who record -- AND LIKEWISE IN THE FORM!, that is, I can delete a child
record in clCoSub and lose just the child Who record and keep the parent Who
record, which is what I wanted in the first place. So why doesn't it work
that way in clCoSubChan?

You spoke of "main table" in a join, and seemed to say that it's the table
you mention first, whether your join is inner or outer. But I already tried
varying the JOINs in that way and it didn't make any difference. If I didn't
misunderstand you, how sure are you of that theory? You sound pretty
certain, but it isn't working that way at this end.
.