none
#Deleted appears in dleted field before delete confirmation RRS feed

  • Question

  • Hello everyone, in my database below

    I got "#deleted"appears in credit field (subform) when I try to delete levelID from tblYearLevel. However, it seems the delete process done properly, so should I worry about it? if yes, then how to fix. Many thanks.

    Saturday, September 1, 2018 5:06 PM

Answers

  • The behaviour which you are experiencing should not occur if subform2 is linked to subform1 by means of the LinkMasterFields and LinkChildFields properties of the subform control.  When a record in subform1 is deleted it will immediately disappear from the subform pending the user's confirmation or cancelation of the delete operation.  Where the subforms are linked to reflect the one-to-many relationship type between the tables, subform2's recordset is reloaded, so that it will show the rows which reference the currently visible record,  or an empty new record, in which case both subforms will show no data.

    You can see this for yourself if you take a look at CorrelatedSubs.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates the use of both correlated subforms, and (as in your case) nested subforms, using data from Northwind.  Select the nested subforms option and then select the order record in the first subform by clicking on the subform's record selector.  Hit the Delete key and see what happens.  When the conformation dialogue appears drag it down so that it does not obscure the subforms.  You'll see that the order record you are deleting is not shown in the first subform, and its referencing order details rows are not shown in the second subform.  Select No in the dialogue and the original data will reappear in both subforms.  This is the usual behaviour with nested subforms.   Yours do not behave in this way, which suggests an unusual set-up in which the subforms are not linked.  This is not to say the set-up is wrong.  It might well reflect the logical model underlying your database, but it is not the usual way in which nested subforms behave.


    Ken Sheridan, Stafford, England

    • Marked as answer by AlefAelol Sunday, September 2, 2018 10:23 PM
    Sunday, September 2, 2018 8:15 PM

