Delete won't delete from query/form

[sml]

I'm mystified. I have a pretty simple table with company name
information which has a one-many relation with a table with dates.
They are connected via the companyID.

I've created a left join query (all the records from the company even
if there are no dates). I've then used the query to create a
continuous form listing the company name and dates. I can add a new
company, I can add/edit company name, and I can add/edit new dates
associated with the company.

But I can't delete. I've tried both a button, and then I tried just
selecting the record, and then I tried going directly to the query to
delete a record there. I get the "you are about to delete a record,
and select yes to confirm" message and the record leaves .... but the
record it is NOT deleted from the table. When I next open the query
the 'deleted' record is there.

 

[Dirk Goldgar]

I'm mystified. I have a pretty simple table with company name
information which has a one-many relation with a table with dates.
They are connected via the companyID.

I've created a left join query (all the records from the company even
if there are no dates). I've then used the query to create a
continuous form listing the company name and dates. I can add a new
company, I can add/edit company name, and I can add/edit new dates
associated with the company.

But I can't delete. I've tried both a button, and then I tried just
selecting the record, and then I tried going directly to the query to
delete a record there. I get the "you are about to delete a record,
and select yes to confirm" message and the record leaves .... but the
record it is NOT deleted from the table. When I next open the query
the 'deleted' record is there.

Which record is it that you are intending to delete? A setup like that
*should* delete the record from the "many-side" table -- in this case, the
table of dates. It won't delete the company record, so rerunning the query
will show the company again.

 

[sml]

But I can't delete.  I've tried both a button, and then I tried just

Which record is it that you are intending to delete?  A setup like that
*should* delete the record from the "many-side" table -- in this case, the
table of dates.  It won't delete the company record, so rerunning the query
will show the company again.

Ah. That's what it was doing. It was just deleting from the dates
table. But if I want to delete from the company table ... how would I
do that? Thanks.

 

[Dirk Goldgar]

Ah. That's what it was doing. It was just deleting from the dates table.
But if I want to delete from the company table ... how would I do that?
Thanks.

There are several ways you might accomplish it. One likely one that would
require no code is to reconstruct the form as a main form/subform
arrangement, with the main form based on the Companies table alone, and the
subform based on the Dates table alone. The subform would be linked to the
main form by the shared CompanyID key. With this arrangement, deleting the
main form's record will delete the company and -- if the relationship is
defined with cascading deletes, -- also any dates related to that company.
Deleting a record on the subform will just delete that date, leaving other
dates and the company intact. Note, though, that the main form would have
to be in single-form view, because you can't put a subform on a continuous
form.

If you don't want to use a main form and subform, you could make your
current form do this by adding a "Delete Company" command button, with VB
code behind the button that would execute a delete query to delete the
company record from the Companies table. However, that a bit more
complicated than the subform arrangement, and also doesn't give the user the
nice visualization of the relationship between the tables that is the
benefit of using a subform.