Strange source table behavior when deleted record through form

[keliie]

Hello,

I have an index form which displays menu items (e.g., filet mignon) and
their associated prices (e.g., 1/1/2006 - $30). When I delete a record
from the form (i.e., right click, delete record), the price data is
deleted, but the menu item is not deleted. More specifically, when the
form is refreshed the menu item reappears, but the price fields are
null. I have an inkling that the issue is due to the underlying form's
query; in particular the use of the "In" operator for the subquery
located in the Price_ID fields criteria.

My question: does anyone have any idea why this is happening and what I
can do to fix it?

Details:
tblMenuMaster:
Menu_Description_ID (key field)
Menu_Category
Menu_Type
Menu_Memo

tblMenuPrice:
Price_ID (key field)
Company_Location
Menu_Price_Date
Menu_Description_ID
Menu_Item_Price

1:M link on Menu_Description_ID

Source query on aforementioned form:
SELECT
tblMenuMaster.Menu_Item_ID, tblMenuMaster.Menu_Category,
tblMenuMaster.Menu_Type, tblMenuMaster.Menu_Description_ID,
tblMenuMaster.Menu_Memo, tblMenuPrice.Price_ID,
tblMenuPrice.Menu_Price_Date, tblMenuPrice.Menu_Item_Price
FROM
tblMenuMaster LEFT JOIN tblMenuPrice ON
tblMenuMaster.Menu_Description_ID = tblMenuPrice.Menu_Description_ID
WHERE
(((tblMenuPrice.Price_ID)
In (SELECT tblMenuPrice.Price_ID
FROM [SELECT tblMenuPrice.Menu_Description_ID,
Max(tblMenuPrice.Menu_Price_Date)
AS LastDate FROM tblMenuPrice GROUP BY
tblMenuPrice.Menu_Description_ID ].
AS M INNER JOIN tblMenuPrice ON
(M.Menu_Description_ID = tblMenuPrice.Menu_Description_ID)
AND (M.LastDate = tblMenuPrice.Menu_Price_Date))
Or (tblMenuPrice.Price_ID) Is Null)
AND ((Len([tblMenuMaster].[Menu_Description_ID]))>='0'
Or (Len([tblMenuMaster].[Menu_Description_ID])) Is Null)
AND ((Len([tblMenuMaster].[Menu_Category]))>='0'
Or (Len([tblMenuMaster].[Menu_Category])) Is Null)
AND ((Len([tblMenuMaster].[Menu_Type]))>='0'
Or (Len([tblMenuMaster].[Menu_Type])) Is Null))
ORDER BY
tblMenuMaster.Menu_Category, tblMenuMaster.Menu_Type,
tblMenuMaster.Menu_Description_ID;

 

[Guest]

Not strange at all as you have a Left Join.

What you really want is a form / subform combination. First though you want
the two tables linked together in the Relationship window with referiential
integrity turned on and cascade Update and Delete enabled.

That way if you want filet mignon completely removed from the menu, deleting
it from the form will remove the related records in the table that populates
the
subform. If you wish just to delete one record in the subform for a
particular date, it won't bother the data in the form.

 

[keliie]

Jerry,

Thanks for your response, I appreciate it.

First things first, relationship integrity is turned on.

Second, I'm not totally clear on what you mean by form / subform
combinations. I am familiar, and extensively use, form / subform
combinations in my application, but I'm not sure how this impacts
deletion of records.

Finally, I'm intrigued by your left join comment, can you elaborate.

Thanks again,

Kelii

 

[Guest]

Hi,

If you have a form based on the parent table and a subform based on the
child table, you should be able to delete a record from the child table
(assuming that it isn't related to yet another table). After the deletion the
parent record still should be there. The subform would then be empty or have
other related records.

If you try to delete the parent record one of two things should happen as
you say you have RI turned on between these two tables. (1) Access throws up
a warning stating that you can't delete the parent due to the child records
or (2) if you have cascade delete enabled Access will delete the parent
record and any child records related to it.

I reviewed your form's SQL and it could well be the problem. If a
form/subform combination you usually do not have a join at all in the form.
Rather you should have the form based on just one table and the subform based
on the other table.

In your case the form should be based on tblMenuMaster and the subform based
on tblMenuPrice. The form/subform should be linked by Menu_Description_ID and
this link should be in the Relationship Window with referiential integrity
turned on and cascade delete enabled. That you when you delete a record from
tblMenuMaster all related records in tblMenuPrice should also be deleted.
However deleting a record from tblMenuPrice will not delete the matching
parent record in tblMenuMaster.

 

[keliie]

Jerry,

Interesting thoughts.

I've chosen to create a form which has a few simple combo boxes which
pass filter arguments to the subform. The subform itself, shown in
datasheet view, displays fields from two tables tblMenuMaster and
tblMenuPrice. As you might imagine, each Menu item has several Prices,
as prices change over the course of the year. The subform displays the
most recent price (hence the need for the complex query).

Having worked on the problem for the last few days, I have come to the
same conclusion as you have: the query! I haven't solved the problem
yet, but any additional thoughts, given this further clarification
would be appreciated.

Kelii