All replies

  • My guess is that 'cascade deletes' are enforced in the relationship between tblYearLevel and tblYearLevelCourses.  Consequently, when a row is deleted from the former any matching rows will be deleted from the latter.  The first question to ask yourself is whether you want this to happen.  If you disable cascade deletes in the relationship you would not be able to delete a row from tblYearLevel until all matching rows have been deleted from tblYearLevelCourses.

    If you decide that enforcing cascade deletes in the relationship is legitimate in the context of your database, then you would need to requery the subform when a row is deleted from tblYearLevel.  This will reload its recordset and remove any matching rows from the subform rather than showing them as #deleted.

    Ken Sheridan, Stafford, England

    Sunday, September 2, 2018 11:53 AM
  • My guess is that 'cascade deletes' are enforced in the relationship between tblYearLevel and tblYearLevelCourses.  Consequently, when a row is deleted from the former any matching rows will be deleted from the latter.  The first question to ask yourself is whether you want this to happen.  If you disable cascade deletes in the relationship you would not be able to delete a row from tblYearLevel until all matching rows have been deleted from tblYearLevelCourses.

    If you decide that enforcing cascade deletes in the relationship is legitimate in the context of your database, then you would need to requery the subform when a row is deleted from tblYearLevel.  This will reload its recordset and remove any matching rows from the subform rather than showing them as #deleted.

    Ken Sheridan, Stafford, England

    Thank you Ken Sheridan for the replay. Actually, the "#deleted" appears only at the Form_beforeDelConfirm, and it disappears after the deletion process done. My question is "Should I worry about that issue?" because the deletion process looks proper to me unless that "#deleted". For some reasons, I don't want to remove cascade deletes.
    Sunday, September 2, 2018 3:07 PM
  • You have no need to worry.  When you delete a record via the user interface it is, in effect, first moved to a delete buffer until you confirm the deletion or otherwise.  The record will disappear form the parent form.  If you don't confirm the deletion, it will reappear in the form.

    Any referencing rows in a subform  which are deleted by virtue of a cascade deletes operation will not disappear from the subform until it is requeried, which is why you see them marked as #deleted.  Usually the user will not see this, because a subform is normally linked to the parent form on the relevant keys, so when the record disappears from the parent form, it will move to another record and the subform will show the rows which reference that record.  Your set-up seems to be unusual in that the rows referencing the row being deleted are still visible in a subform, suggesting that the subform is not linked to the parent form.

    Ken Sheridan, Stafford, England

    Sunday, September 2, 2018 5:49 PM
  • Your set-up seems to be unusual in that the rows referencing the row being deleted are still visible in a subform, suggesting that the subform is not linked to the parent form.

    Ken Sheridan, Stafford, England

    Sorry, but I didn't understand that point of your replay. The interface scheme of my data entry is a form of two subfroms, the first from is bound to the academic years table, the subfrom1 is bound to tblYearLevel and subform2 is bound to tbl YearLevelCourseTeacher. the first subfrom is the parent of the second subfrom. meanwhile, subfrom1  is the child for the main form. In short,

      
    MainForm (tblAcadimicYears) is a Parent of Subform1(tblYearLevel) is a parent of Subfrom2 (tblYearLevelCorseTeacher)

    This issue happens when the deletion come from the mid of the scheme, i.e., when user delete at the Yearlevel.


    Sunday, September 2, 2018 6:26 PM
  • The behaviour which you are experiencing should not occur if subform2 is linked to subform1 by means of the LinkMasterFields and LinkChildFields properties of the subform control.  When a record in subform1 is deleted it will immediately disappear from the subform pending the user's confirmation or cancelation of the delete operation.  Where the subforms are linked to reflect the one-to-many relationship type between the tables, subform2's recordset is reloaded, so that it will show the rows which reference the currently visible record,  or an empty new record, in which case both subforms will show no data.

    You can see this for yourself if you take a look at CorrelatedSubs.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates the use of both correlated subforms, and (as in your case) nested subforms, using data from Northwind.  Select the nested subforms option and then select the order record in the first subform by clicking on the subform's record selector.  Hit the Delete key and see what happens.  When the conformation dialogue appears drag it down so that it does not obscure the subforms.  You'll see that the order record you are deleting is not shown in the first subform, and its referencing order details rows are not shown in the second subform.  Select No in the dialogue and the original data will reappear in both subforms.  This is the usual behaviour with nested subforms.   Yours do not behave in this way, which suggests an unusual set-up in which the subforms are not linked.  This is not to say the set-up is wrong.  It might well reflect the logical model underlying your database, but it is not the usual way in which nested subforms behave.


    Ken Sheridan, Stafford, England

    • Marked as answer by AlefAelol Sunday, September 2, 2018 10:23 PM
    Sunday, September 2, 2018 8:15 PM
  • The behaviour which you are experiencing should not occur if subform2 is linked to subform1 by means of the LinkMasterFields and LinkChildFields properties of the subform control.  When a record in subform1 is deleted it will immediately disappear from the subform pending the user's confirmation or cancelation of the delete operation.  Where the subforms are linked to reflect the one-to-many relationship type between the tables, subform2's recordset is reloaded, so that it will show the rows which reference the currently visible record,  or an empty new record, in which case both subforms will show no data.

    You can see this for yourself if you take a look at CorrelatedSubs.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates the use of both correlated subforms, and (as in your case) nested subforms, using data from Northwind.  Select the nested subforms option and then select the order record in the first subform by clicking on the subform's record selector.  Hit the Delete key and see what happens.  When the conformation dialogue appears drag it down so that it does not obscure the subforms.  You'll see that the order record you are deleting is not shown in the first subform, and its referencing order details rows are not shown in the second subform.  Select No in the dialogue and the original data will reappear in both subforms.  This is the usual behaviour with nested subforms.   Yours do not behave in this way, which suggests an unusual set-up in which the subforms are not linked.  This is not to say the set-up is wrong.  It might well reflect the logical model underlying your database, but it is not the usual way in which nested subforms behave.


    Ken Sheridan, Stafford, England


    Many thanks ken, I just learned new technique to organize nested forms
    Sunday, September 2, 2018 10:24 PM